Forum Discussion

YJ2023's avatar
YJ2023
Copper Contributor
Feb 21, 2023

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

 

ABCEFGHoutput
Iam LegendblaOurTeamLegendNo
AChristmas Story blaANew-yearstoryNo
A 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 

excel
Formulas and Functions

6 Replies

  • FikturFox's avatar
    FikturFox
    Brass Contributor
    Feb 21, 2023

    YJ2023 

    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"))))

    • YJ2023's avatar
      YJ2023
      Copper Contributor
      Feb 21, 2023

      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

      • FikturFox's avatar
        FikturFox
        Brass Contributor
        Feb 21, 2023
        What does the error says? Is it a #Name Error? or what?

Resources