3

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?

Simon Sheehan
  • 9,204
Dani
  • 753

2 Answers2

5

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.

Mike Fitzpatrick
  • 17,427
0

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

Xantec
  • 2,479