Results 1 to 5 of 5

INDEX & MATCH Formula Help

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    INDEX & MATCH Formula Help

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1