Formula Errors

Why Excel MATCH Returns #N/A for Equal Time Values in Microsoft 365

Elise WilliamsElise Williams Nov 13, 2026 868 views

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 you start

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.

Solution 1Recommended

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.

1
Select a helper cell for the rounded time

Click into an empty cell next to your calculated time, for example the cell where you currently have E4-D4-F4 or its equivalent.

2
Enter the TEXT and double unary formula

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.

3
Apply the same rounding to the lookup array

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.

4
Update the MATCH or INDEX-MATCH formula

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).

Exact comparisons now work: When both lookup and range values are rounded to the same precision, MATCH finds the time value instead of returning #N/A.

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. 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. 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. 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. 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.
Fix MATCH #N/A for equal time values without changing your lookup logicFully compatible with Microsoft Excel .xlsx files and formulasFree, lightweight, and easy to use on Windows, macOS, Linux, Android, and iOSFamiliar ribbon interface with Excel-like functions
microsoft office alternative - wps office

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.