Why Excel MATCH Returns #N/A for Equal Time Values in Microsoft 365
Question details
Resolve Excel MATCH returning #N/A for time values that appear identical because calculated times contain hidden floating-point differences.
- Product
- Microsoft Excel / WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is using LARGE to identify duplicate calculated time values and then INDEX-MATCH or MATCH to locate those values in a range.
- Observed behavior
- MATCH or INDEX-MATCH returns #N/A even when the time values look identical to the values returned by LARGE.
Before editing the formula, expand the time cell formatting to show seconds or more decimal places so the hidden floating-point difference becomes easier to diagnose, and confirm whether the lookup values are calculated results rather than manually typed times.
Round Calculated Times to Minute Precision with TEXT and Double Unary
Use this method when minute-level precision is sufficient. It eliminates hidden binary differences by formatting the calculated time as text and converting it back to a number.
Excel stores time as a fraction of a day. Calculated times such as E4-D4-F4 can contain floating-point leftovers beyond the fifteenth significant digit, so MATCH and INDEX-MATCH may fail even when the displayed values look equal.
Click into an empty cell next to your calculated time, for example the cell where you currently have E4-D4-F4 or its equivalent.
Type =--TEXT(E4-D4-F4,"[h]:m") and press Enter. The TEXT function rounds the result to minute precision, and the double unary -- converts the text back to a numeric time value.
Create a helper column that uses the same formula for every time value in the MATCH lookup range so both sides are compared at the same minute precision.
Change the MATCH formula so the lookup value points to the helper cell and the lookup array points to the helper column, for example =MATCH(G4,$H$4:$H$20,0).
Round to Minute Precision with ROUND for Numeric Arrays
Use this numeric alternative when you prefer to keep lookup values as numbers and do not want text-format conversion.
Use WPS Office Spreadsheets to Fix MATCH #N/A with Rounded Time Values
In WPS Office Spreadsheets, you can use the same TEXT and double unary formula to round calculated times before MATCH, preserving compatibility with Microsoft Excel workbooks while working in a free, familiar spreadsheet interface.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open the affected .xlsx workbook, and go to the sheet containing the calculated time values.
- 2. Create a helper column for rounded time values: In an empty column, enter =--TEXT(E4-D4-F4,"[h]:m") for the first row and fill down for all rows in the lookup range.
- 3. Point the MATCH formula to the helper cells: Update the MATCH or INDEX-MATCH formula to use the helper lookup value and helper lookup column, for example =MATCH(H4,$I$4:$I$20,0).
- 4. Confirm the #N/A is resolved: Check that the formula now returns the expected row position for the duplicate time value. Save the workbook in .xlsx format to keep Excel compatibility.

Frequently Asked Questions
Why does MATCH return #N/A when two time values look exactly the same?
Excel stores time as a fraction of a day, and calculated times can carry tiny floating-point differences beyond the displayed precision. MATCH compares the stored values, so use a rounding helper like =--TEXT(E4-D4-F4,"[h]:m") before matching.
What formula rounds a time to minute precision for MATCH?
Use =--TEXT(E4-D4-F4,"[h]:m"). TEXT formats the time as hours and minutes, and the double unary -- converts that text back into a numeric time value.
Can I use MROUND to fix MATCH #N/A with time values?
MROUND can work only if you explicitly control the rounding multiple. For minute precision, =ROUND((E4-D4-F4)*1440,0)/1440 is a safer numeric alternative, or use the TEXT helper formula.
Why does Excel LARGE find duplicates but INDEX-MATCH misses them?
LARGE may return values that round to the same display, but INDEX-MATCH or MATCH compares the underlying binary values. The equality operator and MATCH can behave differently because of Excel's 15-digit precision limit.
Does this fix work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the same TEXT, double unary, ROUND, MATCH, and INDEX-MATCH formulas, so you can open the workbook and apply the same helper column approach.




