# Using a VLOOKUP & MAX/MIN formula

#### t_w_haines

##### New Member
Hi,

I am trying to create some grading charts for my teaching, the difficulty that I am having is that the marks are letters rather than numbers and therefore do not respond to standard formula.

"NYA" (not yet achieved)
"P" (pass)
"M" (merit)
"D" (distinction)

I would like to be able to type in the grades (as above) into a series/range of cells, then to set up a formula that finds the highest grade from that series/range.

e.g.

Typed data:

P
M
P
D

Formula to automate that the best grade here is D.

Any help would be most gratefully received,

Thanks

TH

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi,

I am trying to create some grading charts for my teaching, the difficulty that I am having is that the marks are letters rather than numbers and therefore do not respond to standard formula.

"NYA" (not yet achieved)
"P" (pass)
"M" (merit)
"D" (distinction)

I would like to be able to type in the grades (as above) into a series/range of cells, then to set up a formula that finds the highest grade from that series/range.

e.g.

Typed data:

P
M
P
D

Formula to automate that the best grade here is D.

Any help would be most gratefully received,

Thanks

TH
Data in the range A2:A5.

Try this array formula**...

=INDEX({"NYA","P","M","D"},MAX(MATCH(A2:A5,{"NYA","P","M","D"},0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks for this,

I couldn't get this to work though (probably my inexperience with Excel!)

Here is a table to try and explain it a bit further

In the columns C3:C11 I have entered grades (NYA, P, M, D or blank)

In cell C12 I need the formula that takes the highest of those grades (where NYA is lowest, then P, then M, then D is highest, and blank is ignored)

This is then required for columns D, E, F & G as well.
 A1 B C D E F G 2 Assignments GC1 GC2 GC3 GC4 GC5 3 Assig 1 P NYA NYA M 4 Assig 2 M P 5 Assig 3 NYA D D 6 Assig 4 D M 7 Assig 5 M P M 8 Assig 6 P D 9 Assig 7 NYA M M 10 Assig 8 D P 11 Assig 9 M P NYA 12 OVERALL

<TBODY>
</TBODY>

Any further help would be wonderful!!

Thanks

Thanks for this,

I couldn't get this to work though (probably my inexperience with Excel!)

Here is a table to try and explain it a bit further

In the columns C3:C11 I have entered grades (NYA, P, M, D or blank)

In cell C12 I need the formula that takes the highest of those grades (where NYA is lowest, then P, then M, then D is highest, and blank is ignored)

This is then required for columns D, E, F & G as well.
 A1 B C D E F G 2 Assignments GC1 GC2 GC3 GC4 GC5 3 Assig 1 P NYA NYA M 4 Assig 2 M P 5 Assig 3 NYA D D 6 Assig 4 D M 7 Assig 5 M P M 8 Assig 6 P D 9 Assig 7 NYA M M 10 Assig 8 D P 11 Assig 9 M P NYA 12 OVERALL

<TBODY>
</TBODY>

Any further help would be wonderful!!

Thanks
Try this...

List the grades in a range of cells and list them in this specific order:

NYA
P
M
D

Let's assume they're in the range J2:J5.

Then, enter this array formula** in C12 and copy across as needed:

=INDEX(\$J2:\$J5,MAX(IF(ISNUMBER(MATCH(C2:C11,\$J2:\$J5,0)),MATCH(C2:C11,\$J2:\$J5,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Replies
9
Views
173
Replies
4
Views
202
Replies
7
Views
2K
Replies
10
Views
311
Replies
8
Views
337

1,203,506
Messages
6,055,806
Members
444,825
Latest member
aggerdanny

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