Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to use vlookup exact and approximate match in Excel?

In Excel, vlookup is one of the most important functions for us to search a value in the left-most column of the table and return the value in the same row of the range. But, do you apply the vlookup function successfully in Excel? This article, I will talk about how to use the vlookup function in Excel.

Use vlookup with an exact match in Excel

Vlookup to get an exact match with Kutools for Excel

Use vlookup with the approximate match in Excel


Vlookup to return exact matching value from a list table:

With this formula of Kutools for Excel, you can quickly vlookup the exact value from a list without any formulas.

doc-vlookup-function-6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Use vlookup with an exact match in Excel


First, you must know the vlookup syntax and details of the parameters.

The syntax of VLOOKUP function in Excel is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: the value to search based on in the first column of the table;
  • table_array: the range of cells that contains the data, you can use a reference to a range such as A1:D10, or a range name;
  • col_index_num: the column number in the table_array argument from which the matching value must be returned;
  • range_lookup: is optional operation, FALSE to find an exact match, and TRUE to find the approximate match.
  • Now, I will take an example to explain this vlookup function to get the exact match, supposing you have the following list of persons’ information in range A2:D10:

    doc-vlookup-function-1

    In this case, you know an ID number A1007 and want to find the corresponding name with the ID in the same row, please use this vlookup formula: =VLOOKUP(F2,A2:D10,2,FALSE), and then press Enter key, you will get the result that you want. See screenshots:

    doc-vlookup-function-1
    -1
    doc-vlookup-function-1

    Notes:

    1. In the above formula: F2 is the value which you want to return its relative information, A2:D10 is the data range you use, the number 2 indicates the column number that your matched value is returned and the FALSE refers to the exact match.

    2. If your value AA1007 is not found in the data range, you will get an error value #N/A.


    Vlookup to get an exact match with Kutools for Excel

    If you have Kutools for Excel, with its Look for a value in list formula, you can quickly return the matching data based on a specific value.

    Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days

    After installing Kutools for Excel, please do as follows:

    1. Click a cell where you want to put the matched result.

    2. Then click Kutools > Formulas > Look for a value in list, see screenshot:

    doc-vlookup-function-1

    3. In the Formula Helper dialog box, please do the following operations:

    (1.) Click first  111 button beside Table_array to select the data range which you want to use;

    (2.) Click second  111 button beside Look_value to select the criteria that you want to return its matched value;

    (3.) Click third  111 button beside Column to select the column contains the value you want to return.

    doc-vlookup-function-1

    4. Then click Ok, and the exact matched data based on a specific value has been returned at once. See screenshot:

    doc-vlookup-function-1

    Note: If the value is not found in the data range, you will get an error value #N/A.

    Download and free trial Kutools for Excel Now !


    Demo: Vlookup to get an exact match with Kutools for Excel

    Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


    Use vlookup with the approximate match in Excel

    Sometimes, your specified data is not in the data range, to get the nearest match with the given data, you need to use the vlookup to get an approximate match.

    If you have the following range data, the specified quantity number 158 is not in the Quantity column, how to get its closest unit price in column B?

    doc-vlookup-function-1

    Please enter this formula: =VLOOKUP(D2,A2:B8,2,TRUE) and press Enter key to return the nearest value, see screenshot:

    doc-vlookup-function-1

    Notes:

    1. In the above formula: D2 is the value which you want to return its relative information, A2:B8 is the data range you use, the number 2 indicates the column number that your matched value is returned and the TRUE refers to the approximate match.

    2. The approximate match returns the next largest value that is less than your specific lookup value.

    3. To use the vlookup function to get an approximate match value, your first column in the table must be sorted in ascending order, otherwise it will return a wrong result.


    Related articles:

    How to vlookup values from right to left in Excel?

    How to lookup the last matching value in Excel?

    How to lookup value to match case sensitive in Excel?


    Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

    • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
    • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
    • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
    • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
    • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
    • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
    • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
    • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
    • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
    kte tab 201905

    Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

    • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
    • Open and create multiple documents in new tabs of the same window, rather than in new windows.
    • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
    officetab bottom
    Say something here...
    symbols left.
    You are guest ( Sign Up? )
    or post as a guest, but your post won't be published automatically.
    Loading comment... The comment will be refreshed after 00:00.

    Be the first to comment.