Hello Excellers, yes it is #FormulaFriday again.  Time for some more #Excel formula fun.  The VLOOKUP is really great for pulling in data from a larger table in Excel, but sometimes in larger data sets,  you can come across multiple values that could be returned from your VLOOKUP.  That is to say, more than one match could be available.  So just how do we know if there are possible multiple matches?.

Use VLOOKUP with IF Statement.

We can use the VLOOKUP Formula and this time combine it with an IF statement to flag if there are multiple matches in your data.  I will work through an example.  Here is an extract of a larger data set.  It is the sales of machine parts by City and State.

Let’s create a usual VLOOKUP formula and see what is returned.

We can see that Excel has correctly returned the first value it has come across in the data set.  However,  in this instance, we need to know if there is indeed a second or third value (multiples) in the data set.  We can easily do this by inserting an IF statement.

Let’s talk through the formula.

We begin with off an IF statement.

The criteria of the IF statement is COUNTIF(C:C,H4)>1. What this does is count all the instances of cell H4 in Column C.

So, this example counts 3 instances of CA. Since this is greater than 1, it displays the TRUE value.

The TRUE value to be returned is “MULTIPLE ANSWERS”. If the instances were not greater than 1, then it would display the FALSE result, which is the actual VLOOKUP.  How cool is that?.

We can now drag the formula down just like a regular VLOOKUP formula and identify any multiples matches in our data.

 

 

 

If you want more Excel tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays and Formula Friday Series Click The Link Below

Macro Mondays Blog Posts.

Formula Friday Blog Posts.