+ Reply to Thread
Results 1 to 6 of 6

Lookup corresponding fields of identified row in table with duplicates

  1. #1
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Question Lookup corresponding fields of identified row in table with duplicates

    Hi All,

    I'm running into a real challenge looking up corresponding fields in a table with several records of data. I have table (Excel sheet attached) which contains a unique key in column A. Within the key the table is is sorted on column B (Value1). I've been able to look up the top 5 largest values in column D (Diff) where the condition in column E matches 'Red' by using an array version of the Large function.

    The top 5 largest values in the Diff column where Condition meets 'Red' are 50, 50, 40, 40 and 30 (rows 4, 7, 3, 10 and 9). I'm looking for a formula to retrieve the Key values in column A. As the table is not sorted on the Diff column and the Diff column can contain duplicates, I'm having trouble finding a formula to retrieve the corresponding Key values (Column A) of the rows of the top 5 largest fields which match the specified condition.

    Key Value1 Value2 Diff Condition
    1 100 60 40 Black
    2 99 49 50 White
    3 98 58 40 Red
    4 97 47 50 Red
    5 96 76 20 Black
    6 95 65 30 White
    7 94 44 50 Red
    8 93 23 70 White
    9 92 62 30 Red
    10 91 51 40 Red

    Any help will be greatly appreciated!

    Thanks in advance, Eric

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Lookup corresponding fields of identified row in table with duplicates

    Hi and welcome to the forum.

    In C20 and copy down, this Array formula.

    Please Login or Register  to view this content.
    Is this, works for you?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Question Re: Lookup corresponding fields of identified row in table with duplicates

    Hi Fotis, Thanks for the quick reply!

    The specified formula results in 2, 4, 1, 3 and 6. These are the row keys for the top 5 largest values in the table, however they unfortunately don't take the condition 'Red' into account (B15). Is there anyway to take the condition in cell B15 (Value in column E = 'Red') into account?

    Based in the condition Red, the Keys I'm looking for are 4, 7, 3, 10 and 9.

    Hope this can be added to the formula in someway.

    I added the condition field in C20..C24 in the attached Excel sheet. Perhaps these fields can be used in the formula.

    Regards,

    Eric
    Last edited by Eric_25; 06-25-2012 at 08:02 AM. Reason: updated attached Excel sheet

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Lookup corresponding fields of identified row in table with duplicates

    So Eric, try this.

    =INDEX($A$2:$A$11,SMALL(IF($D$2:$D$11&$E$2:$E$11=B20&$B$15,ROW($A$2:$A$11)-ROW($A$2)+1),COUNTIF($B$20:$B20,$B20)))

    Is this, works for you?

  5. #5
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Lookup corresponding fields of identified row in table with duplicates

    That's exactly what I was looking for!!

    Thanks a lot!

    Eric

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Lookup corresponding fields of identified row in table with duplicates

    You are welcome!

    Thanks for the feed back!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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