CODEWITHSUNDEEP
excelif-statementmatchvlookup
florence-y
florence-y

Reputation: 871

EXCEL: Check if value in cell has more than one value under it in adjacent row

Sorry, I tried my best to frame my question in the title! But here's what I'm trying to do:

I have an excel sheet of two columns, A and B. I am trying to check if a value underneath A has two or more values underneath it in B. See below:

A               B
France          Paris
France          Paris
France          Nice
California      Los Angeles
California      Los Angeles
Japan           Tokyo
Japan           Tokyo

In here, I am trying to report back that France is a value in column A that has more than one value in B (Paris and Nice). How do I write a comparison formula in Excel that will allow me to do this?

I thought about doing something along the lines of doing VLOOKUP to report back the first row (France --> Paris, California --> Los Angeles, Japan --> Tokyo), and if the value in B does not equal the value in the first row, report that back to me.

Ex. in C4, it's supposed to anticipate Paris but a conditional will throw False since it's equal to Nice.

TIA!

Upvotes: 0

Views: 361

Answers (1)

Joe Beck
Joe Beck

Reputation: 431

A VLOOKUP can be paired with an IF statement to work like this. First of all you will need a key with all the desired match-ups. And you can add a 3rd column to report if it's good or bad. Then you can have this:

enter image description here

You can copy & paste here to test it yourself:

=IF(B6 = VLOOKUP(A6,$E$4:$F$6,2,FALSE), "GOOD", "BAD")

Upvotes: 0

Related Questions