+ Reply to Thread
Results 1 to 8 of 8

Formula to lookUp something but with duplicate

  1. #1
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Formula to lookUp something but with duplicate

    Hello Excel frinds
    I have a Database with item from my store in column A,B,C,D

    in G2 i need a formula that lookUp the value in F2 and retrive the corrisponding record,
    I tried with Lookup function but doesn't work because i ve duplicates.
    is possibile with the formula?
    Thank for your help
    Attached Files Attached Files
    Last edited by GerryZucca; 01-18-2021 at 04:49 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to lookUp something but with duplicate

    Please try at G2
    =INDEX($B$1:$D$20,SMALL(IF($A$2:$A$20=F2,ROW($A$2:$A$20)),COUNTIF(F$2:F2,F2)),)
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Re: Formula to lookUp something but with duplicate

    Wonderfull!! Great Job Bo_Ry
    thank so much

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to lookUp something but with duplicate

    In G2:
    =INDEX(B$2:B$20,AGGREGATE(15,6,(ROW($2:$20)-1)/($A$2:$A$20=$F2),COUNTIF($F$2:$F2,$F2)))

    Copy down and across. Apply appropriate cell format as required.

    EDIT: Aggregate is used for backward compatibility reasons (If recipient of your file does not use Office 365, then this is required as other version does not support new array behaviour).
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Formula to lookUp something but with duplicate

    With a help column I came to this solution
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formula to lookUp something but with duplicate

    Or using Filter
    In G2 filled down
    =INDEX(FILTER($B$2:$D$20,$A$2:$A$20=F2),COUNTIF(F$2:F2,F2),0)

  7. #7
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Re: Formula to lookUp something but with duplicate

    Thank everybody SOLVED

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formula to lookUp something but with duplicate

    You're welcome & thanks for the feedback.

+ 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. [SOLVED] Lookup on Duplicate Values
    By arasan25 in forum Excel General
    Replies: 4
    Last Post: 08-20-2017, 10:17 AM
  2. Lookup duplicate values - Help please
    By tony126 in forum Excel General
    Replies: 5
    Last Post: 05-27-2015, 06:23 PM
  3. lookup with duplicate values
    By Jouzea in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2015, 10:18 PM
  4. Lookup for Duplicate Matches
    By Yash20 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-09-2014, 10:18 AM
  5. [SOLVED] Lookup + Duplicate Values
    By keithmacd in forum Excel General
    Replies: 4
    Last Post: 06-12-2012, 07:49 PM
  6. 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
  7. Duplicate Lookup
    By smandula in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2005, 09:05 PM

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