Best formula for multiple values in a matrix?

[mzem]

I'm having trouble with a formula. Column A is a list of names. Row 1 is a list classes. What I need to be able to do is select a name from column a in drop-down list in B11 and have it return the list of classes in list starting at B14 based of the 'X'. In this example for Tom it should return PE and ART. The actual file has 50+ names and 50+ classes. Can this be done without a maco? If a macro is required where do I begin. Any help is appreciated.

Thanks,
mzem

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD style="TEXT-ALIGN: center">Math</TD><TD style="TEXT-ALIGN: center">Science</TD><TD style="TEXT-ALIGN: center">History</TD><TD style="TEXT-ALIGN: center">Lagguage</TD><TD style="TEXT-ALIGN: center">PE</TD><TD style="TEXT-ALIGN: center">ART</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Lisa</TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Tom</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Joe</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Sam</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Laura</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD>Name</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD></TD><TD>Tom</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD></TD><TD>Course Required</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD>PE</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD>ART</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[p45cal]

Unfortunately, this is probably not the best formula, but it's a formula. ARRAY-ENTER (THIS IS VITAL), with Ctrl+Shift+Enter the formula into cell B14, then copy by dragging down to cell B19.

Excel Workbook
	A	B	C	D	E	F	G
1		Math	Science	History	Lagguage	PE	ART
2	Lisa	X		X			X
3	Tom					X	X
4	Joe	X	X		X
5	Sam			X		X	X
6	Laura		X
7
8
9
10		Name
11		Joe
12
13		Course Required
14		Math
15		Science
16		Lagguage
17		-
18		-
19		-
Sheet

 

[mzem]

Thanks very much. Your formula does in fact work. My attempts didn't get me remotely close to that. A working formula is good enough at this point.
thanks again,
mzem