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:

[TABLE="width: 188"]
<colgroup><col width="62" style="width: 47pt;"> <col width="62" style="width: 47pt;" span="2"> <col width="62" style="width: 47pt;"> <tbody>[TR]
[TD="class: xl80, width: 62, bgcolor: transparent"]ITEM[/TD]
[TD="class: xl82, width: 62, bgcolor: #FFFFCC"]Column 1[/TD]
[TD="class: xl82, width: 62, bgcolor: #FFFFCC"]Column 2[/TD]
[TD="class: xl83, width: 62, bgcolor: #FFFFCC"]Column 3[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Apple[/TD]
[TD="class: xl84, bgcolor: transparent"]2[/TD]
[TD="class: xl84, bgcolor: transparent"]57[/TD]
[TD="class: xl84, bgcolor: transparent"]9.2[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Apple[/TD]
[TD="class: xl84, bgcolor: transparent"]3[/TD]
[TD="class: xl84, bgcolor: transparent"]63[/TD]
[TD="class: xl84, bgcolor: transparent"]9.3[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Apple[/TD]
[TD="class: xl85, bgcolor: yellow"]4[/TD]
[TD="class: xl85, bgcolor: yellow"]69[/TD]
[TD="class: xl85, bgcolor: yellow"]15[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Apple[/TD]
[TD="class: xl84, bgcolor: transparent"]5[/TD]
[TD="class: xl84, bgcolor: transparent"]81[/TD]
[TD="class: xl84, bgcolor: transparent"]9.2[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Apple[/TD]
[TD="class: xl84, bgcolor: transparent"]6[/TD]
[TD="class: xl84, bgcolor: transparent"]87[/TD]
[TD="class: xl84, bgcolor: transparent"]8.8[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Plum[/TD]
[TD="class: xl86, bgcolor: #92D050"]2[/TD]
[TD="class: xl86, bgcolor: #92D050"]57[/TD]
[TD="class: xl86, bgcolor: #92D050"]15[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Plum[/TD]
[TD="class: xl84, bgcolor: transparent"]3[/TD]
[TD="class: xl84, bgcolor: transparent"]63[/TD]
[TD="class: xl84, bgcolor: transparent"]8.5[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Plum[/TD]
[TD="class: xl84, bgcolor: transparent"]4[/TD]
[TD="class: xl84, bgcolor: transparent"]69[/TD]
[TD="class: xl84, bgcolor: transparent"]7.9[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Plum[/TD]
[TD="class: xl84, bgcolor: transparent"]5[/TD]
[TD="class: xl84, bgcolor: transparent"]81[/TD]
[TD="class: xl84, bgcolor: transparent"]8.8[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]Plum[/TD]
[TD="class: xl84, bgcolor: transparent"]6[/TD]
[TD="class: xl84, bgcolor: transparent"]87[/TD]
[TD="class: xl84, bgcolor: transparent"]8.7[/TD]
[/TR]
</tbody>[/TABLE]


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?