Returning Adjacent Cell with Max(IF

[pmolan]

Office 365
I am working with a large spreadsheet that changes monthly. I prefer my code to be dynamic because the quantity of data changes each month. I have a summary page that displays the MAX of each ITEM.
I am currently using MAX(IF to find these values. I would like to also display the adjacent cells (to the left) of the found MAX value.

The data is structured something like this:

ITEM	Column 1	Column 2	Column 3
Apple	2	57	9.2
Apple	3	63	9.3
Apple	4	69	15
Apple	5	81	9.2
Apple	6	87	8.8
Plum	2	57	15
Plum	3	63	8.5
Plum	4	69	7.9
Plum	5	81	8.8
Plum	6	87	8.7

<colgroup><col width="62" style="width: 47pt;"> <col width="62" style="width: 47pt;" span="2"> <col width="62" style="width: 47pt;"> <tbody> </tbody>

On the summary page, B35 = "Plum" in the following code
The formula that I am using is:
=MAX(IF('Combined Data'!$A:$A=B35,'Combined Data'!$D:$D)) (CTRL + SHIFT + ENTER)
this returns a value of 15 which is correct

I would like to display columns B and C in my report as well
Example:

B35
Plum 2 57 15

I have tried code like
=INDEX('Combined Data'!$B:$B,MATCH(MAX(IF('Combined Data'!$A:$A=B35,'Combined Data'!$D:$D)),'Combined Data'!$D:$D,0))
This results in erroneous data such as

B35
Plum 4 69 15

because it is returning the first value that it finds in column B that has 15 in column D.

I know there is a simpler way with OFFSET etc.. but I just cant seem to get it to work with MAX(IF.

I hope that this is clear.

Thanks.

 

[Aladin Akyurek]

You can't just assume that the plum and 15 combination occurs just once. So, a single output record like Plum 2 57 15 won't work. Just replace 8.8 in column D of Combined Data with 15 to see what I mean.

 

[pmolan]

Thank you for your reply. I agree there may be multiple 15’s. I am only interested in the first occurrence at the moment.

 

[Aladin Akyurek]

Thank you for your reply. I agree there may be multiple 15’s. I am only interested in the first occurrence at the moment.

Do you have MAXIFS on your Excel version?