This article will explore the Excel VLOOKUP Formula and show you how to combine it with an IF statement to flag if there are multiple lookup values matches in your data set.
VLOOKUP is really great for pulling in data from a larger table in Excel, but sometimes in a larger data sets there may be multiple values that could be returned from your VLOOKUP match.
Here is an extract of a larger data set to explore an example with. It contains the sales units of machine parts by City and State. My data starts in B3 and ends in D10 in my Excel work sheet.
Troubleshoot VLOOKUP() Formula – The Common Mistakes
| City State | Parts | Sold |
| Jacksonville | FL | 474047 |
| Los Angeles | CA | 299723 |
| Orlando | FL | 405426 |
| San Fran | CA | 473695 |
| Atlanta | GA | 326085 |
| Rochester | NY | 264132 |
| San Diego | CA | 310811 |
Lets go ahead and create a simple VLOOKUP formula and see what is returned. The formula reads as below –
=VLOOKUP(H4,C3:D10,2,FALSE)
my lookup value is contained in cell H4.
We can see that Excel has correctly returned the first value it has come across in the data set when it has matched the lookup value of CA displaying 299723 as the result.
We may however need to know if there is in our data, a second or third value (multiples) in the data set. We can easily answer this question by inserting an IF statement into our VLOOKUP formula to give us the answer.
Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
Let’s talk through the formula that we can use.
=IF(COUNTIF(C:C,H4)>1,"MULTIPLE ANSWERS",VLOOKUP(H4,C:D,2,FALSE)
We begin with the IF Statement.
The criteria of the IF statement is COUNTIF(C:C,H4)>1.
What this does is count all the instances of cell values of H4 in Column C. So this example counts that there are 3 instances of CA in our data set. Since this is greater than 1, it displays the TRUE value which we can set to be anything we want it to be.
How to VLOOKUP with Multiple Criteria in Multiple Column
The TRUE value to be returned in my example are the words “MULTIPLE ANSWERS”. If the instances were not greater than 1, then it would display the FALSE result, which is the actual VLOOKUP which Excel will then return the result of.
We can now drag the formula down just like a regular VLOOKUP formula, and identify any multiple matches in our data by displaying “MULTIPLE ANSWERS”.