How to Flag Multiple Matches In Your VLOOKUP Formula?

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


3

No Responses

Show all responses

Leave a Reply