how to get the specific time group for lookup a cell input?

Originally Posted by tinyau

for example i got a table:
d e
1 a 10:00-12:00
2 b 12:00-14:00
3 c 14:00-16:00

in cell a1 input a time，e.g. 10:05, how can i get the "a" in cell b1?
e.g. input 14:15 in cell a1 then get the result "c" in cell b1. thx experts!

If A1 = 12:00 what result would you expect, A or B?

with index / match or with VLookup if you are willing to change the layout.

See also the question in #2 from Tony Valko

Create table as shown below in columns E and F

10:00 a
12:00 b
14:00 c

format first column as hh:mm

with time in A1 then in B1 =VLOOKUP(A1,E1:F3,2,1)

Originally Posted by tinyau

oh yes。if it is posible，12:00 belongs to b。

OK, you don't need the end time for the intervals but it makes it easier to see the full interval.

I'll assume your times are in whole minutes.

Data Range

	A	B	C	D	E	F
1	12:45	B			From	To
2				A	10:00	11:59
3				B	12:00	13:59
4				C	14:00	15:59
5	------	------	------	------	------	------

Create the table in D1:F4.

Then, this formula entered in B1:

=LOOKUP(A1,E2:E4,D2:D4)

A couple of bits of info...

If the time entered in A1 is less than the lowest time value in the table then the formula will return the #N/A error. We can account for that if you tell us what version of Excel this has to work in and what result you would rather get.

If the time entered in A1 is greater than the highest time value in the table then the formula will return the last (bottom-most) value from column D. If you don't want that then tell us what result you do want.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.