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.

excel get most frequent occurring text

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.

excel get most frequent occurring text


You can find similar Excel Questions and Answer hereunder

1) Converting numbers stored as text to numbers via macro in Excel

2) Remove the apostrophe cell text values in Excel

3) Is there a way to get a log value of a number using Excel?

4) I conducted a test and the test scores are available - how can I get Percentile rank of students?

5) How can I get row count of filtered data?

6) How can I get the information about my current operating environment (OS version, Excel version, Current Directory)

7) Vba delete entire row if contains certain text in Excel

8) How can I loop through all WorkSheets and get their names using VBA?

9) How do I change the rating numbers to rating labels?

10) Here an explanation about text box and how to control text boxes in Excel VBA


Here the previous and next chapter