When using VLOOKUP, if an "exact match" can't be found, don't sweat it! Read to find out how to find the closest match.
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. In the case where we can't match the exact value in the table then we would use the closest match option. 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!
See our other location pages: