Formula to find multiple results from 1 table source

D

deduwa

Board Regular
Joined
Jul 28, 2015
Messages
110
Hi,

I have the following table;

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alex[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Brad[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chris[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dave[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Elli[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]











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;

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/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]Alex[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]Rank[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Brad[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]70[/TD]
[TD]Elli[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chris[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dave[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]60[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Elli[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]60[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]Chris[/TD]
[/TR]
</tbody>[/TABLE]













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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
680
wsr429
W
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
309
Fluff
Fluff
R
  • Question Question
How do I rank a table, formula or macro please?
Replies
5
Views
123
Peter_SSs
Peter_SSs
C
  • Solved
Sum of Lowest Rankings
2
Replies
10
Views
335
Fluff
Fluff
N
  • Solved
How to calculate the percentage?
Replies
9
Views
308
Phuoc
P

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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