Matching times and classes

[LS]

I have two sheets one named Daily Sch and Others. I am using Excel 2003.

Daily Sch
Others
A B A
B C
Time Class Class
Start End
7:30 (formula) Literacy
8:15 10:10
7:35 (formula) Math
11:00 11:55
7:40 (formula) Lunch
10:15 10:40
7:45 (formula) Science
7:30 8:10

Times continue on down on Daily Sch A. The formula should be placed in
Daily Sch B. Lookup the time in Others B and if it finds it should place the
name of the class in Daily Sch B. If time not found leave Daily Sch B blank.
Formula will have to look between start and end times.

If the times on Others is also a formula from another sheet will that also
make a difference? Is this impossible?

 

[T. Valko]

Try this...

Daily Sch

A2 = 7:30
A3 = 7:35
A4 = 7:40
etc
etc

Class refes to Others!A$2:A$5
Start refers to Others!B$2:B$5
End refers to Others!C$2:C$5

Enter this array formula** in Daily Sch B2:

=IF(ISNA(MATCH(1,(Start<=A2)*(End>=A2),0)),"",INDEX(Class,MATCH(1,(Start<=A2)*(End>=A2),0)))

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

Copy down as needed.

 

[LS]

=IF(ISNA(MATCH(1,(Others!W$5:W$15<=A4)*(Others!X$5:X$15>=A4),0)),"",INDEX(Others!V$5:V$15,MATCH(1,(Others!W$5:W$15<=A4)*(Others!X$5:X$15>=A4),0)))

Thanks so much I was sure this would work and I still think it will. What I
get is #VALUE. I have changed the formatting to general instead of time and
it still doesn't work. I think its the format but not sure.

What does MATCH 1 mean?

Any suggestions. Thanks

 

[T. Valko]

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.

What does MATCH 1 mean?

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

 

[LS]

You surely must be a genius. It worked. I am so thankful.

I have been working on this for weeks and learned alot just from you on this.

 

[T. Valko]

You're welcome. Thanks for the feedback!