So many times vlookup (or index & match) saves the day and gets your answer! ….wait…are you sure it’s correct??? Read this post to see why you should use countifs to confirm your vlookup.
(download my Excel file)
The Dirty Secret
Even if your vlookup syntax is correct it might only be telling part of the story. HUH?!
Vlookup will only retrieve the first answer. What if your lookup_value is found multiple times in your data-set? Which answer is correct?
Assuming that your lookup_value is only found once is a dangerous assumption.
Good Habit
I can’t examine every row in a dataset so I use various methods to double check my results.
I often use countifs to confirm how many times the lookup_value is found in the data-set. If it’s only found 1 time then vlookup works.
Other Methods
There are other ways to verify if you have unique values in a column:
- Pivot Table put lookup_value column into row label area and also in values area as a count
- Remove Duplicates make a copy of the column and remove the duplicates. If nothing is removed there weren’t any duplicates
- Conditional Formatting highlight the duplicates
- Formula compare =COUNTA(Data!A2:A27) with =SUM(1/COUNTIFS(Data!A2:A27,Data!A2:A27)) (array formula)
- Find Excel’s find feature can search for a specific lookup_value (shortcut = ‘Ctrl F’)
Did I miss any?
Multiple Matches
If countif shows multiple matches what should we do?
- The first match wins (regular vlookup or index/match)
- The last match wins =LOOKUP(1,1/(Data!$A$2:$A$27=$C6),Data!$B$2:$B$27)
- Create a concatenated key to properly identify the value
About Me
My name is Kevin Lehrbass. I’m a Data Analyst.
I live in Markham Ontario Canada.
When working with data assumptions are dangerous.
Over the years I’ve found many ways to double check results even when I’m in a hurry.