Lookups?

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hey all. Thanks in advance for any help. I am setting up a workbook to help simplify scheduling
classes for a school. I would like to look through a
column of classes, find a teacher's name, then copy the
information from that period to another sheet. I'm not
sure if this makes any sense, but basically, I'm trying to
create a system in which a principal can input a master
schedule, and the information automatically links to other
sheets to create teacher schedules.

here is part of a master schedule:(600, 601, 602 are classes and 1, 2, etc. are periods of a day)


1 2 3 4
600 Science Spanish SS Math
Morris Bradley Dunn Finn
Rm.215 Rm.209 Rm.211 Rm.213
601 Science SS English Art
Humphreys oakes Bradley Roines
Rm.229 Rm.# Rm.211 Rm.207
602 Spanish Science SS Math
Frazier Humphreys Allen Alladice
Rm.221 Rm.229 Rm.208 Rm.214


Here is part of a teacher master schedule:
1 2 3 4
Humphreys

Bradley

Morris



--
 
Hi Aaron

Here's one way.
Assuming your first list is on sheet1 in B7:F56 and your
second list is on sheet2 in K9:O12

In L10 on sheet2 enter this array formula on one line:

=INDEX(Sheet1!$B$7:$B$56,MAX(IF(ISNUMBER(SEARCH($K10,Sheet1!C$7:C$56)),
ROW(Sheet1!$B$7:$B$56)-MIN(ROW(Sheet1!$B$7))+1)))

Please notice the use of mixed absolute($$) and relative(no cash)
references.

The formula must be entered with <Shift><Ctrl><Enter> also if
edited later. If done correctly, Excel will display the formula in the
formula bar enclosed in curly brackets { } Don't enter these brackets
yourself.

Copy L10 to O10 with the fill handle (the little square in the lower
right corner of the cell.)
While L10:O10 is selected drag the selection down with the fill handle.


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.


"Aaron" <[email protected]> skrev i en meddelelse Hey all. Thanks in advance for any help. I am setting up a workbook to help simplify scheduling
classes for a school. I would like to look through a
column of classes, find a teacher's name, then copy the
information from that period to another sheet. I'm not
sure if this makes any sense, but basically, I'm trying to
create a system in which a principal can input a master
schedule, and the information automatically links to other
sheets to create teacher schedules.

here is part of a master schedule:(600, 601, 602 are classes and 1, 2, etc. are periods of a day)


1 2 3 4
600 Science Spanish SS Math
Morris Bradley Dunn Finn
Rm.215 Rm.209 Rm.211 Rm.213
601 Science SS English Art
Humphreys oakes Bradley Roines
Rm.229 Rm.# Rm.211 Rm.207
602 Spanish Science SS Math
Frazier Humphreys Allen Alladice
Rm.221 Rm.229 Rm.208 Rm.214


Here is part of a teacher master schedule:
1 2 3 4
Humphreys

Bradley

Morris



--
 
Back
Top