Forum Discussion
Solved
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...
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.
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?
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.
