Forum Discussion
Xlookup formula will only look up values in one column not two
I have a formula that I use to look up a seat number in one tab of a work book and if it is there it returns the name of the person that seat is assigned to in a different tab.
The formula I have been using to search only seems to work of it is looking in one column and I now need it to search for the value with in two columns.
The formula I have been using is:
=IF(ISERROR(XLOOKUP(G2,RSVP!$I:$I,RSVP!$A:$A,)),"",(XLOOKUP(G2,RSVP!$I:$I,RSVP!$A:$A,)))
When I changed it to =IF(ISERROR(XLOOKUP(G2,RSVP!$I:$J,RSVP!$A:$A,)),"",(XLOOKUP(G2,RSVP!$I:$J,RSVP!$A:$A,))) it doesn't return anything or show any error. I am not too sure what the issue is.
or maybe:
=XLOOKUP(G2,RSVP!$I:$I,RSVP!$A:$A, XLOOKUP(G2,RSVP!$J:$J,RSVP!$A:$A,""))alternatively you could use wildcard option unless G2 could be a subset and you don't want that:
=XLOOKUP("*"&G2&"*",RSVP!$I:$I&RSVP!$J:$J,RSVP!$A:$A,"",2)
2 Replies
or maybe:
=XLOOKUP(G2,RSVP!$I:$I,RSVP!$A:$A, XLOOKUP(G2,RSVP!$J:$J,RSVP!$A:$A,""))alternatively you could use wildcard option unless G2 could be a subset and you don't want that:
=XLOOKUP("*"&G2&"*",RSVP!$I:$I&RSVP!$J:$J,RSVP!$A:$A,"",2)
