Posts

VLOOKUP using Approximate Match

In a lot of cases, we use VLOOKUP to find exact matches, based on some kind of unique id. But there are many situations where we can perform VLOOKUP using Approximate Match. A classic case is using VLOOKUP to find a Grade for the students where marks are provided.

Suppose we are provided with Test scores of students and have been asked to find the grades for each students base on different slabs of scores. In such cases, we generally use nested IF and the formula goes too long.

We can replace the same with VLOOKUP using Approximate Match by using TRUE or 1 in the last argument that is [range_lookup].

vlookup-using-approximate-match

[range_lookup] is the most critical or we can say the most important argument of VLOOKUP. If we use TRUE, then it finds the closest minimum match from the Master Table.

The most important task to get the desired result is to prepare the table_array in a smart, VLOOKUP friendly format and in the sorted in ascending order.