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

Copper Contributor

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


Iam LegendblaOurTeamLegendNo
AChristmas Story blaANew-yearstoryNo
Goofy Movie blaIamStory No
Cis CatblaIam LegendYes
Dis dogblaCis CatYes


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



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


@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

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


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:


**fill down

In results column:


--fill down


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