# Excel lookup based on times

I have a table of data

``````number  LowTime HighTime
2          0:10:10   0:11:23
4          0:11:24   0:12:55
``````

I want to find in this table the number value for a given time

if I have in my cell 11:25 (it's between 11:24 and 12:55) I want to get in the cell next to it the number 4 from the table.

How can I do it?

-
The short answer is VLOOKUP. Long answer i'm not sure of off-hand. I haven't really used times. But VLOOKUP is certainly the function to use for it, i've used it before. –  barlop Oct 28 '10 at 11:05
Can Vlookup work with criteria ? –  Dani Oct 28 '10 at 11:34

If your time periods are (A) non-overlapping, (B) in ascending order and (C) contain no gaps then you can use VLOOKUP with approximate match (the 4th parameter to VLOOKUP being TRUE or omitted).

You'll also need to reposition the number column somewhere to to right of the LowTime column since you'll want to have VLOOKUP search in LowTime and return from number.

-
+1, But I'd like to add another criteria: (C) not disconnected. If "Item1" is 10-11 and "Item2" 12-13 (with 11-12 being nothing in this example) a lookup for the value 11.5 will return "Item1" because 10 <= 11.5 < 12. `VLOOKUP` doesn't understand upper bounds. The fix here is to add a "empty" value for the 11-12 range, so a lookup of 11.5 returns "empty" instead. –  DMA57361 Oct 28 '10 at 12:06
@DMA57361: +1, Thanks, that hadn't occurred to me but makes total sense. I've edited my answer to include your criteria. Good explanation. –  Mike Fitzpatrick Oct 28 '10 at 12:27
10x, figured it out already, but this is the right answer. –  Dani Oct 28 '10 at 13:16

While VLOOKUP is probably the simpler solution, the following formula will also work:

``````=INDIRECT(CONCATENATE("C",MATCH(Input,LookupRange,1)))
``````

where C is the letter of your "number" column, Input is the cell that your look up value is in and LookupRange is the array of cells, organized vertically, that you want to compare Input to.

Also, you may need to offset the return value from MATCH if your table does not start in row 1. Example:

``````=INDIRECT(CONCATENATE("C",MATCH(Input,LookupRange,1)+X))
``````

where X is the number of rows to offset.

Also worth experimenting with is the final input for the MATCH formula, as this can affect whether Excel will match in-between values with greater then or less then values (accepted inputs are: -1, 0, 1).

-
Will have to check it out looks interesting approach, and definitely something I wouldn't think of myself. –  Dani Oct 30 '10 at 6:17