Add-Innovation Home

About Add-Innovation

Contact Us

Searching for an exact / closest match.

 

The last argument (part of formula) of the VLOOKUP function allows you to specify if you wish to search for an exact, or the closest match. Often a match can be found to your data by specifying that you want to find the closest match.

 

This can be especially useful if, for example, you want to return a reference to a value within given tolerance levels.

 

It should be used with care however, and you may only want to look for the exact match. Looking for the closest match is the default, and therefore by leaving this last part of the formula out, you may be returning an incorrect value.

 

If you have specified that you want to find the closest match, then the data you’re looking into must be sorted in Ascending order. Excel will run through each row in the data you’re looking into and test to see if the value is greater than or equal to the value you’re looking up. If the value is greater than the value you’re looking up, then it returns the previous value. If the values are equal it returns the value it has found.  Once a match has been found. Excel stops running through the lookup procedure.

For text values, returning the closest match only works if the first letter can be found in the specified range, processing each row in turn. If Excel finds text in the next cell it’s searching through, which is greater than the first letter of the text being searched for it will look at the next cell, and then fail the VLOOKUP if it doesn’t begin with the same first letter.

 

Because of the way Excel searches for the closest match, and expects the data to be sorted in Ascending order, then specifying you want the closest match, but looking within a data table which is NOT sorted, can result in a file on the VLOOKUP even though an exact match exists. This is made worse by the fact that the closest match is the default. An example of this is shown below ;

 

Suppose we have a data table we want to look into as follows ;

 

Monday

1

Tuesday

2

Wednesday

3

Thursday

4

Friday

5

 

If we specify a VLOOKUP of ;

 

=VLOOKUP("Friday",G8:H12,2)

 

Then this will fail, even though “Friday” exists in the data table. Note that the last part of the VLOOKUP formula is missing. Excel applies the default of closest match. It starts to search through the days of week in the left most column of the lookup array. It sees that “M” is higher than “F”, but looks in the next cell “Tuesday”, and sees that this doesn’t match either. It therefore fails the VLOOKUP.

If we had been searching for “Thursday” then VLOOKUP would find it, because it uses the look one array rule.

 

If we sort the data table in ascending order, the VLOOKUP works. Alternatively, specifying that we want an exact match would also work.

 

The VLOOKUP Assistant will check to see if you have specified an exact, or the closest match, and will inform you of such. It will warn you if you have omitted the argument, and tell you what the default is, and if it sees that the exact match exists using the COUNTIF function, warn you that your data table may not be sorted.