Correct MAXIFS Function to get Hotel Name

[Hany ali]

Hello my Dear ,I want Your Help For Correct Function to get Hotel Name for every Guide by The largest number of repeats for this hotel ..Note The Correct Results In Column K from Total Sheet and the Functon will be in J Column ..Mistake In The Red Cells

=INDEX(Basic!$T$2:$T$1444,MATCH(MAXIFS(Basic!$S$2:$S$1444,Basic!$P$2:$P$1444,$G2),Basic!$S$2:$S$1444, 0))

 

[Debaser]

Which version of Excel do you have?

Your issue is that the match is looking for the relevant number anywhere in column S, not just in those rows that match the criteria in the MAXIFS function.

 

[Excel Wizard]

MS365
=LET(h,Basic!T2:T1444,g,Basic!P2: P1444,VLOOKUP(G2:G26,SORTBY(HSTACK(g,h),-COUNTIFS(g,g,h,h)),2,))

Old version
=INDEX(Basic!$T$2:$T$1444,MATCH(1,FREQUENCY(0,1/(COUNTIFS(Basic!$P$2:$P$1444,G2,Basic!$T$2:$T$1444,Basic!$T$2:$T$1444)+1)),))

Correction ,Slow calculation

=INDEX(Basic!$T$2:$T$1444,MATCH(MAXIFS(Basic!$S$2:$S$1444,Basic!$P$2:$P$1444,$G2),Basic!$S$2:$S$1444*(Basic!$P$2:$P$1444=G2), 0))

 

[Hany ali]

thanks
Debaser
excel 2016
thanks Alot

Excel Wizard ..This is Required you are a genius man thank you very much for your efforts