Look up to find who has highest quantity

[KIM Chanthan]

Hi,

I have one question to ask you about how to look up to find who has highest quantity. So please find attached file fore more detail.

Thanks for help,
Chanthan

 

[r1c1]

@KIM Chanthan Interesting question. You can use MODE formula to find the most frequent item in a list, but it works only for numbers. So we use a pair of INDEX & MATCH to get what we want. For your data, this formula works:

=INDEX($A$2:$A$13,MODE.SNGL(MATCH($A$2:$A$13,$A$2:$A$13,0)))

You may use MODE.MULT if you want to get more than one manager's name in case of ties. Replace with MODE if you are using this on a older version of Excel.

 

[KIM Chanthan]

@KIM Chanthan Interesting question. You can use MODE formula to find the most frequent item in a list, but it works only for numbers. So we use a pair of INDEX & MATCH to get what we want. For your data, this formula works:

=INDEX($A$2:$A$13,MODE.SNGL(MATCH($A$2:$A$13,$A$2:$A$13,0)))

You may use MODE.MULT if you want to get more than one manager's name in case of ties. Replace with MODE if you are using this on a older version of Excel.

Dear sir,

Yeah it works. Thanks for help.

 

[KIM Chanthan]

@KIM Chanthan Interesting question. You can use MODE formula to find the most frequent item in a list, but it works only for numbers. So we use a pair of INDEX & MATCH to get what we want. For your data, this formula works:

=INDEX($A$2:$A$13,MODE.SNGL(MATCH($A$2:$A$13,$A$2:$A$13,0)))

You may use MODE.MULT if you want to get more than one manager's name in case of ties. Replace with MODE if you are using this on a older version of Excel.

Hi Sir,

What if I want to put another criteria by branch? I mean I want to find highest quantity area manager in each branch. Please find attached file for more detail.

Thank in advance for your perfect job.
Chanthan

 

[r1c1]

@KIM Chanthan Thank you. You can use similar logic to find most frequent item in the list for a given dept. Below array formula works fine, assuming E1 contains the branch code.

=INDEX($B$2:$B$13, MATCH(MAX(COUNTIFS($B$2:$B$13,$B$2:$B$13,$A$2:$A$13,$E1)), COUNTIFS($B$2:$B$13,$B$2:$B$13,$A$2:$A$13,$E1),0))

Press CTRL+Shift+Enter to get the result.

I think we can use MODE as well for this scenario, but I can't figure it out. Anyone?!?