CODEWITHSUNDEEP
excel-formula
user2654953
user2654953

Reputation: 221

Excel: Matching on multiple Conditions

How do I test for multiple conditions using Excel.

For Example:

I have Text in A2 'foo' and for each cell in column A:A that has 'foo' I want to Match B2 'Bar' to each cell in Column B:B. If the cell in A3 contains 'foo' and B3 contains 'bar', output "Match", Else "NA"

I have tried =IF(AND(A2=A:A,B2=B:B),"Match","NA"), but it does not work properly.

Upvotes: 2

Views: 83

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Try using COUNTIFS:

=IF(COUNTIFS(A:A,A2,B:B,B2)>1,"Match","NA")

Your idea was almost correct but faced the fact that the AND and OR operator in Excel are not array friendly, and also the fact that you already have a match on the line you are checking, so you need to have "more than 1" matches.

Upvotes: 2

Related Questions