SOLVED

# Excel XLOOKUP 2 column lookup array

Copper Contributor

# Excel XLOOKUP 2 column lookup array

Can someone help with this formula please?

Essentially want the staff sheet to autofill from the rooms sheet. As there are two columns to be searched (C4-D36 for example for 9-11), my basic skill is not living up to demand..

Help is very much appreciated!

4 Replies
best response confirmed by Help_appreciated (Copper Contributor)
Solution

# Re: Excel XLOOKUP 2 column lookup array

In C5:

=LET(a,XLOOKUP(\$B5,Rooms!C\$5:C\$36,Rooms!\$B\$5:\$B\$36,""),b,XLOOKUP(\$B5,Rooms!D\$5:D\$36,Rooms!\$B\$5:\$B\$36,""),c,TEXTJOIN(",",TRUE,a,b),IF(c="","Empty",c))

(You may want to use "Check" instead of "Empty" - that's up to you.

# Re: Excel XLOOKUP 2 column lookup array

Legend! Thanks Hans!
Is there a reason to use Check! over empty or any other word?

# Re: Excel XLOOKUP 2 column lookup array

You had "Empty" in some of the columns and "Check" in other ones - I don't know why. You can use either, or "" if you want to leave cells without appointments blank.

# Re: Excel XLOOKUP 2 column lookup array

Good to know. Yes I was just playing around with it.

Thanks again

1 best response

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

# Re: Excel XLOOKUP 2 column lookup array

In C5:

=LET(a,XLOOKUP(\$B5,Rooms!C\$5:C\$36,Rooms!\$B\$5:\$B\$36,""),b,XLOOKUP(\$B5,Rooms!D\$5:D\$36,Rooms!\$B\$5:\$B\$36,""),c,TEXTJOIN(",",TRUE,a,b),IF(c="","Empty",c))

(You may want to use "Check" instead of "Empty" - that's up to you.