+ Reply to Thread
Results 1 to 7 of 7

Separate duplicate numbers using INDEX MATCH formula

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Separate duplicate numbers using INDEX MATCH formula

    The problem is the formula in Column AE4:AE25
    =IFERROR(INDEX($AD$4:$AD$25,MATCH(AF5,AC$4:AC$25,0)),"")

    I get duplicate numbers, number 5 twice and number 6 is missing.

    I have searched for a solution, but the formula for duplicates I've found don't seem to work
    with the way I have me numbers placed in the uploaded spreadsheet.
    However I do see within the formula of Column AE:AE25 there is a way to distinguish and separate
    the duplicate using a slight variation only if there are duplicates.

    How can I do this ?

    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Separate duplicate numbers using INDEX MATCH formula

    CORRECTION

    =IFERROR(INDEX($AD$4:$AD$24,MATCH(AF4,AC$4:AC$24,0)),"")

    All formula go to row 24

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Separate duplicate numbers using INDEX MATCH formula

    Bump & Hope ?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Separate duplicate numbers using INDEX MATCH formula

    Try ...


    =IFERROR(INDEX($AD$4:$AD$10,LARGE(IF(MATCH(AF4,AC$4:AC$10,0),ROW($AD$4:$AD$10)-3,""),ROWS($A$1:A1))),"")

    Enter with Ctrl+Shift+Enter and copy down

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Separate duplicate numbers using INDEX MATCH formula

    =IFERROR(INDEX($AD$4:$AD$24,MATCH(AF4,AC$4:AC$24,0))+countif($AF$4:$AF4;AF4)-1,"")

    and drag down
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Separate duplicate numbers using INDEX MATCH formula

    Hi

    AE4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down

    or

    AGGREGATE only work from excel 2010 onward? Won't work on excel 2003 and 2007!!
    AE4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down
    Last edited by micope21; 07-12-2015 at 05:23 AM.
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  7. #7
    Registered User
    Join Date
    03-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Separate duplicate numbers using INDEX MATCH formula

    wow,
    Thanks

    I have 2010 student edition

+ 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] How use INDEX MATCH formula across multiple excel sheets in separate folders
    By no1freeman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-11-2015, 04:22 AM
  2. [SOLVED] Sum Duplicate Index-match-match Values
    By djh30 in forum Excel General
    Replies: 3
    Last Post: 01-06-2015, 11:27 PM
  3. [SOLVED] INDEX/MATCH from Separate Workbook(s)
    By krista_o in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2014, 05:08 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. [SOLVED] INDEX and MATCH using 4 separate Criteria
    By swill008 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2013, 03:23 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