How to Fix VLOOKUP When It Fails to Match Time Values in Excel
Question details
The user needs to troubleshoot and resolve VLOOKUP formula errors that occur when attempting to find and match 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.
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.
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.
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.
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)
Press Enter to execute the formula, then drag the fill handle down to apply it to the remaining rows in your dataset.

Strip Hidden Dates Using the MOD Function
If your time cells contain a date component (which translates to an integer > 0 in Excel's serial number system), the MOD function extracts just the time portion for a successful exact match.
Remove Seconds Mismatches Using the TIME Function
When differences down to the second prevent a match, reconstructing the time using only the hour and minute guarantees an exact match.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the time datasets.
- 2. Select the formula cell: Click the cell where you want the VLOOKUP output.
- 3. Input the VLOOKUP formula: Type your formula, combining VLOOKUP with MOD or TRUE as needed.
- 4. Calculate: Press Enter. WPS Spreadsheet processes the formula instantly, just like Excel.

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.




