Function Problems

Use Excel INDEX MATCH to Return the Record Valid Today

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Find the record matching a lookup value whose validity period includes today.

Use Excel INDEX MATCH to Return the Record Valid 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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the lookup value

Type the value you want to find in cell C2.

2
Set today's date

Enter =TODAY() in cell C3. The date updates when the workbook recalculates.

3
Enter the lookup formula

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.

4
Confirm the formula

Press Enter in Microsoft 365. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.

Match the lookup value and validity dates with INDEX MATCH
When no row matches: The formula returns #N/A if no row meets all three conditions. If multiple rows qualify, it returns the first one in the selected ranges.
WPS Spreadsheets

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. 1. Open your workbook: Open the spreadsheet in WPS Spreadsheets and identify the lookup, result, valid-until, and valid-from columns.
  2. 2. Enter the criteria: Enter the lookup value in C2 and type =TODAY() in C3.
  3. 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. 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.
Match a lookup value against a date rangeOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and cells
microsoft office alternative - wps office

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.