Use Excel INDEX MATCH to Return the Record Valid Today
Question details
Find the record matching a lookup value whose validity period includes today.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A lookup value appears in multiple rows, each with a valid-from and valid-until date.
- Observed behavior
- The desired formula returns the matching record where the valid-from date is on or before today and the valid-until date is on or after today.
Check that your validity dates are stored as Excel dates, not text. Identify the lookup, result, valid-from, and valid-until columns, and make sure their ranges cover the same rows.
Match the lookup value and validity dates with INDEX MATCH
Use this formula to return the first matching row whose validity period includes today.
In this example, B7:B14 contains lookup values, C7:C14 contains results, D7:D14 contains valid-until dates, and E7:E14 contains valid-from dates. The comparisons include both the start and end dates.
Type the value you want to find in cell C2.
Enter =TODAY() in cell C3. The date updates when the workbook recalculates.
Select a blank result cell and enter =INDEX(C7:C14,MATCH(1,(B7:B14=C2)*(D7:D14>=C3)*(E7:E14<=C3),0)). Adjust the ranges if your columns or rows differ.
Press Enter in Microsoft 365. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.

Find a Record Valid Today in WPS Spreadsheets
Use INDEX MATCH and TODAY in WPS Spreadsheets to find the first matching record within its validity period.
- 1. Open your workbook: Open the spreadsheet in WPS Spreadsheets and identify the lookup, result, valid-until, and valid-from columns.
- 2. Enter the criteria: Enter the lookup value in C2 and type =TODAY() in C3.
- 3. Add the formula: In a blank cell, enter =INDEX(C7:C14,MATCH(1,(B7:B14=C2)*(D7:D14>=C3)*(E7:E14<=C3),0)), adjusting the ranges to match your sheet.
- 4. Confirm and check the result: Press Enter and check the returned value. If your WPS version requires array entry, select the formula cell, press F2, and confirm with Ctrl+Shift+Enter.

Frequently Asked Questions
What happens if two records are valid today?
MATCH returns the position of the first row that meets all conditions, so INDEX returns that row's value. Reorder the data if the first qualifying row is not the one you want.
Are the valid-from and valid-until dates included?
Yes. The formula uses <= for the valid-from date and >= for the valid-until date, so a record is valid on either boundary date.
Why does the formula return #N/A?
No row may satisfy all three conditions. Check the lookup value in C2, the date in C3, and whether the validity cells contain actual dates rather than text.
How can I show a message instead of #N/A?
Wrap the formula in IFNA, for example: =IFNA(INDEX(C7:C14,MATCH(1,(B7:B14=C2)*(D7:D14>=C3)*(E7:E14<=C3),0)),"No valid record"). In older Excel versions, confirm it with Ctrl+Shift+Enter.




