What does it do?
Searches for an approximate value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.
Formula breakdown:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
What it means:
=VLOOKUP(this value, in this list, and get me value in this column, Approximate Match/TRUE/1])
The Vlookup function in Excel is great when you want to find an exact match in your data table but what happens if you want to find an approximate match?
Approximate matches are used when you have an ascending table like Commission Bonus Rates or Income Tax Rates.
IMPORTANT: For the Vlookup Approximate Match to work in Excel, the table_array has to be sorted in ascending order!
So the way that this formula works is that it looks at the first value in the Table_Array that is greater than the Lookup_Value and then goes back one value.
Have a look at the following tutorial which explains this formula & don’t forget to download the workbook so you can practice:
STEP 1: We need to enter the VLOOKUP function in a blank cell:
=VLOOKUP(
STEP 2: The VLOOKUP arguments:
Lookup_value
What is the value to be looked up?
Select the cell that contains the income as the lookup value.
=VLOOKUP(F14,
Table_array
Where is the list of data?
Select the tax table, as that is where our formula is going to get the tax rate.
=VLOOKUP(F14, B14:C19,
Col_index_num
Which column in the table_array contains the data you want to return?
We want the tax rate which is the second column.
=VLOOKUP(F14, B14:C19, 2,
[Range_lookup]
Would it be an approximate match?
Set this to TRUE as we want an approximate match.
=VLOOKUP(F14, B14:C19, 2, TRUE)
You now have your tax rate!
How to Do VLOOKUP Approximate Match in Excel
HELPFUL RESOURCE:
