How to Build a Trip Times Lookup Table in Excel

Populate target trip times from a separate Excel table and compare actual durations with conditional formatting based on time values.

Excel trip times table with target lookups and time comparison

What this worksheet needs to do

Return the target trip time from the separate Target Times table using a matching key.

Check whether the actual total time in column K is above or below the expected value in column L.

Use conditional formatting in column M so the status is easy to scan across the table.

The key point is that Excel stores times as numbers. A value formatted as [hh]:mm is still numeric, so you can compare it directly to another numeric time value as long as both columns use the same time basis.

• Column J should return a target duration from the lookup table.
• Column K contains the actual total time, displayed as [hh]:mm.
• Column L must hold a comparable numeric time value, not plain text.
• Column M can then flag whether the actual trip is higher or lower than target.

Set up the lookup and comparison logic

Use the matching field that exists in both tables, then compare actual and target durations with direct numeric rules.

Return the target trip time into column J

In the first data cell of column J, enter a lookup formula that matches the trip key in your main table to the corresponding record in Target Times. If your Excel version supports it, use XLOOKUP; otherwise use INDEX/MATCH or VLOOKUP.

• XLOOKUP pattern: =XLOOKUP(lookup_value,'Target Times'!lookup_column,'Target Times'!target_time_column)
• VLOOKUP pattern: =VLOOKUP(lookup_value,'Target Times'!table_range,column_number,FALSE)
• Copy the formula down column J and format the returned values as time if needed.

Expected result: each row in column J shows the matching target duration from the separate table instead of a manually typed value.

Make sure columns K and L are truly comparable

Column K is already formatted as [hh]:mm, which is suitable for durations over 24 hours. Column L can be formatted as a number, but it still needs to represent the same underlying time value if you want a valid comparison.

• If column L stores time as an Excel serial value, the comparison will work directly.
• If column L is plain text or whole minutes, convert it first so both columns use the same unit.
• A quick check is to temporarily format both columns as Number and confirm they are on the same scale.

Verification step: pick one row where you know the expected outcome, then confirm that a larger numeric duration in K is treated as greater than L.

Highlight whether the total time is higher or lower

Select the cells in column M that should display the status. Then create formula-based conditional formatting rules that compare the row values in columns K and L.

• In Excel, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• Set the Applies to range to your column M cells, such as =$M$2:$M$200.
• If column M contains text labels, use formulas that reference K and L while formatting M.

A clean trip time sheet depends on matching keys and true time values

Pull the target duration into column J with a lookup, then compare K and L only after confirming both are stored as compatible numeric time values. Once that is in place, column M can flag over, under, or exact target results reliably.

Build and review trip time tables faster

Use a spreadsheet app that supports lookup formulas, time formatting, and conditional formatting so your target and actual trip durations stay easy to audit.

Complete This Workflow with WPS Office

Use Word, Excel, and PPT for FREE

Free Download

WPS Office can build this lookup-and-comparison workflow directly in WPS Spreadsheets.

1. In the first result cell in column J, enter an XLOOKUP, VLOOKUP, or supported equivalent that matches the trip key to the Target Times table.
2. Fill the formula down and format the returned values as duration values such as [hh]:mm. Confirm columns K and L use the same numeric time basis.
3. Select the status range in column M and add formula rules such as =$K2>$L2, =$K2<$L2, and =$K2=$L2.
4. Test one known over-target trip, one under-target trip, and one exact match before relying on the complete table.

WPS AI can explain the formulas, but verify lookup keys and time units against known trips.

Build a Trip Times Lookup Table in Excel FAQs

Why does column J return the wrong target trip time?

The lookup field in the main table must match the key in the Target Times table exactly. Check for extra spaces, mismatched codes, or a lookup range that does not include the target time column.

Can I compare [hh]:mm in column K with a number in column L?

Yes, but only if the number in column L is the same underlying Excel time value. If L stores text or a different unit such as whole minutes, convert it before applying the rule.

How do I show a neutral result when the actual time equals the target?

Add a third conditional formatting rule using =$K2=$L2 . Apply a separate fill or font style so exact matches are easy to distinguish from over and under target rows.

What is the quickest way to verify that the conditional formatting is correct?

Temporarily edit one row so column K is clearly greater than L, then test another row where K is lower than L. If column M changes format correctly in both cases, the rule references are set properly.