+ Reply to Thread
Results 1 to 6 of 6

Lookup Values according to 2 criteria, with duplicate values in both.

  1. #1
    Registered User
    Join Date
    10-16-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Lookup Values according to 2 criteria, with duplicate values in both.

    Hello - I'm trying to work out a formula that will lookup the Team Name and Position and return the Names, horizontally.

    For example:

    QB QB QB QB QB RB RB WR
    Chiefs- Jim Tom Jeff Aaron Steve
    Panthers- Mike Connor Erick Andrew Nick

    Data:

    Team Position Name
    Chiefs QB Jim
    Chiefs QB Tom
    Chiefs QB Jeff
    Panthers QB Mike
    Panthers QB Connor
    Chiefs RB Aaron
    Chiefs RB Steve
    Panthers WR Nick
    Panthers QB Erick
    Panthers QB Andrew


    I've developed the following formulas but am still encountering issues with both.

    1) =IF(COLUMNS($G2:G2)>$B$21,"",INDEX($C$2:$C$11,SMALL(IF($A$2:$A$11=$F$2,ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($G2:G2))))

    ** I cannot get this formula to differentiate between teams


    2) =INDEX($C$2:$C$11,MATCH($F5&G$1,INDEX($A$2:$A$11&$B$2:$B$11,),0))

    ** I cannot figure out how to nest a SMALL formula in here in order to ignore duplicates and take the next values
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Lookup Values according to 2 criteria, with duplicate values in both.

    Try copy / pasting and array-entering this in G2. Fill down and across to L3.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

  3. #3
    Registered User
    Join Date
    10-16-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: Lookup Values according to 2 criteria, with duplicate values in both.

    Dave - You've just cured be headache that's been lingering for the past week. Thank you. One question - Is there a way to modify the formula so that I can copy past cell L3?

    Thanks again, Dave.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Lookup Values according to 2 criteria, with duplicate values in both.

    Quote Originally Posted by dys5315 View Post
    .............. One question - Is there a way to modify the formula so that I can copy past cell L3?
    Yes. If you can remove the IF > B21 condition then the formula would be (array-entered again)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    When it runs out of data it will return blanks. This one also returns the WR in the Panthers set.

  5. #5
    Registered User
    Join Date
    10-16-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: Lookup Values according to 2 criteria, with duplicate values in both.

    Thank you!!!!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Lookup Values according to 2 criteria, with duplicate values in both.

    You're welcome, and thanks for the rep.

+ 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. Lookup duplicate values - Help please
    By tony126 in forum Excel General
    Replies: 5
    Last Post: 05-27-2015, 06:23 PM
  2. lookup with duplicate values
    By Jouzea in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2015, 10:18 PM
  3. Vlookup with duplicate Lookup values
    By fabvolky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-19-2015, 12:05 PM
  4. [SOLVED] PLEASE HELP: Return Duplicate Values with V-lookup
    By Funky_Finance in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-23-2014, 12:24 AM
  5. [SOLVED] conditional lookup for duplicate values
    By Abhi1687 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2013, 04:39 PM
  6. [SOLVED] Lookup + Duplicate Values
    By keithmacd in forum Excel General
    Replies: 4
    Last Post: 06-12-2012, 07:49 PM
  7. Need help on how to lookup values that duplicate
    By Pat Parker in forum Excel General
    Replies: 4
    Last Post: 01-26-2012, 11:56 PM

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