The difference between approximate and exact match is the Range_lookup box where the exact match uses false (or 0) while the approximate match uses true (or 1 or blank).
You can use one of the following formulas for the approximate match:
=VLOOKUP(Value to look-up, The data range, The column number, true)
=VLOOKUP(Value to look-up, The data range, The column number, 1)
=VLOOKUP(Value to look-up, The data range, The column number)
With VLOOKUP approximate (when the Range_lookup box is blank or is specified with a value of TRUE or 1).
– if an exact match is found, the lookup_value will be returned;
– if an exact match is not found, the next largest but less than the lookup_value will be returned.
To make VLOOKUP approximate match work properly, the table_array must be sorted by the first column that matches the lookup value in the ascending sort order; otherwise, VLOOKUP may not give the correct value.
For example, In the data table below, to find the marks that match with the scores in column A based on the table E – F and put them in column C.
Step 1: Sort Data table E2:F11 based on column E from smallest to largest. Column A and column E are the same identifier shared by the two tables;
Step 2: Click the cell C2, the first cell you want the function;
Step 3: Click the "Formula" Tab from the Ribbon;
Step 4: Click "Insert Function";
Step 5: In the Insert Function Window, type VLOOKUP in the box and click Go. VLOOKUP function will appear in the box below. Click VLOOKUP and then click OK at the bottom;
(Note: if you used VLOOKUP last time, you will see VLOOKUP appears on the list)
Step 6: The Function Arguments window will appear. In the 1st box, click the cell with the lookup ID A2;
Step 7: In the 2nd box, select the data range $E$1:$F$11 where the lookup value is located; the advantage to use absolute value is that the search data range will be the same all the time;
Step 8: In the 3rd box, type in number 2. In the "Table E – F", column F is the 2nd column;
Step 9: In the 4th box, type in True for approximate search (or 1, or omit);
Step 10: Click OK, cell C2 will show "+C"; the value of cell A2 is 78 which is not in column E. When an exact match cannot find, VLOOKUP returns to the next largest but smaller than 78 (the lookup value), which is 76 and the matching value to 76 is C+;
Step 11: Copy the formula down to the bottom to have all the cells with the matching value;
Step 12: Copy column C and paste special with value only to remove formula.
