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

The purpose of the attached 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 in Column H and I 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.

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.

I am not certain I understand what you want to do, but looking at your formula(s) I will hazard a guess and start with this in cell H2 and filled down and across to column I.

Formula:

Please Login or Register  to view this content.

Since I could not find ANTHONY URSUA in any of the columns and all that data appears to be formula driven as well I couldn't test this one. I also had to guess which columns / ranges you want to reference. Have a look at the attached. Let us know what you think.

Show the expected results in the file.

FlameRetired and kvsrinivasamurthy - I apologize for leaving the real name in the Criteria 2 cell it was an oversight. If you can figure out column H formula will be the same for Column I. Don't really worry about formulas H2 or I2 - Please focus attention starting in the cell H3 and Column H.

That said, the specific cells in column H which show a result are correct, but the results shown are incorrect. The results shown need to be based on the File #'s for just that person. Example, Person 2 has Column D values of [5508553, 5526170, 5518889, 5523012, 5527822, 5511807, 5505485, 5505572, 5528659, 5517481, 5528908, 5504629, 5510840, 5504481, 5512174, 5518369, 5518510] yet the results shown in Column H for Person 2 are [5509343, 5509755, 5516875, 5510969]. The first value shown in column H for Person 2 is 5509343 which is a Column D value associated to Person 6.

Again, the goal is to show a specific count of File#'s next to each person based on only the Column D values in the same row as the Person Name. In English, the perfect formula would say: Look at the Person Name in Column C, Match it to Column K then find the number of results to show from Column L... once determined look at all the Column D values in the same row as Person Name and show the determined number of results but give them to me in no order.

Hope this helps and thanks for taking the time to reply.

Double posted.

The reason for the RAND is the spreadsheet is used to choose a random selection of File#'s from Column D for each person. The fact they change each time a key is pressed is not really an issue, unless it's preventing the formula from working. If the current formula in Column H could be modified to only show the specific number of Indexed values from the Column D values which are on the same row as the corresponding Person Name in Column C it would be perfect.

I think I'm still confused but differently this time.

Try this formula in H2:I236.

Formula:

Please Login or Register  to view this content.

The file is attached.
Is this closer to what you want?

I see what you did and appreciate you trying... In column H, you showed the value from Column D based on the value shown in column L for each person. Initially I should have clarified that the values in Column L and M were the amount of results I wanted to see in Columns H and I.

For Person 1, I should see 7 File #'s (selected at random) which are associated (in the same row) to person 1 in column H. For Person 2, I should see 4 File #'s in column H, etc. my original spreadsheet had the correct layout but not the correct values in Column H because it was pulling from the entire list of File #s and not just those belonging to the Person.

I was thinking of trying to separate the people onto different tabs and try to work the problem that way...

Please disregard my previous post. I think I am finally clear on what you want. This randomizes within the limits of each group and ranks within each group.

Formula:

Please Login or Register  to view this content.

I put some formatting in column D so I could keep track of the group break points. I put conditional formatting rules on columns H and I to test for duplicate values. So far none have shown up within or across 'Person #' groups.

I left the formula in column N where I worked out a "portable range" strategy. I left it there so you can see what I did in H:I. They can be deleted without affecting H:I.

Apologies for being so slow on the uptake.
The file is attached.