Array Formula for Returning Text, which is appearing maximum time in Range.

[AjayVyas]

Hi, Anupam


Please check this Formula for particular Text which comes maximum time in particular range.


Cell A1:A8 = {"Raj";"Ajay";"Vijay";"Sanjay";"Raj";"Ajay";"Amit";"Ajay"}


Formula in B2 = =INDEX(A1:A8,MATCH(LARGE(COUNTIF(A1:A8,A1:A8),1),COUNTIF(A1:A8,A1:A8),0))


Best Regards

Ajay Vyas

 

[Faseeh]

Hi AjayVyas,


The formula works correctly and it will give you the most repeated text in a range. but it will be erroneous in case where you have a tie eg. if Raj and Ajay both appears twice in that range, it will give you the one that comes first in the INDEX Lookup range.


Regards,

 

[AjayVyas]

Hi Faseeh,


Your Analysis is Very Correct.


Thanks & Regards

 

[Faseeh]

You are welcome!


Faseeh