Pinterest Pixel

Vlookup Approximate Match in Excel

  What does it do? Searches for an approximate value in the first column of a table... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

 

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.

Want to know how to use the VLOOKUP function from Beginner to Advanced?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

See also  The Essential MAXIFS Formula in Excel - Step by Step Guide

Watch it on YouTube and give it a thumbs-up!

download the youtube excel practice fileVlookUp.xlsx

Want to know how to use VLOOKUP with Approximate Match?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch on YouTube and give it a thumbs up 👍

Vlookup Approximate Match in Excel | MyExcelOnline

Have  a look at the following tutorial which explains this formula & don’t forget to download the workbook so you can practice:

Vlookup Approximate Match in Excel | MyExcelOnline

Download excel workbookVlookup_Approximate-Match.xlsx

STEP 1: We need to enter the VLOOKUP function in a blank cell:

=VLOOKUP(

Vlookup Approximate Match in Excel

 

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,

Vlookup Approximate Match in Excel

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,

Vlookup Approximate Match in Excel

Col_index_num

Which column in the table_array contains the data you want to return?

See also  Vlookup in an Excel Table

We want the tax rate which is the second column.

=VLOOKUP(F14, B14:C19, 2,

Vlookup Approximate Match in Excel

[Range_lookup]

Would it be an approximate match? 

Set this to TRUE as we want an approximate match.

=VLOOKUP(F14, B14:C19, 2, TRUE)

Vlookup Approximate Match in Excel

You now have your tax rate!

Vlookup Approximate Match in Excel

 

How to Do VLOOKUP Approximate Match in Excel

 

 

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!