there are 3 teachers in a school. Ann, ben and carl. Ann teaches math and
history, ben teaches math, history and science, carl teaches math, science
and drawing. suppose cell a1 contains a drop down box containing the three
names (ann, ben and carl). if i select ann in a1, cells a3 should display
math and cell a4 should display history. alternatively, if i select ben in
a1, cell a3 should display math, cell a4 should display history and cell a5
should display science. alternatively if i select carl in a1, cell a3 should
display math, cell a4 should display science and cell a5 should display
drawing. i hope i have made my question clear. thank you in advance for the
answer. also what is the way to store the teacher names with their related
subjects

is there a specific term to describe the above concept? - therefore I can
search in google, excel help sites using that term. Also can you suggest a
better title for better results

Here's a proposal:

A1 = validation list with keys in I2:I5 (first col of vlookup table)
B1:E1 = results from vlookup
Formula in B1 copied to C1:E1:

=IF(VLOOKUP($A$1;$I$1:$M$4;J1;FALSE)="";"";VLOOKUP ($A$1;$I$1:$M$4;J1;FALSE))

Lookup table in

0 2 3 4 5
ann math history
ben math history science
carl math science drawing german
peter english drawing german

You have a response on JMT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"janice fernandes" wrote in message
...
there are 3 teachers in a school. Ann, ben and carl. Ann teaches math and
history, ben teaches math, history and science, carl teaches math, science
and drawing. suppose cell a1 contains a drop down box containing the three
names (ann, ben and carl). if i select ann in a1, cells a3 should display
math and cell a4 should display history. alternatively, if i select ben in
a1, cell a3 should display math, cell a4 should display history and cell
a5
should display science. alternatively if i select carl in a1, cell a3
should
display math, cell a4 should display science and cell a5 should display
drawing. i hope i have made my question clear. thank you in advance for
the
answer. also what is the way to store the teacher names with their related
subjects

is there a specific term to describe the above concept? - therefore I can
search in google, excel help sites using that term. Also can you suggest a
better title for better results

Sorry, hit the send button too early. :-(

Here's a proposal:

A1 = validation list with keys in I2:I5 (first col of vlookup table)
B1:E1 = results from vlookup
Formula in B1 copied to C1:E1:

=IF(VLOOKUP($A$1;$I$1:$M$4;J1;FALSE)="";"";VLOOKUP ($A$1;$I$1:$M$4;J1;FALSE)*)


You may have to replace the semicolons with commas.

Lookup table in I1:M5

0 2 3 4 5
ann math history
ben math history science
carl math science drawing german
peter english drawing german

the numbers in the first row of the lookup table allow copying the
formula without modification.

Hans

In article ,
says...
You have a response on JMT


What does that mean?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

If you are OK with Hans' answers, read no further. {grin}

Ideally, your data should be laid out in a table with 2 columns:
Teacher and Class.

Now, you can use a variety of techniques to find the relationship between
teachers and the classes they teach including filters and PivotTables.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , janicefernandes17
@yahoo.co.in says...
there are 3 teachers in a school. Ann, ben and carl. Ann teaches math and
history, ben teaches math, history and science, carl teaches math, science
and drawing. suppose cell a1 contains a drop down box containing the three
names (ann, ben and carl). if i select ann in a1, cells a3 should display
math and cell a4 should display history. alternatively, if i select ben in
a1, cell a3 should display math, cell a4 should display history and cell a5
should display science. alternatively if i select carl in a1, cell a3 should
display math, cell a4 should display science and cell a5 should display
drawing. i hope i have made my question clear. thank you in advance for the
answer. also what is the way to store the teacher names with their related
subjects

is there a specific term to describe the above concept? - therefore I can
search in google, excel help sites using that term. Also can you suggest a
better title for better results