Excel 2003 - Lookup/Index/Match

[tcgoth]

I've read through the various Help articles regarding Lookup/Index/Match and can't seem to find an answer to my question.

I have the following "Master" table on which a school Conference scedule is built. "Teacher" is the students primary classroom teacher. "Translator" and "IGR" are support teachers who have to accompany the primary teacher at the parent conference.

Conference Schedule

Sally Teacher Joe Teacher
3:30 PM Student1 Student6
Translator Beth Sally
IGR Jennifer

4:00 PM Student10 Student6
Translator Cindy Beth
IGR

4:30 PM Student3 Student9
Translator Jenny
IGR Beth


I want to be able to create a schedule for each of the Support teachers that correspond to either the "Translator" or "IGR" designation - so I want it to look like this (where Beth and Cindy are Support Teachers):

Time Beth Cindy
3:30 PM
Student Student1
Teacher Sally Teacher
Translator Yes
IGR

4:00 PM
Student Student6 Student10
Teacher Joe Teacher Sally Teacher
Translator Yes Yes
IGR

4:30 PM
Student Student3
Teacher Sally Teacher
Translator
IGR Yes

The lookup for the TRanslator and IGR lines seem straightforward. How do I correctly look-up the support teacher dynamically and return the students name and primary teacher?

Thanks for any assistance you can provide. Terry

 

[SkipVought]

Hi,

Your lookup table ought to look something like this
[tt]
ConfTime Teacher Translator IGR Student
15:30 Sally Beth Jennifer Student1
16:00 Sally Cindy Student10
16:30 Sally Jenny Beth Student3
15:30 Joe Sally Student6
16:00 Joe Beth Student6
16:30 Joe Student9
[/tt]
Then you lookups will be simple.

Skip,

_[red][/red]

 

[tcgoth]

I am un fortunately a little to far down the road to switch my look-up table as teachers are actively using it to input their conferences already.

Any other ideas? I should have more specifically spelled out my problem - "Beth" for example could be either a TRanslator or IGR. I have to search two rows for the possibility of a match and then also return the associated Teacher and Student.

Thanks for any help.

Terry

 

[SkipVought]

Unfortunately, you have a bad design, which makes doing what you want just about impossible.

Your Master "table" is not a table at all. It's been designed to satisy visual interest rather than with database principles to satisfy data access.

I guess that you'll have to make do and spend ALOT of time and effort beating the data into sumbmission.

Or take the time now to redesign and reap the benefits of being able to utilize the plethora of data access, data reporting and data analysis tools in Excel that are designed for tables.

Skip,

_[red][/red]

 

[DirkStruan]

You can create the table as Skip describes relatively easily if all of the schedule entries are beneath each other:

I assume the schedules are in columns A to C and the columns F onwards are available.

In F1 put the title "Row", and then the table headings as Skip described.

Enter the following functions in the given cells:
F2=MATCH("Translator",A:A,0)
F3=MATCH("Translator",INDIRECT("A"&F2+1&":A65536"),0)+F2
Drag F3 down for until you return an error message.

You now have a list of the rows where "Translator" appears in the A column. Filling out the table (looking first at column B) is now a matter of returning the cells relative from the cell containing "Translator", i.e.

G2=OFFSET(INDIRECT("A"&$F2),-1,0)
H2=OFFSET(INDIRECT("A"&$F2),-2,1)
I2=OFFSET(INDIRECT("A"&$F2),0,1)
J2=OFFSET(INDIRECT("A"&$F2),1,1)
K2=OFFSET(INDIRECT("A"&$F2),-1,1)

These can then be dragged down to populate the table for the entries in column B.

To return the values in the C column do exactly the same thing but change the last parameter of the functions in H to K to a 2.




Dirk

Everything useful perishes through use....Should I not rejoice at being useless?