Forum Discussion
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 formu...
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