Ranking formula based on variable

[scott_od]

I have a table with country performance data & I would like to be able to see the best performing country on a certain date. The idea is that if I change the date in cell E10 (highlighted in red), the formula in E11 would display the country name of the highest value on that date.

In the event that there are 2 countries who both have the highest value, I would like both countries displayed if possible

[TABLE="width: 524"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]New Sales[/TD]
[TD="align: right"]18-Sep[/TD]
[TD="align: right"]19-Sep[/TD]
[TD="align: right"]20-Sep[/TD]
[TD="align: right"]21-Sep[/TD]
[TD="align: right"]22-Sep[/TD]
[TD="align: right"]25-Sep[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]China[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]India[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]Korea[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Philippines[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Singapore[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date:[/TD]
[TD="align: right"]18-Sep[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Best Performer:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is anybody able to help with this, as so far I have not had much luck.

Thank you.

 

[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][td]

G​

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

1​

[/td][td] New Sales[/td][td]

18-Sep​

[/td][td]

19-Sep​

[/td][td]

20-Sep​

[/td][td]

21-Sep​

[/td][td]

22-Sep​

[/td][td]

25-Sep​

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

2​

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

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

5​

[/td][td]

5​

[/td][td]

3​

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

3​

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

15​

[/td][td]

25​

[/td][td]

0​

[/td][td]

7​

[/td][td]

0​

[/td][td]

0​

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

4​

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

25​

[/td][td]

25​

[/td][td]

21​

[/td][td]

24​

[/td][td]

28​

[/td][td]

29​

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

5​

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

1​

[/td][td]

3​

[/td][td]

2​

[/td][td]

6​

[/td][td]

5​

[/td][td]

5​

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

6​

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

25​

[/td][td]

1​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

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

7​

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

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

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

8​

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

9​

[/td][td] [/td][td] [/td][td] [/td][td] Date:[/td][td]

18-Sep​

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

10​

[/td][td] [/td][td] [/td][td] Best Performer(s)[/td][td] [/td][td]India[/td][td][/td][td][/td][/tr]
[tr][td]

11​

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

12​

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

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

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(INDEX($B$2:$G$7,0,MATCH(E$9,$B$1:$G$1,0))=MAX(INDEX($B$2:$G$7,0,MATCH(E$9,$B$1:$G$1,0))),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($E$10:E10))),"")

 

[scott_od]

Aladin you are a genie(us) - thanks so much for your help ray:

 

[Aladin Akyurek]

Aladin you are a genie(us) - thanks so much for your help ray:

Glad to help.