Using Excel Lookup Function and Handling Case Where No Matches Exist

Question

I'm using Excel to enter data for an event where people register. A high percentage of the registrants will have registered for previous events, so we can their name and ID number.

I'm trying to use the LOOKUP function in Excel to lookup the name and then populate the ID field with their ID number.

This works well unless the value that is looked up is a new user that we don't already have details for.

However, if the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. This causes a problem since you can't tell if the match was exact (and the data is correct) or not exact and the match is incorrect.

How do I catch non-matches and handle separately?

Answer 1

You could try using the VLookup or HLookup functions instead. The final parameter of these functions is "Range_Lookup" which can be set to false if you only want exact matches.

=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

For example, if you do this it will display "#N/A" if a match is not found:

=VLOOKUP(A1, $C$1:$D$4, 2, FALSE)

[There is also the Match function, which allows you to set the last parameter to 0 to find the first value that is exactly equal to the required lookup value.]