# Match Formula Problem

#### jvanworthkhsd

##### Board Regular
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

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### gardnertoo

##### Well-known Member
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

##### MrExcel MVP
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

##### Board Regular
For gardnertoo responce I get "#VALUE!"

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

I believe I pasted correctly

#### Domenic

##### MrExcel MVP
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

##### Board Regular
got it............It worked for both

I do not understand the CONTROL+SHIFT+ENTER though

If you have time can you explain?

Thanks

#### gardnertoo

##### Well-known Member
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

##### Board Regular
6-inch ribbon curls, impossible - Awesome.....Thanks

#### jvanworthkhsd

##### Board Regular
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 Per 1 Per 2 Per 3 Per 4 Math Math 2 John Algebra English 9 PE Art Algebra Pollete English 10 Con Math Biology Geometry Con Math Geometry Chris Art ISP Algebra 2 US Algebra 2 Algebra Gen Math Con Math Geometry Algebra 2

<TBODY>
</TBODY>

Replies
4
Views
212
Replies
2
Views
329
Replies
5
Views
201
Replies
0
Views
138
Replies
5
Views
264

1,195,748
Messages
6,011,421
Members
441,614
Latest member
TiaGtz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back