Forum Discussion
XLOOKUP for multiple lookup ranges
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.
HansVogelaar Thank you, worked perfectly for what I needed.
