Using Index and Rank to show a specific number of values from multiple lists

R

rex_411

New Member
Joined
Apr 1, 2015
Messages
9
The purpose of this excel is to display a specific number of values in columns H or I based on a cell value and a name column based on a random number ranking.

The problem with the formula below is the lack of specification on the Index and/or Rank function that is not taking account the change of name. While it displays the correct number of values it's pulling values from the whole column as opposed to just from those belonging to the specified person.

The data below should populate a spreadsheet and the formulas will replace the values in columns H and I.

Formula in H2 =IF(AND(C2="person 1",A2<=7),(INDEX(D:D,RANK(G2,G:G))),"")

Formula in I2 =IF(AND(D2="Person 1",B2<=7),(INDEX(E:E,RANK(H2,H:H))),"")

Formula in H3:H236 =IF(AND(C3=C2,A3<=VLOOKUP(C2,$K$2:$M$21,2,FALSE)),(INDEX(D:D,RANK(G3,G:G))),IF(AND(C3=C4,A3<=VLOOKUP(C2,$K$2:$M$21,2,FALSE)),(INDEX(D:D,RANK(G3,G:G))),""))

Formula in I3:I236 =IF(AND(C3=C2,B3<=VLOOKUP(C2,$K$2:$M$21,3,FALSE)),(INDEX(D:D,RANK(G3,G:G))),IF(AND(C3=C4,B3<=VLOOKUP(C2,$K$2:$M$21,3,FALSE)),(INDEX(D:D,RANK(G3,G:G))),""))


