Match Formula Problem

[jvanworthkhsd]

I have course list from Col B to Col E for Students (col A):
A B C D E F
Name Per 1 Per 2 Per 3 Per 4 Math
John Algebra English 9 PE Art Algerba
Pollete English 10 Con Math Biology PE Con Math
Chris Art PE Algerba 2 English 9 Algerbra2

in column F I want to list any math course that matches up to a list of math courses...ie:
Algebra
Gen Math
Con Math
Geomerty
Algebra 2

I cannot come up with a match formula that will work. Any ideas

 

[jvanworthkhsd]

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[gardnertoo]

Try this array formula, confirmed by hitting CONTROL+SHIFT+ENTER, in cell F1:

=INDEX($A2:$E2,,MAX(COUNTIF($F$6:$F$10,$A2:$E2)*COLUMN($A2:$E2)))

 

[Domenic]

Try...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX(B2:E2,MATCH(TRUE,ISNUMBER(MATCH(B2:E2,$F$6:$F$10,0)),0))

Note that if there's more than one math course that matches, the formula will return the first occurrence.

Hope this helps!

 

[jvanworthkhsd]

For gardnertoo responce I get "#VALUE!"

and for Domenic's I get "#NA#"

I believe I pasted correctly

 

[Domenic]

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[jvanworthkhsd]

got it............It worked for both

I do not understand the CONTROL+SHIFT+ENTER though

If you have time can you explain?


Thanks

 

[gardnertoo]

By entering with CONTROL+SHIFT+ENTER, you tell Excel that the formula is an array formula. The difference is that excel will evaluate the formula multiple times as an array, in the case of my formula it evaluates this portion:

COUNTIF($F$6:$F$10,$A2:$E2)*COLUMN($A2:$E2)

five times, once each for the following virtual formulas:

COUNTIF($F$6:$F$10,$A2)*COLUMN($A2)
COUNTIF($F$6:$F$10,$B2)*COLUMN($B2)
COUNTIF($F$6:$F$10,$C2)*COLUMN($C2)
COUNTIF($F$6:$F$10,$D2)*COLUMN($D2)
COUNTIF($F$6:$F$10,$E2)*COLUMN($E2)

This creates a virtual array, containing the five results. In this specific case, the five results are {2,0,0,0,0}. The rest of the formula resolves to:

=INDEX($A2:$E2,,MAX({2,0,0,0,0}))

 

[jvanworthkhsd]

6-inch ribbon curls, impossible - Awesome.....Thanks

 

[jvanworthkhsd]

If I wanted to list the second Math class in column G that is not listed in column F how do I proceed. I want to list every math class that a student is taking. ie Pollete, Con Math & Geometry

Name</SPAN>	Per 1</SPAN>	Per 2</SPAN>	Per 3</SPAN>	Per 4</SPAN>	Math</SPAN>	Math 2</SPAN>
John</SPAN>	Algebra</SPAN>	English 9</SPAN>	PE</SPAN>	Art</SPAN>	Algebra</SPAN>
Pollete</SPAN>	English 10</SPAN>	Con Math</SPAN>	Biology</SPAN>	Geometry</SPAN>	Con Math</SPAN>	Geometry</SPAN>
Chris</SPAN>	Art</SPAN>	ISP</SPAN>	Algebra 2</SPAN>	US</SPAN>	Algebra 2</SPAN>
					Algebra</SPAN>
					Gen Math</SPAN>
					Con Math</SPAN>
					Geometry</SPAN>
					Algebra 2</SPAN>

<TBODY>
</TBODY>