Max Value Based On Custom List

[Nanogirl21]

Hi,

I am looking for a formula that will be able to populate a cell with the max value in a range based on a custom list.

Example.

CUSTOM LIST (Smallest to Largest) = ORANGE 00, APPLE 05, BANANA / HOLD, PINEAPPLE
(If I have to have the list somewhere in my workbook it will be on a sheet called Holidays in Cell E2-E15 sorted from smallest to largest. All other info is on Sheet 1)


The range is W:Z and I am looking for the max value to populate in AA

[TABLE="width: 600"]
<tbody>[TR]
[TD="align: center"]W
[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z
[/TD]
[TD="align: center"]AA
[/TD]
[/TR]
[TR]
[TD]BANANA / HOLD[/TD]
[TD]PINEAPPLE[/TD]
[TD]ORANGE 00
[/TD]
[TD]APPLE 05[/TD]
[TD]PINEAPPLE
[/TD]
[/TR]
[TR]
[TD]APPLE 05[/TD]
[TD]ORANGE 00[/TD]
[TD]BANANA / HOLD[/TD]
[TD]BANANA / HOLD[/TD]
[TD]BANANA / HOLD
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for the help.

 

[Eric W]

If your first row is 2, try:

=INDEX(W2:Z2,MATCH(MAX(MATCH(W2:Z2,Holidays!$E$2:$E$15,0)),MATCH(W2:Z2,Holidays!$E$2:$E$15,0),0))
confirmed with Control+Shift+Enter.

If you might have spaces or values that aren't in your list, then maybe:

=INDEX(W2:Z2,MATCH(AGGREGATE(14,6,MATCH(W2:Z2,Holidays!$E$2:$E$15,0),1),MATCH(W2:Z2,Holidays!$E$2:$E$15,0),0))
with CSE.

 

[Nanogirl21]

If your first row is 2, try:

=INDEX(W2:Z2,MATCH(MAX(MATCH(W2:Z2,Holidays!$E$2:$E$15,0)),MATCH(W2:Z2,Holidays!$E$2:$E$15,0),0))
confirmed with Control+Shift+Enter.

If you might have spaces or values that aren't in your list, then maybe:

=INDEX(W2:Z2,MATCH(AGGREGATE(14,6,MATCH(W2:Z2,Holidays!$E$2:$E$15,0),1),MATCH(W2:Z2,Holidays!$E$2:$E$15,0),0))
with CSE.

Thank you. I tried both and I am getting a #Value ! error.

 

[Eric W]

That would happen if you entered either formula without Control+Shift+Enter. If you have one of the formulas entered, select the cell it's in. Press F2 to edit the formula, then hold down the Control and Shift keys, then press Enter, and release all the keys.

To start from scratch, delete whatever's in AA2. Select it, paste the formula into the formula bar, and instead of just pressing Enter, hold down the Control and Shift keys, then press enter. If you did it right, Excel will put {} around the formula. You can't just put the {} yourself though, you must do the whole CSE procedure.