Excel: Matching on multiple Conditions

Question

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.

A.S.H · Accepted Answer · 2017-04-14 21:41:10Z

2

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.

edited Apr 14, 2017 at 21:41

answered Apr 14, 2017 at 21:22

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel: Matching on multiple Conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related