Formula to report most commonly entered location query

%3CLINGO-SUB%20id%3D%22lingo-sub-1174188%22%20slang%3D%22en-US%22%3EFormula%20to%20report%20most%20commonly%20entered%20location%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174188%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20relatively%20new%20to%20larger%20formulas%20in%20excel%20and%20I%20would%20greatly%20appreciate%20any%20advice%20in%20resolving%20a%20formula%20issue%20I%20have.%26nbsp%3B%20I%20have%20a%20spreadsheet%20spanning%20line%2011%20to%20line%20400%2C%20which%20will%20reflect%20the%20entries%20for%20this%20year%20potentially.%20The%20column%20'AK'%20records%20places%20meetings%20happen%2C%20ie%20'Margate'%2C%20'Ramsgate'%2C%20'On-Phone'%2C%20etc.%20To%20report%20the%20most%20common%20meeting%20location%20I%20have%20used%20the%20formula%20'%3D%40INDEX(AK11%3AAK53%2CMODE(MATCH(AK11%3AAK53%2CAK11%3AAK53%2C0)))'%2C%20which%20reports%20in%20the%20selected%20reporting%20cell%20the%20most%20common%20entry%20from%20the%20lookup%20array.%20You%20will%20note%20'AK11%3AAK53'%20as%20the%20lookup%20value%20and%20lookup%20array%20%2C%20because%20I%20I%20enter%20the%20full%20array%20'AK11%3AAK400'%2C%20where%20there%20are%20not%20yet%20any%20entries%20from%20AK54%20to%20AK400%2C%20the%20reporting%20cell%20shows%20a%20'%23N%2FA'%20error.%26nbsp%3B%20Is%20there%20anyway%20I%20can%20run%20the%20lookup%20value%3Alookup%20array%20with%20the%20full%20value%20(AK11%3AAK400)%20without%20having%20to%20update%20it%20manually%20every%20time%20I%20enter%20a%20value%20in%20the%20AK%20column%3F%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1174188%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1174855%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20report%20most%20commonly%20entered%20location%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F557813%22%20target%3D%22_blank%22%3E%40LWE_SE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(AK11%3AAK530%2CMODE(IF(AK11%3AAK530%26lt%3B%26gt%3B%22%22%2CMATCH(AK11%3AAK530%2CAK11%3AAK530%2C0))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(array%20formula).%20Please%20check%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fmost-frequently-occurring-text%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EMost%20frequently%20occurring%20text%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175590%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20report%20most%20commonly%20entered%20location%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175590%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei.%20I%20have%20entered%20the%20formula%20with%20the%20if%20statement%20exactly%20as%20dispalyed.%20There%20is%20a%20%23NAME%3F%20Error%20with%20the%20open%20parenthesis%20before%20the%20if%20and%20its%20closing%20parenthesis%20highlighted%20in%20red.%20Any%20idea%3F%20I%20appreciate%20your%20support.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177300%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20report%20most%20commonly%20entered%20location%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Success%20-%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I am relatively new to larger formulas in excel and I would greatly appreciate any advice in resolving a formula issue I have.  I have a spreadsheet spanning line 11 to line 400, which will reflect the entries for this year potentially. The column 'AK' records places meetings happen, ie 'Margate', 'Ramsgate', 'On-Phone', etc. To report the most common meeting location I have used the formula '=@INDEX(AK11:AK53,MODE(MATCH(AK11:AK53,AK11:AK53,0)))', which reports in the selected reporting cell the most common entry from the lookup array. You will note 'AK11:AK53' as the lookup value and lookup array , because I I enter the full array 'AK11:AK400', where there are not yet any entries from AK54 to AK400, the reporting cell shows a '#N/A' error.  Is there anyway I can run the lookup value:lookup array with the full value (AK11:AK400) without having to update it manually every time I enter a value in the AK column? Thank you.

3 Replies

@LWE_SE 

That's like

=INDEX(AK11:AK530,MODE(IF(AK11:AK530<>"",MATCH(AK11:AK530,AK11:AK530,0))))

(array formula). Please check here Most frequently occurring text 

Thank you Sergei. I have entered the formula with the if statement exactly as dispalyed. There is a #NAME? Error with the open parenthesis before the if and its closing parenthesis highlighted in red. Any idea? I appreciate your support.

@Sergei Baklan  Success - Thank you.