# EXCEL Getting exact match of 3 cells against anywhere in 3 columns

Copper Contributor

# EXCEL Getting exact match of 3 cells against anywhere in 3 columns

Hi All,

I am new to the forum hope someone can help me with the correct formula for me, I al looking to get an "output" column created like below

 A B C E F G H output I am Legend bla Our Team Legend No A Christmas Story bla A New-year story No A Goofy Movie bla I am Story No C is Cat bla I am Legend Yes D is dog bla C is Cat Yes

I want to get as above and A,B,C cell aligned against columns F, G and H if  yes put that in output column. Thanks in advance

6 Replies

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

Perhaps..

=LET(a, BYROW(A2:C6, LAMBDA(r, TEXTJOIN("|",1,r))),
BYROW(E2:G6, LAMBDA(r, IF(ISNUMBER(XMATCH(TEXTJOIN("|",1,r),a)),"Yes","No"))))

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

@FikturFox  I am getting a formula error - =LET(a, BYROW(A2:C6, LAMBDA(r, TEXTJOIN("|",1,r))),BYROW(E2:G6, LAMBDA(r, IF(ISNUMBER(XMATCH(TEXTJOIN("|",1,r),a)),"Yes","No")))) when I ran it

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

What does the error says? Is it a #Name Error? or what?

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

@FikturFox  the error is

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

Check your formula again. You might be missing something. What excel version are you using?

Here's another approach with helper column if you are not using M365.

In helper column:

=A2&"|"&B2&"|"&C2

**fill down

In results column:

=IF(ISNUMBER(MATCH(E2&"|"&F2&"|"&G2,\$J\$2:\$J\$6,0)),"Yes","No")

--fill down

# Re: EXCEL Getting exact match of 3 cells against anywhere in 3 columns

@FikturFox  Thank you so much really appreciated it works fine now