Problem: incorrect value returned using INDEX, MATCH, MIN

[99tollap]

I would be very grateful if someone could help me with what is probably a simple problem. I have attached a spreadsheet that hopefully clearly illustrates the problem. In this spreadsheet, the first column lists various categories, each of which has many entries (in this example the two columns with values are labelled "High" and "Low"). I am then trying to find the maximum value for one of these categories (in the example, "Low") in column "Pigs", which I do successfully using this formula:

{=MAX(IF(A2:A11=A13,B2:B11))} Here the formula only looks for maximum values when the word "Low" is found in the first column

Next, I am trying to read off the value for this row in the "Cows" COLUMN. I attempt this using the following formula:

{=INDEX(C2:C11,MATCH(MAX(IF(A2:A11=A13,B2:B11)),B2:B11,0))}

The problem is, that this formula establishes the value to search for (20) but then searches through all values in column "Cows", not just the values associated with "Low". Because one of the "High" category also has a value of 20, the formula returns the values of "Cows" for this, rather than the one I want. How can I get the correct value here? I need a way of telling excel to only extract the value in the "Cows" column at the exact row given by the first formula.

Hopefully this is sufficiently clear. Many thanks for the help.

[Phuocam]

Change to:

{=INDEX(C2:C11,MATCH(MAX(IF(A2:A11=A13,B2:B11)),IF(A2:A11=A13,B2:B11),0))}

[Pete_UK]

You can use this array* formula in B16:

=INDEX(C2:C11,MATCH(1,(MAX(IF(A2:A11=A13,B2:B11))=B2:B11)*(A2:A11=A13),0))

*An array formula needs to be confirmed using the key strokes Ctrl-Shift-Enter (CSE) instead of the usual Enter, unless you are using Excel 365.

Hope this helps.

Pete

[99tollap]

Thank you both very much for this. It's working now and I can enjoy Christmas. Really appreciate the rapid help.

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Merry Christmas.

Pete