How to Return a State Based on City and Date in Excel
Question details
The user needs to retrieve the correct state for a city based on a selected date, accounting for historical changes in the city's assigned state over time.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing a two-way lookup where rows contain cities, column headers contain effective dates, and the target is to find the closest past date.
- Observed behavior
- The goal is to accurately identify the state assigned to a city at a specific point in time using an exact match for the city and a less-than-or-equal-to match for the date.
Ensure that your data table is structured correctly with cities listed in the rows and effective dates stored as column headers. The date headers must be sorted in ascending order for the approximate match function to work properly.
Use INDEX and XMATCH for a Two-Way Date Lookup
This method uses the XMATCH function's ability to find the exact date or the next smallest item, making it perfect for finding historically effective dates.
The XMATCH function allows for precise control over match types. By setting the match mode to -1, Excel will look for the exact date or the most recent earlier date, which is essential when dealing with changing assignments over time.
Make sure your cities are in the first column of your table (e.g., Table1[City]) and your dates are in the header row. Verify that the date headers are sorted from oldest to newest.
Use XMATCH([@City], Table1[City]) to find the exact row index for the specified city.
Use XMATCH([@Date], DATEVALUE(Table1[[#Headers],[2024-08-01]:[2024-08-20]]), -1) to find the column index. The '-1' argument ensures it returns the latest date less than or equal to the lookup date.
Wrap both XMATCH formulas inside an INDEX function that references the data range: =INDEX(Table1[[2024-08-01]:[2024-08-20]], XMATCH([@City], Table1[City]), XMATCH([@Date], DATEVALUE(Table1[[#Headers],[2024-08-01]:[2024-08-20]]), -1)).
Perform Complex Lookups Seamlessly with WPS Office
WPS Spreadsheet offers full support for advanced lookup functions like INDEX and XMATCH, making it easy to analyze dynamic data such as changing city and state assignments over time.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the city and date matrix.
- 2. Enter the lookup formula: Select the target cell and type your INDEX and XMATCH combination formula directly into the formula bar.
- 3. Apply and drag to copy: Press Enter to calculate the result. You can then drag the fill handle down to apply the lookup formula to other rows.
Frequently Asked Questions
Why is my XMATCH formula returning an #N/A error?
This usually happens if the lookup value does not exist in the lookup array, or if the dates in the headers are not sorted in ascending order when using the -1 match mode. Also, ensure your header text is converted to real dates using DATEVALUE.
Can I use VLOOKUP or HLOOKUP for this scenario instead?
VLOOKUP and HLOOKUP are not ideal for two-way approximate lookups where both rows and columns are dynamic. INDEX combined with XMATCH or MATCH provides the flexibility required for both row and column indexing based on multiple criteria.
Does WPS Spreadsheet support the XMATCH function?
Yes, WPS Spreadsheet supports the XMATCH function, allowing you to use the exact same formula syntax as you would in Microsoft Excel for advanced array lookups.
What does the -1 argument do in the XMATCH function?
The -1 argument tells the XMATCH function to look for an exact match. If an exact match is not found, it returns the next smallest item. In the context of dates, this means finding the most recent earlier date.
