Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
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
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
2  
+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
add comment

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).

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.