+ Reply to Thread
Results 1 to 3 of 3

Index Match Small with duplicate value.

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    Arlington, Texas
    MS-Off Ver
    Excel 2007
    Posts
    60

    Index Match Small with duplicate value.

    Please help.

    I have an equation below that work great except when row 2 has a duplicate value. When this happen row 5 only return the first value in row 1 and not the other duplicated values.

    =INDEX($B$1:$AF$1,MATCH(SMALL($B$2:$AF$2,COLUMN(B$1)-1),$B$2:$R$2,0))


    Pleas see attachment. How can I have row 5 return the following value: B5 = A, C6 = B and D5 = C.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Index Match Small with duplicate value.

    Hi,

    Maybe a small increment to each to differentiate:

    =INDEX($B$1:$R$1,MATCH(SMALL(INDEX($B$2:$R$2+COLUMN($B$2:$R$2)/10^9,,),COLUMNS($A:A)),INDEX($B$2:$R$2+COLUMN($B$2:$R$2)/10^9,,),0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    10-18-2013
    Location
    Arlington, Texas
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Index Match Small with duplicate value.

    thank you. just what i needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. problems using SMALL and INDEX(MATCH) in a table
    By lookingforhelp2014 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2014, 11:12 AM
  2. Index( Match( Small( If
    By TravCAH in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2013, 08:41 AM
  3. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 AM
  4. Match/index/large/small - need for top and low scores
    By Lenisghio in forum Excel General
    Replies: 1
    Last Post: 05-29-2012, 10:13 PM
  5. Large, Small, Index, Match formula combos
    By taudano in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 01:53 AM

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