VLOOKUP with time

Hi serhito,

Difficult question. "time" is a value indicating a specific point in time, expressed as a Date and Time value, which always includes a date part and a time part.

My approach would be to replace the D&T values in the lookup table with text representations that will match the search values in column A of the main table.

A screen shot of the two tables would make a more specific response easier to produce.

Regards,

Barry

To reiterate, your table of "values entered as time format" is actually a table of Date & Time values. You might have it formatted to show the time only but there are dates attached to all of those times. If you try to lookup a "time" of 3:00PM, you must attach a date to it. But if the time and date do not match in your other table, you will not get a result.

Here is an example where I have attached the date of today to the "time". The "time" is in a cell formatted as text and contains a value like "3:00PM"

=LOOKUP(TODAY()+TIMEVALUE(A2),Table 1::A)

I would recommend what Barry said and make another column in your table that is for the time only. Use TIMEVALUE to get a decimal representation of the time. Use TIMEVALUE in your lookup function too. Here is an example that requires Table 1::B = TIMEVALUE(Table 1::A)

=LOOKUP(TIMEVALUE(A2),Table 1::B,Table 1::A)

Here's an example that migth be what you're looking for. The "times" in column A of both tables are Date and Time Values, entered as times. The date part of all of these is the same (January 13, 2014), but that is not necessary to the working of the VLOOKUP formula as it uses the TIMEVALUE in column B. COlumn B of Main may be hidden.

Both tables use the formula below in cloumn B to extract the time value from the D&T value in column A:

B2, and filled down: =TIMEVALUE(A)

All other data on Lookup is directly entered.

Main uses this VLOOKUP formula, entered in C2, then filled down to the end of column C, and filled right to column C.

Main::C2: =VLOOKUP($B,Lookup :: $B:$D,COLUMN()-1,)

(This displays as =VLOOKUP($B,Lookup :: $B:$D,COLUMN()-1,close-match) in Numbers)

Note that the LOOKUP table used by VLOOKUP is columns B - D of Lookup.

Regards,

Barry

"Either I create that column with the time value, or i can fool vlookup by doing the request including the same date that is in the time value in the table."

Unless you create the "column with the time value," there is no "time value in the table". That is true for both tables.

EVERY cell that displays a Time (of day), EVERY cell that displays a Date, and EVERY cell that displays a date and a time (of day) contains a Date and Time Value. Period. Full Stop. The date part (or the time part) could be hidden, but it is still there.

The column labeled "Tval" and the one labeled "Time value" contain numbers. The number is produced by the TIMEVALUE(date-and-time) function. TIMEVALUE() ignores the date part of a Date and Time Value, and returns the fraction of a 24 hour day represented by the time part of that Date and Time Value.

If you use the numerical values produced by TIMEVALUE as the search-for value in VLOOKUP, and the column containing the numerical values produced by TIMEVALUE as the first column of the search-where value in VLOOKUP, the formulas will work as displayed above, and will not be affected by any difference in the date part of the D&T values in column A of the two tables.

If you instead use the D&T values in column A for search-for and search-where, you need to include a date in your search term, ot to "fool" VLOOKUP, but to give it a value that exactly or closely matches one of the values on the lookup table. If you simply enter the time of day as the search value, VLOOKUP will always return the closest match to the Date and Time value resulting from that entry. On any day later than the date the lookup table was created, the close match will be the last item on the table (ie. the Date and Time that is most recent)

Regards,

Barry