# INDEX & MATCH formula help

This is a discussion on INDEX & MATCH formula help within the Excel Questions forums, part of the Question Forums category; Table 1 Name Number Albert 3.05 Frank 3.00 Cynthia 2.98 Herbert 3.01 Dorothy 2.96 Delia 2.90 Elsie 2.90 George 2.90 ...

1. ## INDEX & MATCH formula help

 Table 1 Name Number Albert 3.05 Frank 3.00 Cynthia 2.98 Herbert 3.01 Dorothy 2.96 Delia 2.90 Elsie 2.90 George 2.90 Audrey 2.88 Arthur 2.90 Rose 3.00 Winifred 2.90 Cecil 2.88 Mable 2.87 Godfrey 2.88 Sid 2.88 Reginald 2.88 Roy 2.86 Deirdre 2.90 Kevin 3.00 Table 2 Name Number Selection Albert 3.05 1 Duplicate Table 2's Name Number Selection Herbert 3.01 2 Name Number Selection Frank 3.00 3 Name Number Selection Rose 3.00 4

Table 1 is in Range A8:A105

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.

2. ## Re: INDEX & MATCH formula help

Place to lookup at in cell J10

=INDEX(A\$8:A\$27,MATCH(LARGE(\$B\$8:\$B\$105+ROW(\$B\$8:\$B\$105)/1000000,\$J10),\$B\$8:\$B\$105+ROW(\$B\$8:\$B\$105)/1000000,0))

Confirm with Ctrl+Shift+Enter

Regards

3. ## Re: INDEX & MATCH formula help

Thanks sailepaty, works great.

Dan

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•