Formula to find multiple results from 1 table source

deduwa

Board Regular
Joined
Jul 28, 2015
Messages
110
Hi,

I have the following table;

AB
1Alex60
2Brad60
3Chris50
4Dave60
5Elli70

<tbody>
</tbody>











I then rank 1 to 5 from highest to smallest. Using =LARGE(B:B,1), =LARGE(B:B,2) and so on down to =LARGE(B:B,5)

According to the value of each ranked item in column E below, I need to then assign the person in that position. I get the following output;

ABCDEF
1Alex60Rank
2Brad60170Elli
3Chris50260Alex
4Dave60360Alex
5Elli70460Alex
6550Chris

<tbody>
</tbody>













I get Alex appearing 3 times in column F but need the name of the other 2 people that also have a value of 60 (i.e. Brad and Dave). I am using =INDEX($A$1:$A$5,MATCH(E2,$B$1:$B$5,1)) in column F above.

Without having to use VBA code, how can I solve this so that in rank 2, 3 & 4 I get the names Alex, Brad & Dave? These 3 names can appear in any order and doesn't have to be alphabetical. Also the source data range in terms of number of rows is dynamic.

Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Match will always return the first match found that is why Alex is listed three times.

try

Excel 2010
ABCDEF
1Alex60Rank
2Brad60170Elli
3Chris50260Alex
4Dave60360Brad
5Elli70460Dave
6550Chris
Sheet1
Cell Formulas
RangeFormula
E2=INDEX($B$1:$B$5,MATCH(LARGE($B$1:$B$5,ROWS($E$2:E2)),$B$1:$B$5,0))
E3=INDEX($B$1:$B$5,MATCH(LARGE($B$1:$B$5,ROWS($E$2:E3)),$B$1:$B$5,0))
E4=INDEX($B$1:$B$5,MATCH(LARGE($B$1:$B$5,ROWS($E$2:E4)),$B$1:$B$5,0))
E5=INDEX($B$1:$B$5,MATCH(LARGE($B$1:$B$5,ROWS($E$2:E5)),$B$1:$B$5,0))
E6=INDEX($B$1:$B$5,MATCH(LARGE($B$1:$B$5,ROWS($E$2:E6)),$B$1:$B$5,0))
F2{=IF(COUNTIF($E$2:$E$6,E2)=1,INDEX($A$1:$A$5,MATCH(E2,$B$1:$B$5,0)),INDEX($A$1:$A$5,SMALL(IF(E2=$B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1)+1,999999),COUNTIF($E$2:E2,E2))))}
F3{=IF(COUNTIF($E$2:$E$6,E3)=1,INDEX($A$1:$A$5,MATCH(E3,$B$1:$B$5,0)),INDEX($A$1:$A$5,SMALL(IF(E3=$B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1)+1,999999),COUNTIF($E$2:E3,E3))))}
F4{=IF(COUNTIF($E$2:$E$6,E4)=1,INDEX($A$1:$A$5,MATCH(E4,$B$1:$B$5,0)),INDEX($A$1:$A$5,SMALL(IF(E4=$B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1)+1,999999),COUNTIF($E$2:E4,E4))))}
F5{=IF(COUNTIF($E$2:$E$6,E5)=1,INDEX($A$1:$A$5,MATCH(E5,$B$1:$B$5,0)),INDEX($A$1:$A$5,SMALL(IF(E5=$B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1)+1,999999),COUNTIF($E$2:E5,E5))))}
F6{=IF(COUNTIF($E$2:$E$6,E6)=1,INDEX($A$1:$A$5,MATCH(E6,$B$1:$B$5,0)),INDEX($A$1:$A$5,SMALL(IF(E6=$B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1)+1,999999),COUNTIF($E$2:E6,E6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks Scott - that works. My columns A & B are dynamic number of rows (hundreds), and top 5 ranked will change based on this dynamic range. How can I adjust the worksheet & array formulas to capture this?
 
Upvote 0
If you use a helper column in C then you can use very simple formulas:


Book1
ABCDEFGHI
1namevaluerankRankValueName
2Alex6021Elli70
3Brad6032Alex60
4Chris5053Brad60
5Dave6044Dave60
6Elli7015Chris50
7
8
9
10
11
12
Sheet1
<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br />

<b><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=RANK.EQ(<font color="Blue">B2,$B$2:$B$6</font>)+COUNTIFS(<font color="Blue">$B$2:$B$6,B2,$A$2:$A$6,"<"&A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=SMALL(<font color="Blue">$C:$C,ROWS(<font color="Red">F$1:F1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=INDEX(<font color="Blue">A:A,MATCH(<font color="Red">$F2,$C:$C,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=INDEX(<font color="Blue">B:B,MATCH(<font color="Red">$F2,$C:$C,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Thanks. I tried your suggestion but if I now go and enter "Fred" in cell A7 and "55" in cell B7, I get errors.

Basically is there an automatic way so that the rank table replaces Chris = 50 with Fred = 55 in cell range F6:H6?

Ideally I also do not want to drag any formulas down in column C that captures any new data coming in.

Thanks
 
Upvote 0
You could convert the range that you already have (A1:C6, where in C there are already formulas) to a table (named Table1). And whenever you add a new record in column A and B, the formula that is in C will update automatically.

then in F2: =IF(ROWS(F$1:F1)>MAX(Table1[rank]),"",SMALL(Table1[rank],ROWS(F$1:F1)))
then in G2: =IF($F2="","",INDEX(Table1[name],MATCH($F2,Table1[rank],0)))
then in H2: =IF($F2="","",INDEX(Table1[value],MATCH($F2,Table1[rank],0)))

and you drag these 3 down as much as your data could go
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top