Appears Most

[markoakes]

I have a table with 3 columns, A= Name, B= City, C=Item. This table may have thousands of rows of data. I then have a second table that list each City and each Item for that city. In column H I need to return the Name that appears most often for that combination of City and Item. I this example for Modesto and Table, Joe appeared most often. Incase of a tie, Miami and Chair I need the name that appears first in the list, Bill.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

Name​

[/td][td]

City​

[/td][td]

Item​

[/td][td][/td][td][/td][td]

City​

[/td][td]

Item​

[/td][td]

Name​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Joe[/td][td]Modesto[/td][td]Table[/td][td][/td][td][/td][td]Modesto[/td][td]Table[/td][td]Joe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Joe[/td][td]Modesto[/td][td]Table[/td][td][/td][td][/td][td]Modesto[/td][td]Chair[/td][td]Joe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Joe[/td][td]Modesto[/td][td]Chair[/td][td][/td][td][/td][td]Modesto[/td][td]Lamp[/td][td]Tom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Joe[/td][td]Modesto[/td][td]Chair[/td][td][/td][td][/td][td]Miami[/td][td]Table[/td][td]Bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Tom[/td][td]Modesto[/td][td]Table[/td][td][/td][td][/td][td]Miami[/td][td]Chair[/td][td]Bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Tom[/td][td]Modesto[/td][td]Chair[/td][td][/td][td][/td][td]Miami[/td][td]Lamp[/td][td]Sally[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Tom[/td][td]Modesto[/td][td]Lamp[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Tom[/td][td]Modesto[/td][td]Lamp[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Bill[/td][td]Miami[/td][td]Table[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Bill[/td][td]Miami[/td][td]Table[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Bill[/td][td]Miami[/td][td]Chair[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]Bill[/td][td]Miami[/td][td]Chair[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]Sally[/td][td]Miami[/td][td]Table[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]Sally[/td][td]Miami[/td][td]Chair[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]Sally[/td][td]Miami[/td][td]Chair[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]Sally[/td][td]Miami[/td][td]Lamp[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Marcelo Branco]

Maybe...

Assumes headers in row 2

Array formula in H3 copied down
=INDEX(A$3:A$18,MODE(IF(B$3:B$18=F3,IF(C$3:C$18=G3,{0,0}+MATCH(A$3:A$18,A$3:A$18,0)))))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[markoakes]

That seems to be working, Thanks for the help.

 

[Oscar Cronquist]

Array formula in cell H3:

=INDEX($A$3:$A$18, MATCH(MAX(COUNTIFS($B$3:$B$18, F3,$C$3:$C$18, G3,$A$3:$A$18, $A$3:$A$18)), COUNTIFS($B$3:$B$18, F3, $C$3:$C$18 ,G3, $A$3:$A$18, $A$3:$A$18), 0))

Copy cell H3 and paste to cells below.