Help! Max and Index with multiple criteria?? :c

[kltinaheart]

Hey all!

so ive been at this for 4 hours -.-
If I have lets say a
Column A for item type - Gloves, masks, shoes
Column B for Size - Sma, Med, Lar
Column C for prices - 5.00, 10.00, 15.00

I want a formula to look at the variable in Column A (this I can manually type in the formula, lets say gloves)
So IF Gloves, and IF Column B says "Med", then give me the Highest price for all Medium gloves.

So far I tried (=MAX(IF(D2:D17="Gloves",O2:O18))) which is supposed to work fine, but doesn't on my excel
(=SUMPRODUCT(MAX((D2:D17="Gloves")*O2:O18))) Which is supposed to work fine but doesn't.
Then finally...
=MAX(INDEX((Table1[Item Type]="Gloves")*Table1[Price per Unit],0)) Which works Great, but I only have 1 criteria (whether or not it says Gloves)
Can I add some kind of Gloves AND Med, give me max price?

thank you!!
tina

 

[mrshl9898]

Can you show a screenshot? Need to know what's in D and O it seems.

If we're just talking A,B and C then this should work.

=MAXIFS(C:C,A:A,"Gloves",B:B,"Med")

 

[kltinaheart]

Can you show a screenshot? Need to know what's in D and O it seems.

If we're just talking A,B and C then this should work.

=MAXIFS(C:C,A:A,"Gloves",B:B,"Med")

Hmmm it seems as if MAXIFS isn't even an option on my version...
looking now its version 2003.

So pic attached, once again I want.. Look at all gloves, and all Size medium, return the Highest price you see

 

[mrshl9898]

Bugger...

Can you sort the data? If you can sort high to low on price, then join D and F =D2&F2. Then do a VLOOKUP on the new value to get the first (so highest) result

 

[mrshl9898]

Or try to join them as above, and use =MAX(IF(A:A="ClovesM",O:O)) where column A is the joined text

 

[kltinaheart]

Bugger...

Can you sort the data? If you can sort high to low on price, then join D and F =D2&F2. Then do a VLOOKUP on the new value to get the first (so highest) result

Hmm but then it wont work for all my other item types, when I expand form just gloves, right?
So I cant take this
=MAX(INDEX((Table1[Item Type]="Gloves")*Table1[Price per Unit],0))
And add an additional criteria, next to gloves?

 

[kltinaheart]

Or try to join them as above, and use =MAX(IF(A:A="ClovesM",O:O)) where column A is the joined text

Interesting... Like Concatenate them and just ide the column...? that can work yes..

 

[Eric W]

Hmm but then it wont work for all my other item types, when I expand form just gloves, right?
So I cant take this
=MAX(INDEX((Table1[Item Type]="Gloves")*Table1[Price per Unit],0))
And add an additional criteria, next to gloves?

Try:

=MAX(INDEX((Table1[Item Type]="Gloves")*(Table1[Size]="Med")*Table1[Price per Unit],0))

You can remove the INDEX if you enter the formula using Control+Shift+Enter:

=MAX((Table1[Item Type]="Gloves")*(Table1[Size]="Med")*Table1[Price per Unit])