Forum Discussion
XLOOKUP for multiple lookup ranges
Hello,
I'm trying to populate an interactive calendar with dates that a team member will be working. I have been using the XLOOKUP function to do this and can get it working for a single date. Ideally this needs to be a variable date range as I dont want to have to manually input a new formula into the calendar each time. Can XLOOKUP search multiple ranges? I.e searching to see if the date on the calendar matches either cell $B3:$B4 &:$D3:$D4 and then populating the calendar with the persons name?
current formula is as follows : =XLOOKUP(D8,Sheet1!B3:B23,Sheet1!E3:E23)
Screenshots attached.
In D5:
=LET(r, SUM((Sheet1!$B$3:$D$23=D4)*(ROW(Sheet1!$B$3:$E$23)-ROW(Sheet1!$E$3)+1)), IF(r=0, "", INDEX(Sheet1!$E$3:$E$23, r)))
Fill to the right to J5.
Select D5:J5 and copy the selection.
Paste to D7, D9, D11 and D13.
Hi HansVogelaar . Thank you, that seems to do the trick. Any ideas on if I have a clash of dates, i.e John and Chris are both required to work the same date. I was thinking the easiest way would be to add another row under each date, so they can be separated out. Not sure on how that would affect the formula to first identify the name and populate in the correct row.
You don't have to insert a new row.
Change the formula in D5 to
=TEXTJOIN(", ", TRUE, FILTER(Sheet1!$E$3:$E$4, BYROW(Sheet1!$B$3:$D$4, LAMBDA(r, ISNUMBER(XMATCH(D4, r)))), ""))
Then fill and copy as before. For dates on which both Chris and John should work, you'll see
Chris, John
in the cell.
