To perform a search under the exact match mode, the fourth argument must be set to FALSE. If left omitted, Excel sets it to the approximate match mode (TRUE) by default.
Now how does this cause the VLOOKUP to malfunction? See here:
For Item Code 64, the VLOOKUP function returns the product ‘Caps’. However, that’s not the correct answer. We can see the product against Item Code 64 is ‘Clips’ ❌
This happened because we forgot to write the range_lookup argument in our VLOOKUP function. So it was set to an approximate match mode (TRUE) by default.
The approximate match mode needs the data arranged in an ascending order which was not the case in our example. The Item Code at Row 4 is 70 (greater than the lookup value of 64). That’s where Excel stopped searching and returned the Product against the Item Code equal to or next smallest to 64 🤷♀️
From the numbers 15, 17, and 63, the next smallest to 64 is 63 and so we get the product against the Item Code 63 i.e. ‘Caps’.
Now let’s rewrite the same function with the exact match mode as follows:
= VLOOKUP (E3, $A$2:$C$6, 2, FALSE)
The results this time will be different (and correct):
This time Excel doesn’t stop searching at 70.
If you’re looking for an exact match, do not forget the exact match mode argument 🔎