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+

VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office.Advanced users have seen the use of adding more than one...

Cleaning Data with Excel’s TRIM Formula What does it do?Removes unneeded spaces in your text, except single spaces in between wordsFormula breakdown:=TRIM(text)What it means:=TRIM(text that you want extra spaces to be removed)In the quest for cleaner data, one of the common scenarios is remo...

INDEX-MATCH Maximum Sales The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array.Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array....

Calculate Total Sales With An Array Formula An Array Formula performs an Excel operation (math, comparative, join or function argument) on an array or range of data. This could be a range of cells, a worksheet reference or a defined name.One of the most popular uses of an array formula is to calculate Total Sales....