Index/Match and Max Value

[djmarsh51]

Hello,

I am currently trying set up a spreadsheet which returns a number based on mulitple criteria using an index and match function (I believe this is the most efficent way). Each sample represents it own unique values (eg, 1 is independent of 2, independent of 3...). Within each set of data or sample, I would like the spreadsheet to select the pet with the highest weight only for "Good" Samples (Bad will be ignored) and return the number related to that pet. My data as approx 20000 rows of data so I am also looking for the most efficent way to perform the calculation. I believe that the index/match and max should work, but I am getting lost how to set up the formula. I am open to any other suggestions which may work.

Please see attachment for example.

Thanks for the Help!

[NBVC]

I am not sure about most efficient, as you need an array formula to get max with conditions, but you can test:

in G5:

Please Login or Register  to view this content.

adjust ranges, and then confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

in F5:

=IF(G5="","",D5)

copied down

[djmarsh51]

Hi NBVC,

Formula looks to be working with one issue. All the inputs are constantly changing in my spreadsheet (not many constants) and when testing the formula I noticed when the sample numbers (column B) are not used in the formula, the formula for the Countifs statement seem to not work if a combination is already used above. For example if a sample one has "Good" and "Dog", sample 2 assumes this combination has already been used (result of the of the countif is 2>1) and therefore the result is a blank.

Is there a way to adjust this so that even if the the result is used for a previous sample, the formula returns the correct value looking at the specific sample (eg. 1 or 2 or 3 or ...... or 15,000)

Please see example attached.

Thanks!

[NBVC]

Do you mean:

Please Login or Register  to view this content.

CSE confirmed and copied down?

[djmarsh51]

NBVC,

Almost there, I am just finding that the formula is showing all results for those labeled as good, whereas I am trying to get only those which have the highest weight. I added another table with comments to show desired output and thought process.

Confirmed CSE and copied down - Please see updated attachement.

Thanks again for all your help with this!

[NBVC]

The last 2 explanations don't correspond to your expected results... which is correct, should we be returning 10 for Zebra (since it is highest weight in Sample 3 category)?

[djmarsh51]

Hi NBVC,

Sorry about that. You are correct.

It should read Good Zebra 10 as it is a 30 weight - ignoring the Good Fish 2

10 3 Good Zebra 30 Zebra 10

My mistake.

[NBVC]

Then perhaps:

=IF(C5="Bad","",IF(E5=MAX(IF($B$5:$B$12=B5,IF($C$5:$C$12="Good",$E$5:$E$12))),A5,""))

CSE confirmed and copied down?

[djmarsh51]

That seems to work, thanks so much for the help!