Excel 2010
ABCDEFGHIJKLM
1Criteria1Criteria2Person NameFile #Value Field1Value Field2Random #File# for Critiera1File# for Critiera2Person NameCriteria1Criteria2
200Person 15523466$ 541.880.3532665505944Person 173
31Person 15522183$ 768.550.8199745505957Person 242
42Person 15524778$ 773.830.854965519024Person 342
53Person 15502127$ 1,200.300.9855085523466Person 4103
64Person 15517652$ 1,428.840.4213945519711Person 5123
75Person 15504259$ 1,510.190.7877035526331Person 6133
86Person 15522407$ 1,726.050.3795745514049
97Person 15521914$ 2,066.020.2962115511927
108Person 15517601$ 2,106.750.796037
119Person 15523432$ 2,637.920.951678
1210Person 15525693$ 2,708.060.501158
1311Person 15516669$ 2,717.190.512559
1412Person 15505035$ 2,972.460.749989
1513Person 15519024$ 3,418.470.259492
1614Person 15506951$ 3,558.310.529597
1715Person 15505957$ 4,156.140.861768
1816Person 15503813$ 5,036.290.475368
1917Person 15519909$ 5,052.160.260247
2018Person 15507720$ 5,458.280.423069
2119Person 15523398$ 5,846.330.211517
2220Person 15526331$ 5,981.930.272262
2321Person 15512391$ 8,062.630.575073
241Person 15500711$ 2,308.480.3505925510038
252Person 15507074$ 16,135.880.9470655502127
263Person 15509100$ 16,430.720.4658965510867
274Person 15509448$ 8,252.100.898739
285Person 15510596$ 8,565.580.474861
296Person 15510678$ 3,260.370.202444
307Person 15518219$ 5,065.450.363097
318Person 15519785$ -0.343625
329Person 15519978$ 17,034.340.633883
3310Person 15522760$ 6,259.390.097369
341Person 25508553$ 877.090.5937645511807
352Person 25526170$ 1,135.630.3177445506432
363Person 25518889$ 1,287.860.1023425509736
374Person 25523012$ 1,287.860.5479715528659
385Person 25527822$ 1,675.190.219639
396Person 25511807$ 1,806.630.112683
407Person 25505485$ 1,922.270.598361
418Person 25505572$ 2,296.450.290566
429Person 25528659$ 3,277.410.6787
4310Person 25517481$ 3,322.250.49769
4411Person 25528908$ 3,375.870.805792
4512Person 25504629$ 3,479.520.523029
4613Person 25510840$ 5,457.510.05475
471Person 25504481$ 3,627.060.6704545508553
482Person 25512174$ 5,777.100.6783375522760
493Person 25518369$ 3,683.580.406746
504Person 25518510$ 15,416.260.457473
511person 35519991$ 1,308.190.6676395526170
522person 35530146$ 1,619.800.4332325528172
533person 35506000$ 2,485.440.9044925521914
544person 35510867$ 2,896.260.1181095494326
555person 35517372$ 3,402.160.094509
566person 35519116$ 4,024.550.753897
577person 35528172$ 4,116.110.482778
588person 35505755$ 4,285.160.303059
599person 35523026$ 6,942.660.934322
6010person 35519711$ 8,779.010.214863
6111person 35516974$ 10,236.460.766763
6212person 35519010$ 18,789.430.832706
631person 35504732$ 8,625.590.0411185515912
642person 35514049$ 5,046.080.0895695502333
653person 35514897$ 5,866.530.980576
664person 35527626$ 5,825.820.932558
671person 45505944$ 91.210.9447395517652
682person 45510038$ 94.160.0607345503859
693person 45520734$ 183.380.3820415504732
704person 45519195$ 309.680.8811075525693
715person 45519271$ 405.340.3597645527626
726person 45506432$ 430.780.2096525501673
737person 45516875$ 447.960.5269745528908
748person 45511927$ 660.330.1444785506185
759person 45502475$ 673.700.7692675507074
7610person 45511880$ 701.280.3310575519271
7711person 45508358$ 722.250.662813
7812person 45510150$ 780.190.819091
7913person 45505104$ 812.330.694237
8014person 45514961$ 869.940.869905
8115person 45505186$ 1,011.750.191852
8216person 45501673$ 1,029.740.78734
8317person 45518379$ 1,112.650.996997
8418person 45512711$ 1,120.740.42425
8519person 45506185$ 1,240.520.058602
8620person 45511660$ 1,335.230.883844
8721person 45494326$ 1,397.900.795586
8822person 45510922$ 1,401.970.053654
8923person 45509736$ 1,427.440.728766
9024person 45510969$ 1,526.400.468481
9125person 45507383$ 1,532.200.785363
921person 45498726$ 5,347.200.1242245511660
932person 45502333$ 5,210.380.3369985519195
943person 45503859$ 14,033.890.4549065519116
954person 45507718$ 3,374.770.752951
965person 45510907$ 11,495.360.394693
976person 45511602$ 1,507.940.518461
987person 45515912$ 2,823.650.092585
998person 45522538$ 3,275.900.58766
Sheet1
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
I know it's only been a few days, but I was hoping someone could solve my quandary. Essentially how can you set up an index statement that takes into account a column of changing values. Maybe I am trying to put to much into an Index statement... overcomplicating the problem. In the actual spreadsheet there will be 20+ names so making individual statements like D:D= 'Person1' is not really an option. I am at a total loss for a next step.
 
Upvote 0
I seem to still be running at deficit here. If someone could post and let me know if it's the depth of the question or my presentation of the issue which is preventing the solution. Am I asking the impossible? I highly doubt it based on some of the code I have used from this site... So the presentation of the question then? Please let me know if my spreadsheet is to much and I need to simplify down. The two columns are the same so an answer for one is an answer for the other. Lastly, if there is another means for me to obtain an answer maybe someone could let me know that as well. Thanks for any help in either clarification or an answer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
RANK with INDEX MATCH
Replies
4
Views
809
Peter_SSs
Peter_SSs
N
  • Question Question
How to add content of two referenced rows and columns to be added if numbers and keep separate if text in one cell
Replies
0
Views
314
nsa1
N
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
F
  • Solved
Formula to show in 4 columns the letters from one column with condition
Replies
6
Views
277
Fractalis
F
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
237
Joe4
Joe4

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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