Use approximate VLOOKUP for ordered ranges such as grade thresholds. Learn when it is appropriate and why the lookup column must be sorted.
When to Use Closest Match in VLOOKUP
In our VLOOKUP tutorial, we said to hold off on worrying about the fourth input in the VLOOKUP, the range lookup. Most of the time we will be using the 0 or FALSE option for an exact match, but in certain cases, we may actually use the closest match option. Use approximate matching only when the lookup table intentionally defines ranges or thresholds; it is not a general remedy for a failed exact match. For example, if we have tax brackets that serve as ranges for different income values and then we input different income amounts that fall within those ranges, an exact match will not work and we'll need to use the closest match. See below for how to use the closest match and more on the tax bracket example.
Finding the Closest Match
An “Exact Match” means that the data to be returned is explicitly stated in the data set
A “Closest Match” means that the data to be returned is not explicitly stated (i.e. the data can be found within a range)
The Closest Match will seek the closest value, without going over.
In this case, "Range Look Up" will be True (1)
This is often useful when data is contained within a defined range, such as tax brackets, or grades.
Learn More About Excel
Become an expert in Excel through our Microsoft Office classes in NYC and Excel training courses. Sign up and start creating smart and efficient spreadsheets today!
Approximate VLOOKUP requires the first column of the lookup table to be sorted in ascending order. It returns the largest lookup value less than or equal to the requested value; a request below the smallest value returns #N/A. Use FALSE for an exact-match lookup.