Forum Discussion

KuroShiiro21's avatar
KuroShiiro21
Copper Contributor
Jun 27, 2020

How to Look for the Most Occurring Word in a 5x10 Table

I am trying to find the most occurring word in Excel in a 5x10 table. After looking through Youtube and other guides available, I have tried to use MATCH,INDEX,MODE. However, after entering the formula, the output is always #NA. Is it because the data from the table I used is from a Drop-down List?

 

 

excel
Formulas and Functions

7 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    Jun 28, 2020

    KuroShiiro21 

     

    Hi,

     

    Office 365 or Excel 2019:

     

    =INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",0,K3:M16)&"</b></a>","//b"),MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>")))))

     

    Excel 2007 or later:

     

    =INDEX(K3:M16,1+INT((MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>"))))-1)/COLUMNS(K3:M16)),1+MOD(MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>"))))-1,COLUMNS(K3:M16)))

     

    Regards

    • KuroShiiro21's avatar
      KuroShiiro21
      Copper Contributor
      Jun 27, 2020

      DevendraJain I have attached the file. If you look at the file on the Data page, look for the Genres columns, I would like to have a single cell formula to find the most occurring word in that section of the data. 

      • DevendraJain's avatar
        DevendraJain
        Iron Contributor
        Jun 27, 2020

        KuroShiiro21 You can find the most common word in each column using this formula

         

        =+INDEX(K3:K16,MODE.SNGL(MATCH(K3:K16,K3:K16,0)))

         

        Try this and let me know is it is working for Genres 1st column

         

         

Resources