Index/Match/Max Formula

[AlexaS]

Ok, so I'm good on how Index/Match works. I've used it successfully in the past, understand the examples that I can find online, I'm good. Where I'm having a problem is trying to incorporate a Max function. Here's my simplified data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cust[/TD]
[TD]Class[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Low[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Middle[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Low[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]High[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]None[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]


Basically I need to search column A to match a customer number, and then search for the maximum sales volume and then return the appropriate class. So in the example I would search for Cust# 2, have it find that the max value is 200 and return the corresponding class which is Middle. I need some help because I can't get the Max portion to work.

This is the closest I've gotten

=INDEX(B21:F1959,MATCH(J22,B21:B1959,0),3)

and this just returns a #REF!

=INDEX(B20:F1958,MATCH(J21,B20:B1958,0),MATCH(MAX(F20:F1958),F20:F1958,0))

Any help is appreciated since I'm currently doing this manually and I'm getting sick of it.

 

[Caribeiro77]

Try:

Book1
	A	B	C
1	Cust	Class	Sales
2	1	Low	100
3	2	Middle	200
4	2	Low	75
5	1	High	50
6	4	None	125
7
8
9	Cust
10	1
11
12	Class
13	Low
Sheet5

Cell Formulas
Range	Formula
A13	{=INDEX(B2:B6,MATCH(MAX(IF(A2:A6=A10,C2:C6)),C2:C6,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AhoyNC]

Caribeiro77,
You need to make a small change to your formula in case there are duplicate vales in the Sales column for different Cust. numbers.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

=INDEX($B$2:$B$6,MATCH(MAX(IF($A$2:$A$6=$A$10,$C$2:$C$6)),[COLOR=#ff0000]IF($A$2:$A$6=$A$10[/COLOR],$C$2:$C$6),0))

 

[Caribeiro77]

Ahoy you are right...Didn't even thought on dups...

 

[AlexaS]

These worked perfectly thank you! I do have a question though (as I tend to avoid array formulas), can I put an if statement in front of it and still work or do I need to handle that in a separate cell?

Never mind! I played with it a little bit and got it to work with the IF statement and a concatenate. This rocks! Thank you so much!

 

[XOR LX]

Hi.

If you want a non-array (and shorter!) solution, I always prefer:

=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A6=A10)*C2:C6)),B2:B6)

for this type of question.

Explanation here if you're interested:

Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

Regards

 

[HankPym]

When your just looking up a single value in a col of values (col A), and wanting the maximum value for that value from another col in your table (col D).

=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$1:$A$10=A15)*$D$1:$D$10)),$D$1:$D$10)

Thanks XOR LX!

 

[XOR LX]

Sure! You're welcome!

 

[Phyo89]

Thank you very much AhoyNC
This one is very useful for my work