INDEX & MATCH Formula Help

[ddub25]

I've attached an example workbook.

Table 1 is a list of names and numbers that relate to the names.

Table 2 needs to return entries in the Name and Number columns depending on a value that I place in
the Selection column of Table 2. If I placed a '1' in the Selection column, I want the highest number from Table 1
to be returned in the Number column and the corresponding Name from Table 1 in the Name column of Table 2.

I have duplicated Table 2, three times to show examples of results expected. In the last duplicate table I have placed a
'4' in the selection column which means I want the 4th highest number returned. There isn't a 4th highest number as
Frank, Rose and Kevin are all joint 3rd with a value of 3.00. However, when I place a '4' in the Selection column
I want results for 'Rose' returned as the formula should be searching by highest number first and then by order in the list.
There are three people holding a value of '3.00', they are Frank, Rose and Kevin. If '3' is entered, 'Frank' and '3.00'
should return, if '4' is entered, 'Rose' and '3.00', if '5', then 'Kevin' and '3.00' is the result.
This list would be fixed, Ideally I wouldn't re-order it.

I've tried an INDEX and MATCH formula but it has problems with duplicate numbers. In the above example it would always return
'Frank' and '3.00' whenever 3,4 or 5 are entered in the Selection column. Can anyone help? Thanks.

[Cutter]

You have to do a 'tie-breaker' approach to that.
Here's one way:
In C4 this: =RANK(B4,$B$4:$B$23)+COUNTIF(B$4:B4,B4)/100
and drag down

In D4 this: =INDEX(A$4:A$23,MATCH(SMALL($C$4:$C$23,$F4),$C$4:$C$23,0))
and copy to the other cells

[icestationzbra]

see if the attached captures your requirements...

you may have to change layout of TABLE 2 for this solution, if it works for you.

[ddub25]

Thanks very much for both of your help. It's much appreciated - problem solved. Thanks.

Dan

[Cutter]

You're welcome.