MAXIF and return another column

[redzhell]

Hi,

Please can somebody help?​

The purpose is to get the Max of column C for each AssetID(column B) and then return whether it’s a Cross Arm or Pole but I’d need to returnall values for Poles and Cross Arm if the highest occurs more than once per AssetID.

So just for the below example, the Max is

4.20825744623599 which is true for both the L Pole and R Pole. How would I write the formula around my MAXIF so that it returns 'L Pole, R Pole'?​

_{^{<strike>
</strike>}}
I do have a spreadsheet I can attach but can't see a way of attaching it.





[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10029396C<strike></strike>
[/TD]
[TD]<strike></strike>KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<strike></strike>2.43650068130681<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CROSS ARM[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>[TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10029396L[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<strike></strike>4.20825744623599<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]L POLE[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>[TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10029396R[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<strike></strike>

4.20825744623599​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]R POLE[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

<strike></strike>

[TABLE="width: 519"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 

[DanteAmor]

I've saved that too as never know when I'd need it. Thank you for the code

Youre welcome and thanks for the feedback.

 

[Aryatect]

Hi all,

Thanks very much for all the input.

I'm not allowed to use Macros in this instance so I have used Aryatect's formula which works a treat!

Glad was able to help