SOLVED

Excel XLOOKUP 2 column lookup array

Copper Contributor

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!

 

https://docs.google.com/spreadsheets/d/1jtiOQ5LFIKMvYSiRGvFh03CoAww_B2_W/edit?usp=sharing&ouid=11661...

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

@Help_appreciated 

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.

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

(Replying from an alternative account)
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.

@Hans_Vogelaar 

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

@Help_appreciated 

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.

View solution in original post