Function Problems

How to Fix VLOOKUP When It Fails to Match Time Values in Excel

Steve KSteve K Nov 10, 2026 869 views

Question details

The user needs to troubleshoot and resolve VLOOKUP formula errors that occur when attempting to find and match time values.

How to Fix VLOOKUP Not Matching Time Values
Product
Excel
Device & OS
not provided
Scenario
Using the VLOOKUP function to search for a specific time or time range within a dataset to retrieve associated data.
Observed behavior
The VLOOKUP formula returns an #N/A error despite visual matches, usually caused by hidden dates, unseen seconds, or floating-point precision discrepancies in the time values.
Before you start

Click on the cells containing your time values and check the Formula Bar to ensure there are no hidden dates attached (e.g., '1/1/1900 10:00:00 AM') before applying your VLOOKUP.

Solution 1Recommended

Use Approximate Matching for Time Ranges

By setting the VLOOKUP range_lookup argument to TRUE, you can match the closest time value, avoiding errors caused by exact precision mismatches.

When dealing with time ranges or fractional time differences, exact matching (FALSE) often fails. Using approximate matching (TRUE) tells Excel to find the exact match or the next largest value that is less than the lookup value.

1
Sort the lookup table

Select your entire lookup table. Go to the Data tab on the ribbon and click 'Sort A to Z' to sort the first column (the time values) in ascending order. This is strictly required for approximate matching to work.

2
Enter the VLOOKUP formula

Click the cell where you want the result. Type the formula replacing the arguments with your data: =VLOOKUP(A2, $H$1:$I$6, 2, TRUE)

3
Apply the formula

Press Enter to execute the formula, then drag the fill handle down to apply it to the remaining rows in your dataset.

Use Approximate Matching for Time Ranges
Sorting is Critical: If you use TRUE for the range_lookup argument and your lookup table is not sorted in ascending order, VLOOKUP will return incorrect results.
Solve Spreadsheet Issues Easily

Master Time Formulas Seamlessly in WPS Spreadsheet

WPS Spreadsheet is a robust, free alternative to Microsoft Excel that perfectly handles advanced lookup functions, time tracking, and data analysis. Whether you are using VLOOKUP, XLOOKUP, or complex time calculations, WPS Office guarantees a smooth and highly compatible experience.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the time datasets.
  2. 2. Select the formula cell: Click the cell where you want the VLOOKUP output.
  3. 3. Input the VLOOKUP formula: Type your formula, combining VLOOKUP with MOD or TRUE as needed.
  4. 4. Calculate: Press Enter. WPS Spreadsheet processes the formula instantly, just like Excel.
100% compatibility with Microsoft Excel (.xlsx) formats and VLOOKUP functionsBuilt-in error checking to easily spot precision issues with time valuesFamiliar ribbon interface with zero learning curveCompletely free to download and use for all your data needs
microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return #N/A when the time looks exactly the same?

Spreadsheet software stores time as a decimal serial number. Even if two cells both visually display '10:00 AM', one might contain a hidden fractional second (e.g., 10:00:00.002) due to floating-point precision from a previous calculation. These microscopic differences cause exact match (FALSE) VLOOKUPs to fail.

How can I check if my time value has a hidden date?

Click the cell containing the time and look at the Formula Bar at the top of the screen. If the cell displays '12:00 PM' but the Formula Bar shows '1/1/2023 12:00:00 PM', the cell has a hidden date component affecting your VLOOKUP.

Can I format the cells as Text to fix VLOOKUP time errors?

Yes, you can use the TEXT function to force the time into a specific text format before looking it up. For example: =VLOOKUP(TEXT(A2, "hh:mm"), Lookup_Table, 2, FALSE). Note that your lookup table column must also be formatted as text matching the 'hh:mm' structure.

Is XLOOKUP better for handling time ranges?

Yes, XLOOKUP provides more flexible matching arguments. By using XLOOKUP with a match mode of -1 (exact match or next smaller item), you can perform time range lookups without requiring the source data to be sorted in ascending order, unlike VLOOKUP.