How to Compare Dates Across Excel Worksheets by Name and Floor Number
Question details
The user needs to compare date records between two worksheets where the data is ordered differently, requiring a match on both the person's name and their floor number.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing records across multiple sheets to verify matching dates based on two specific conditions.
- Observed behavior
- Data is not sorted in the same order, requiring a multi-criteria lookup to accurately compare the corresponding date values.
Ensure both worksheets have identical spelling for names and floor numbers, and remove any trailing spaces that might prevent a successful match.
Use INDEX and MATCH for Multi-Criteria Lookup
The most robust way to match two conditions (Name and Floor Number) across unsorted sheets is using an INDEX and MATCH array formula.
By concatenating the lookup values within the MATCH function, you can search for a unique combination of Name and Floor Number in the second worksheet and retrieve the date for comparison.
In your first worksheet, select the cell where you want the comparison result to appear (e.g., cell D2).
Type the formula: =INDEX(Sheet2!$C$2:$C$100, MATCH(1, (Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100=B2), 0)) = C2. Assume Column A is Name, Column B is Floor, and Column C is Date.
Depending on your version, press Ctrl + Shift + Enter to apply the array formula. It will return TRUE if the dates match and FALSE if they do not.
Use XLOOKUP with Multiple Criteria
If you are using a recent version of Excel or WPS Office, XLOOKUP provides a cleaner syntax for multi-criteria comparisons without needing array execution.
Use WPS Spreadsheet to Compare Complex Data Easily
WPS Spreadsheet fully supports advanced array formulas, INDEX/MATCH, and modern lookup functions like XLOOKUP, making cross-worksheet data comparison incredibly smooth.
- 1. Open your workbooks in WPS Spreadsheet: Launch WPS Office and open the file containing the worksheets you need to compare.
- 2. Apply the lookup formula: Use the INDEX/MATCH or XLOOKUP formulas directly in the formula bar, just as you would in Excel.
- 3. Evaluate and filter results: Use WPS Spreadsheet's built-in Data Filter tool to quickly isolate rows where the date comparison returns FALSE.
Frequently Asked Questions
Why does my INDEX MATCH formula return an #N/A error?
An #N/A error usually means the exact combination of Name and Floor Number could not be found in the second worksheet. Check for hidden spaces, spelling differences, or ensure your lookup ranges are locked with absolute references (e.g., $A$2:$A$100).
Can I compare dates if they are formatted differently in the two sheets?
Yes, as long as both cells contain valid date serial numbers. However, if one sheet stores dates as text, the comparison will fail. You can use the DATEVALUE function to convert text dates into serial numbers before comparing.
Is there a way to highlight mismatched dates instead of showing TRUE/FALSE?
Yes. You can use Conditional Formatting. Select your date column, go to Conditional Formatting > New Rule, and use the same INDEX/MATCH formula to format cells where the dates do not match.
