How to Look Up a Manager Based on Location and Date in Excel
Question details
The user needs an Excel formula to return a specific person's name based on two matching criteria: location and date. If no match is found, it should display 'No match'.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a data lookup table where personnel assignments are tracked by location and date, requiring conditional data extraction based on multiple parameters.
- Observed behavior
- Needs a dynamic formula to cross-reference rows and columns, returning the matched result or a custom 'No match' text when the criteria are not met.
Ensure your data table is organized with locations in a single column and dates or managers structured in a predictable matrix to allow accurate reference lookups without overlapping ranges.
Use OFFSET, MATCH, and IF for Two-Criteria Lookup
Utilize a combination of OFFSET and MATCH to locate the correct row, and COUNTIF/IF to validate the date criteria, returning 'No match' for failed validations.
This method uses the MATCH function to find the relative position of the location. The OFFSET function then shifts the reference to the corresponding row. Finally, COUNTIF and IF are used to check if the date exists in that row.
Ensure you have designated cells for your input criteria. For example, let cell B2 contain the target location and cell D2 contain the target date.
Click on the cell where you want the manager's name or the match result to be displayed.
Enter the formula: =IF(COUNTIF(OFFSET($K$1:$M$1,MATCH(B2,$J$2:$J$6,0),0),D2),B2,"No match"). Modify the ranges ($J$2:$J$6 for the location list, $K$1:$M$1 for the data array) to fit your actual dataset.
Press Enter. If the location and date match the matrix, the formula will process the result; otherwise, it displays 'No match'.
Wrap the formula with IFERROR, like =IFERROR(your_formula, "No match"), to gracefully handle cases where the location itself is not found in the initial MATCH lookup.
Perform Complex Lookups Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like OFFSET, MATCH, INDEX, and XLOOKUP. Handle complex data matrices, manage personnel records, and execute multi-criteria lookups with zero lag.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the manager and location data.
- 2. Insert the Function: Click on the 'Formulas' tab and use the 'Insert Function' tool to guide you through building complex OFFSET and MATCH formulas step-by-step without syntax errors.
- 3. Analyze the Results: Use the 'Evaluate Formula' feature under the Formulas tab to verify that your multiple criteria are matching correctly and to see exactly how the nested functions calculate.
Frequently Asked Questions
Can I use INDEX and MATCH instead of OFFSET?
Yes, INDEX and MATCH is often preferred for multi-criteria lookups because it is non-volatile and calculates faster on large datasets. You can use an array formula like =INDEX(ManagerRange, MATCH(1, (LocationRange=B2)*(DateRange=D2), 0)) combined with IFERROR to achieve a similar, often more robust result.
Why is my formula returning an #N/A error?
An #N/A error typically occurs when the MATCH function cannot find the specified lookup value (e.g., the location in cell B2). Check for trailing spaces or spelling differences between your criteria cell and the data list.
How do I return 'No match' for #VALUE! or #N/A errors?
Wrap your entire lookup formula in the IFERROR function. For example, typing =IFERROR(your_lookup_formula, "No match") will catch any error type and replace it with your custom text automatically.
