Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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.]

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .