SOLVED

# Xlookup formula will only look up values in one column not two

Copper Contributor

# 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.

2 Replies

# Re: Xlookup formula will only look up values in one column not two

=XLOOKUP(G2,RSVP!\$I:\$I&RSVP!\$J:\$J,RSVP!\$A:\$A,"")

best response confirmed by Lhelgert (Copper Contributor)
Solution

# Re: Xlookup formula will only look up values in one column not two

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)

1 best response

Accepted Solutions
best response confirmed by Lhelgert (Copper Contributor)
Solution

# Re: Xlookup formula will only look up values in one column not two

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)