How to Search Multiple Date Ranges in Excel with Array Formulas
Question details
The user needs a method to search across multiple nonadjacent date ranges within an interactive calendar to find and return the assigned team member.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an interactive calendar that assigns team members to different, non-continuous date blocks, requiring a combined lookup.
- Observed behavior
- Needs a dynamic formula capable of testing multiple date ranges simultaneously to extract the matching name when a specific calendar date is queried.
Ensure your interactive calendar is set up with clearly defined date ranges and a corresponding column for team members. Verify that your version of Excel or WPS Spreadsheet supports dynamic array functions like LET.
Use a Dynamic Array Formula with LET, SUM, and INDEX
This method elegantly tests multiple date ranges at once using boolean logic and returns the matching team member by finding the correct relative row number.
While XLOOKUP is excellent for straightforward column-to-column searches, querying a 2D grid of nonadjacent date ranges requires a more advanced approach. By combining LET, SUM, and INDEX, you can create a Boolean test that sweeps through the entire calendar range simultaneously.
Click on the cell in your interactive calendar where you want the corresponding team member's name to appear (for example, cell D5).
Enter the following formula into the formula bar: =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)))
Modify the range Sheet1!$B$3:$D$23 to match your actual date grid, D4 to your target lookup date, and Sheet1!$E$3:$E$23 to your list of team members.
Select cell D5 and drag the fill handle rightward to cover the week (e.g., through J5). Then, select the range D5:J5, copy it, and paste it into the subsequent starting rows for the next weeks, such as D7, D9, D11, and D13.
Build Advanced Interactive Calendars with WPS Spreadsheet
WPS Spreadsheet provides robust support for modern dynamic array functions including LET, XLOOKUP, and INDEX, making it incredibly easy to search complex date ranges and manage interactive calendars effortlessly.
- 1. Open your calendar file in WPS Office: Launch WPS Spreadsheet and open your existing interactive calendar workbook.
- 2. Apply the advanced array formula: Select your target cell and paste the LET and INDEX combination formula to match dates with your team member list.
- 3. Drag and fill to complete: Use the fill handle to seamlessly apply the formula across all nonadjacent date blocks in your schedule.
Frequently Asked Questions
Can I use XLOOKUP to search multiple columns simultaneously?
Yes, you can nest XLOOKUP or combine it with functions like CHOOSECOLS or VSTACK. However, for a 2D grid of nonadjacent date ranges, using a Boolean sum array with LET and INDEX is generally more straightforward and less formula-heavy.
Why is my LET formula returning a #NAME? error?
The #NAME? error usually occurs if your spreadsheet software version is older and does not support the LET function. Ensure you are using an up-to-date version of Microsoft 365 or the latest update of WPS Office.
How do I handle empty calendar dates that return an error?
The formula provided already handles blank results efficiently using the IF statement: IF(r=0,"",...). If a date is not found in the lookup range, 'r' becomes 0, and the formula outputs a blank string instead of an error.
