Function Problems

How to Match ID and Date Across Excel Worksheets

Steve K Oct 10, 2026 870 views

Question details

The user needs to retrieve a value, such as a score, by matching both a unique ID and a date across different worksheets, but standard lookup formulas are failing to find matches.

How to Match an ID and Date Across Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Looking up records when the lookup criteria involve dates exported from external systems and IDs that must match exactly.
Observed behavior: Standard lookup formulas return No Match or #N/A errors despite the data looking identical visually. This is typically caused by hidden time components in the exported dates or trailing spaces in the text.

Before you start

Verify that your dates are stored as actual date values rather than text. Keep in mind that changing a cell's format to 'Short Date' only changes its visual display; it does not remove any hidden time values embedded in the cell's underlying data.

Solution 1Recommended

Use INDEX/MATCH with the INT Function to Normalize Dates

This method strips hidden time values from exported dates by rounding them down to integers, ensuring an exact date match without altering the original raw data.

Exported reports often include timestamps (e.g., 45000.55) but are formatted to look like standard dates (e.g., dd/mm/yyyy). Because Excel evaluates the underlying decimal value, lookups will fail if one date has a time component and the other does not. The INT function extracts just the whole number portion, which represents the date.

Since we are matching multiple criteria, this will function as an array formula.

Select the destination cell

Click on the cell where you want the retrieved score or value to appear.

Enter the array formula

Type the formula combining INDEX, MATCH, and INT. For example: =INDEX(Sheet2!C:C, MATCH(1, (Sheet2!A:A=A2) * (INT(Sheet2!B:B)=INT(B2)), 0))

Evaluate the formula

If you are using an older version of Excel, press Ctrl+Shift+Enter to evaluate the array formula correctly. In newer Microsoft 365 versions, simply pressing Enter is sufficient.

Use INDEX/MATCH with the INT Function to Normalize Dates

Avoid selecting entire columns if possible: Using entire column references (like A:A) in array formulas can slow down performance. It is better to use specific ranges, such as A2:A1000.

Solution 2

Use the FILTER Function in Microsoft 365

For Microsoft 365 users, the FILTER function provides a cleaner and faster alternative to traditional INDEX/MATCH setups for multiple criteria lookups.

Solution 3

Remove Trailing Spaces from IDs

If normalizing the dates did not fix the problem, invisible trailing spaces in the ID column might be causing the lookup to fail.

WPS Spreadsheet Solutions

Perform Multi-Criteria Lookups Seamlessly in WPS Office

WPS Office Spreadsheet fully supports advanced array formulas, INDEX/MATCH, and data cleanup functions. You can flawlessly execute complex, multi-condition data extractions while enjoying a smooth and lightweight interface.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the mismatched ID and Date data.
2. Set up your INDEX/MATCH criteria: Click your target cell and begin typing your multi-criteria formula: =INDEX(Sheet2!C2:C100, MATCH(1, (Sheet2!A2:A100=A2) * (INT(Sheet2!B2:B100)=INT(B2)), 0))
3. Evaluate as an array: Press Ctrl+Shift+Enter to execute the formula. WPS Spreadsheet will automatically enclose it in curly braces {} and fetch the correct score.

100% compatible with Microsoft Excel (.xlsx) formats and formulasFully supports array evaluation for INDEX, MATCH, and INT functionsBuilt-in robust data cleaning tools to remove hidden spaces effortlesslyFree and lightweight Office suite alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date match visually but not in my formula?

Spreadsheet software stores dates as serial numbers and time as fractions of a day. If your cell format hides the time, visually identical dates might have different underlying decimal values. You must use the INT function to compare only the whole number (the date portion).

Does changing the date format to 'Short Date' fix the matching issue?

No. Changing the format only modifies how the data is displayed on your screen; it does not alter the underlying value in the cell. To remove the hidden time component permanently, you need to use a formula like INT or the Text-to-Columns feature.

What should I do if INDEX/MATCH returns No Match for identical IDs?

Double-check for trailing spaces or hidden non-printable characters in your ID cells. Using the TRIM function on your ID columns will strip out these invisible spaces and allow exact matches to work.

Can I use XLOOKUP to match multiple criteria?

Yes, XLOOKUP can also handle multiple criteria by multiplying the criteria arrays together. The syntax looks like this: =XLOOKUP(1, (Sheet2!A:A=A2) * (INT(Sheet2!B:B)=INT(B2)), Sheet2!C:C, "No Match").

Excel & Data Problems