Return column with largest number of matches to multiple criteria

[KT0922]

Hi,

I'm trying to reference a specific list and find a match in an array that has the most commonalities with that list. The goal in the table below would be to return the name "Larry" as he has the most matches to the target list.

[TABLE="width: 528"]
<colgroup><col width="88" span="6" style="width:66pt"> </colgroup><tbody>[TR]
[TD="width: 88, align: left"]List[/TD]
[TD="width: 88, align: left"]Name[/TD]
[TD="width: 88"][/TD]
[TD="width: 88, align: left"]Jeff[/TD]
[TD="width: 88, align: left"]George[/TD]
[TD="width: 88, align: left"]Larry[/TD]
[/TR]
[TR]
[TD="align: left"]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Apples[/TD]
[TD="align: left"]Lemon[/TD]
[TD="align: left"]Apples[/TD]
[/TR]
[TR]
[TD="align: left"]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Oranges[/TD]
[TD="align: left"]Lime[/TD]
[TD="align: left"]Oranges[/TD]
[/TR]
[TR]
[TD="align: left"]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Melons[/TD]
[TD="align: left"]Bananas[/TD]
[TD="align: left"]Bananas[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated!

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

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

1​

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

2​

[/td][td]

1​

[/td][td]

3​

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

2​

[/td][td]List[/td][td]Name[/td][td][/td][td]Jeff[/td][td]George[/td][td]Larry[/td][/tr]
[tr][td]

3​

[/td][td]Apples[/td][td]Larry[/td][td][/td][td]Apples[/td][td]Lemon[/td][td]Apples[/td][/tr]
[tr][td]

4​

[/td][td]Oranges[/td][td][/td][td][/td][td]Oranges[/td][td]Lime[/td][td]Oranges[/td][/tr]
[tr][td]

5​

[/td][td]Bananas[/td][td][/td][td][/td][td]Melons[/td][td]Bananas[/td][td]Bananas[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D1 enter and copy across:

=SUMPRODUCT(COUNTIFS(D3:D5,$A$3:$A$5))

In B3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$2:$F$2,SMALL(IF($D$1:$F$1=MAX($D$1:$F$1),COLUMN($D$2:$F$2)-COLUMN($D$2)+1),ROWS($B$3:B3))),"")

 

[Joyner]

Hello and welcome to the board. I'm just going to ask, because I know everyone will want to know, what do you want to do in the case of a tie, or can there never be a tie?

Never mind, the master beat me to the response

 

[KT0922]

Thank you both! What if I also wanted to rank the top three matches for example? So the first name would be Larry, then Jeff, etc..

If i'm working with a much larger subset of data and am looking to do this sort of selection for multiple "lists," is there a better way to set this up so that you don't have multiple rows of sumproduct(countifs?