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?


    Recommended Productivity Tools

    Ribbon of Excel (with Kutools for Excel installed)

    300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

    Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

    •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
    •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
    •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
    •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
    •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
    •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
    •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
    •  Change the way you work now, and give you a better life immediately!

    Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

    • Increases your productivity by 50% when viewing and editing multiple documents.
    • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
    • Open and create documents in new tabs of same window, rather than in new windows.
    • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
    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.