# XLOOKUP for multiple lookup ranges

Copper Contributor

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

4 Replies

# Re: 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.

# Re: XLOOKUP for multiple lookup ranges

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.

# Re: XLOOKUP for multiple lookup ranges

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.

# Re: XLOOKUP for multiple lookup ranges

@HansVogelaar Thank you, worked perfectly for what I needed.