Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.