What I get is #VALUE.
Did you enter the formula as an array?
Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.
When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.
Consider this example:
................A...............B...............C
1.......8:00 AM....8:30 AM....Class1
2.......8:50 AM....9:20 AM....Class2
3.......9:50 AM..10:45 AM....Class3
E1 = lookup time = 9:00 AM
Array entered:
=INDEX(C1:C3,MATCH(1,(A1:A3<=E1)*(B1:B3>=E1),0))
Returns: Class2
Each of these expressions will retrun an array of either TRUE or FALSE:
(A1:A3<=E1)
(B1:B3>=E1)
We're testing to see if the start time is less than or equal to the lookup
time *and* if the end time is greater than or equal to the lookup time. If
*both* of those conditions are TRUE it means the lookup time falls within
that time interval. With the lookup time of 9:00 AM:
(A1:A3<=E1)
................A......
1.......8:00 AM....(8:00 AM<=9:00 AM) = TRUE
2.......8:50 AM....(8:50 AM<=9:00 AM) = TRUE
3.......9:50 AM....(9:50 AM<=9:00 AM) = FALSE
(B1:B3>=E1)
................B
1.......8:30 AM....(8:30 AM>=9:00 AM) = FALSE
2.......9:20 AM....(9:20 AM>=9:00 AM) = TRUE
3.....10:45 AM....(10:45 AM>=9:00 AM) = TRUE
So we now have these 2 arrays of logical values:
TRUE...FALSE
TRUE...TRUE
FALSE...TRUE
We multiple them together to get an array of numbers. The result of this
multiplication will be either 1 or 0. TRUE * TRUE = 1. Anything else = 0:
TRUE*FALSE=0
TRUE*TRUE=1
FALSE*TRUE=0
In the MATCH function we're telling it to look for the 1:
MATCH(1,(A1:A3<=E1)*(B1:B3>=E1),0) =
MATCH(1,{0;1;0},0)
MATCH returns the relative position if the lookup_value is found. Our
lookup_value of 1 is found in the 2nd position so:
MATCH(1,{0;1;0},0) = 2
This result is then passed to the INDEX function telling it we want the
value of 2nd cell of the indexed range C1:C3:
=INDEX({"Class1";"Class2";"Class3"},2)
Class2 is the 2nd value of the indexed range So:
=INDEX(C1:C3,MATCH(1,(A1:A3<=E1)*(B1:B3>=E1),0))
Returns: Class2
Just make sure you array enter the formula!!!
exp101