+ Reply to Thread
Results 1 to 3 of 3

Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

  1. #1
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

    Hi all,

    I have an issue with returning values from duplicates.

    A: 10000 Names (They include both Characters and Numbers)
    B: 1000 Prices (Only Numbers - Numerical Values, Non-Negative)

    C: List of Prices
    D: Return the Names

    I want to search for a name by PRICE. Because the prices many times are repeated the vlookup stops at the first name it reads.

    I have read somewhere on internet that with MATCH, OFFSET, MATCH, INDEX, COUNTIF I can create an array formula.
    MATCH -> Finds the row which first value occurs
    Offset -> It is used for the next match (N+1)
    2nd MATCH --> which will offset the function and skips the row which contains the first match and will return a row number relative to the top of its array.
    Index --> to read the table of data
    Countif --> How many MATCH (down-counter) to count. When is 0, it is supposed that we have found all the values we requested at the beginning.

    A sample formula which I started is this one =INDEX($A$5:$A$60,MATCH(C1,$A$5:$A$60,0)) but I dont know how to combine the things above to make it work. I have been looking around the forum and although I thought that I could find something, it ended up without success.

    Any help would be much appreciated. Thank you

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

    Perhaps post a small example of your sheet showing "before" and "after"?

  3. #3
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

    Hi, please find the sample file here. Thank you very much for any help.

    This is the formula I managed to find but it doesnt work with the duplicates - it returns error when it finds the duplicate.

    =INDEX(AF:AF,SMALL(IF(AJ$5:AJ$60=AN26,ROW(AJ$5:AJ$60)),COUNTIF(AN$20:AN26,AN26)),1)

    Any help much appreciated
    Last edited by dim06; 03-17-2012 at 07:05 AM.

+ Reply to Thread

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