Get most frequent occurring text in Excel
For example, I want to identify the most frequently occurring name in the range B2 :B21 in Screenshot below.
To do it in Excel, here is the answer:
a) Enter the formula =INDEX(B2:B21,MODE(MATCH(B2:B21,B2:B21,0))) to get the most frequently occuring text.
MATCH function matches the range against itself. Because the lookup value is an array and contains more than one value, MATCH returns an array of results.
Since MATCH always returns the first match, subsequent occurrences of the value will return the first match position (Col A in Screenshot below).
Match would return {1,2,2,1,2,3,3,4,5,3,6,6,5,6,5,3,6,5,6,4} (Col C in Screenshot below).
Mode then determines the most frequently returned position (6) (cell C22 in Screenshot below)
The value returned by Mode is then used to get the actual Text corresponding to position using the INDEX function.