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. Id...
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.