Return Maximum Text Value

[krllb]

Dear Guru's

Good day! Need your help on how to get the cell value (Bin Coordinate) using maximum count with reference to cell Enter Plant (ex. D301). Many thanks in advance.


[TABLE="width: 377"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Enter Plant:[/TD]
[TD]D301[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BIN Coordinate[/TD]
[TD] ????????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Expected Value:[/TD]
[TD]Y03-A01-01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PLANT[/TD]
[TD]BIN Coordinate[/TD]
[TD] Count[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]100D[/TD]
[TD]B01-A01-01[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]100D[/TD]
[TD]B01-A02-01[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]100D[/TD]
[TD]B01-A03-01[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]D301[/TD]
[TD]V94-B01-01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]D301[/TD]
[TD]V94-B03-01[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D301[/TD]
[TD]V94-B04-01[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]D301[/TD]
[TD]V94-C04-01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]D301[/TD]
[TD]Y03-A01-01[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]D301[/TD]
[TD]Y04-A01-01[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try this array formula in B2
=INDEX(B6:B14,MATCH(MAX(IF(A6:A14=B1,C6:C14)),IF(A6:A14=B1,C6:C14),0))
Ctrl+Shift+Enter, not just Enter

M.

 

[jtakw]

Nvm, solution provided.

 

[krllb]

Wow! you guys are fast...will try this. Thanks!

 

[jtakw]

Double post, original post here: https://www.mrexcel.com/forum/excel...nt-text-reference-cell-value.html#post5116943

 

[Fluff]

@krllb
In future please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).