Vlookup Approximate Match in Excel

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 ta the following tutorial which explains this formula & don’t forget to download the workbook so you can practice:

DOWNLOAD WORKBOOK

HELPFUL RESOURCE:

If you like this Excel tip, please share it

Facebook

Twitter

Google+

Evaluate Formulas Step By Step in Excel This is one of the coolest tricks I have seen in Excel, as there are countless times wherein I had a hard time understand formulas. Especially long and complex ones! Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it! ...

Create a Random List with Excel’s RAND Formu... What does it do? Gives you a random number between 0 and 1 Formula breakdown: =RAND() What it means: =RAND(Will automatically choose a random number between 0 and 1) Excel is able to do a lot of things that most users are unaware of! One thing that amazes m...

Create a Dynamic Data Range with the OFFSET functi... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number o...

IF Function: Introduction What does it do? It returns a value that you set if a condition is met, and a value if it is not met Formula breakdown: =IF(Logical Test,Value if True,Value if False) What it means: =IF(The condition to be checked, Value to be shown if the condition is...