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.
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.
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!
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])
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:
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:
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.
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:
3. In the Formula Helper dialog box, please do the following operations:
(1.) Click first button beside Table_array to select the data range which you want to use;
(2.) Click second button beside Look_value to select the criteria that you want to return its matched value;
(3.) Click third button beside Column to select the column contains the value you want to return.
4. Then click Ok, and the exact matched data based on a specific value has been returned at once. See screenshot:
Note: If the value is not found in the data range, you will get an error value #N/A.
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?
Please enter this formula: =VLOOKUP(D2,A2:B8,2,TRUE) and press Enter key to return the nearest value, see screenshot:
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.