Excel 2007 : Look at row, find a value, report column header

[jdottdot]

I want to look up a specific value in a row and find the value and report the column header. I am wondering if there is a relativly simple way to do this. I have a schedule listed with the times on the left, the students across the top, and the teachers listed inside the table. I would like to have it find the teacher and tell me which student they will have during that hour:

Mike Steve Sam
1 Mr Still Mrs Tam Ms Stweart
2 Mr Fong Mr Dave Mrs Tam
3 Mrs Tam Ms Stweart Mr Fong

So, at 1:00, I want it to look up Mrs Tam and tell me that she will teach Steve at that time. At 3, I want it to report that Mr Fong will teach Sam. If you need more details, let me know.

[mewingkitty]

It's certainly doable, there are many options for lookup functions, I'd just need to know what you mean by IT. You want IT to return values.

Explain what IT is, and we'll get started.

IE.
List, chart, drop down box with names and times, user input in cells... mew mew mew.

mew!

[JBeaucaire]

You example is pretty specific, perhaps too specific, but this is one way to do it...

[jdottdot]

My ultimate purpose is to exchange where the teacher names are and the student names are. I would like to have the Teacher Names as the Column Header and the times as the row header and have the resulting table tell me which student the teacher has at the specified time. I'm trying to set up a way of creating a student schedule table and from that create the teacher schedule table. Otherwise I'm doing it manually.

[JBeaucaire]

Ah, much simpler then...

=IF(ISNUMBER(MATCH(G$1, $B2:$D2, 0)), INDEX($B$1:$D$1, MATCH(G$1, $B2:$D2, 0)), "-")

[jdottdot]

Thank you SO much!!! That did exactly what I needed it to do. I ended up making a drop down menu to pick the teacher and then used the formula to have it create the teacher schedule. It works wonderfully! Here's a minor question that may be a little ignorant on my part. There are times that even when I input the correct information, the cells don't auto-populate. If I copy and paste the formula in the cell to the same cell, it will work. Do you know if there is a way to avoid this or a way to refresh the page so it will show the name?

[JBeaucaire]

Check your sheet options (I don't use 2007, so can't tell you where it is) and make sure Calculation is automatic. There's nothing weird about these sheet functions, so it must be something environmental.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)