In the previous article How To Use VLOOKUP In Excel, I told you how to use the excel VLOOKUP function to search for value in a cell range and return the wanted value in the cell range. In this article, I will tell you what is the difference between VLOOKUP approximate match and exact match with examples.
1. What Is The Difference Between Excel VLOOKUP Approximate Match And Exact Match?
- The excel VLOOKUP function has the below syntax.
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
- The fourth parameter of the excel VLOOKUP function is range_lookup which is a boolean value.
1.1 VLOOKUP Approximate Match.
- When range_lookup is set to TRUE it will implement the approximate match.
- In the approximate match mode, if it can find the exactly matched cell then it will return the matched cell value.
- If it can not find the exactly matched cell, it will return a cell whose value is smaller and closest matches the lookup_value.
- In the approximate match mode, you need to convert the cell range to an excel table and sort the first column of the table in ascending order to make it work correctly.
- If you do not order the table’s first column in ascending order, you may find it does not work as wanted.
1.2 VLOOKUP Exact Match.
- When range_lookup is set to FALSE it will implement the exact match.
- In this match mode, if it can find the exactly matched cell, then it will return the cell value.
- If it can not find the exactly matched cell, it will return the error text #N/A, it will not return the cell whose value approximately matches the lookup_value.
- The VLOOKUP exact match mode does not need to order the cell range’s first column in ascending order.
- Now I will give you some examples to verify this.
2. Excel Vlookup Approximate Match And Exact Match Example.
- Below are the article’s example data cells.
A B 1 Score Name 2 35 Jerry 3 28 Tom 4 39 Richard 5 16 Jack 6 99 John
- Column A contains the student’s score, and column B contains the student’s name.
2.1 Excel VLOOKUP Exact Match Example.
- Because the excel VLOOKUP function exact match does not need to convert the above cell range to an excel table and does not need to sort
- column A in ascending order, we will show an example of exact math first.
- Input the formula =VLOOKUP(28, A2:B6,2, FALSE) in cell C1 and press the enter key, then it will show the text Tom in cell C1.
- The fourth parameter in the above formula is FALSE, then it will implement an exact match.
- The first parameter is 28 which matches the cell A3‘s value.
- The third parameter is 2, so it will return the cell B3‘s value which is Tom.
- If you change the above formula to =VLOOKUP(29, A2:B6,2, FALSE), then it will show the error text #N/A, this is because there is no cell containing the number 29 in column A.
2.2 Excel VLOOKUP Approximate Match Example.
- If you want to implement VLOOKUP approximate match in a cell range, you need first convert the cell range to an excel table and sort the table’s first column in ascending order, otherwise, the approximate match will not work correctly.
- Convert the cell range A1:B6 to an excel table, you can refer to the article Excel Convert Table To Range Example And Vice Versa.
- Click the down arrow beside column A‘s header ( Score ) and click the item Sort Smallest to Largest in the popup menu list to sort column A in ascending order.
- Then you can get the below data table.
A B 1 Score Name 2 16 Jack 3 28 Tom 4 35 Jerry 5 39 Richard 6 99 John
- Now input the formula =VLOOKUP(29, A2:B6,2, TRUE) in cell C1 and press the enter key.
- Then it will show the text Tom in cell C1, this is because there is not a cell in column A that contains the number 29 but cell A3‘s value 28 is closest to 29, so it returns the cell B3‘s value Tom ( because the third parameter is 2 ).
- Input the formula =VLOOKUP(37, A2:B6,2, TRUE) in cell C2 and press the enter key, then it will show the text Jerry in cell C2.
- This is because cell A4‘s value (35) is smaller than number 37 and closest to number 37, then it will return cell B4‘s value Jerry.
- How To Use Vlookup Wildcard To Implement Partial Match In Excel.
- How To Fix Vlookup Wildcard Not Working Issue.