+ Reply to Thread
Results 1 to 6 of 6

Help with an Index Match formula so that it return values in order (dates)

  1. #1
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    316

    Help with an Index Match formula so that it return values in order (dates)

    [
    Hi is it possible to add to this so that it returns the unique list in order? the column i need contains dates.

    =IFERROR(INDEX($A$2:$A$18,MATCH(0,COUNTIF($E$1:E2,$A$2:$A$18))),"")

    many thanks for looking
    Last edited by michelle 1; 06-23-2016 at 08:50 AM.

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

    Re: Help with an Index Match formula so that it return values in order (dates)

    You get better result on your question if you add a small excel file, without confidential information.

    Please also add the expected results manualy in your file.
    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.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with an Index Match formula so that it return values in order (dates)

    Yes, it's possible.

    What order do you want, ascending or descending?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    316

    Re: Help with an Index Match formula so that it return values in order (dates)

    HI tony, i am after ascending i would like the earliest to latest date.

    many thanks

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with an Index Match formula so that it return values in order (dates)

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    2
    6/12/2016
    ------
    ------
    ------
    3
    6/27/2016
    6/1/2016
    4
    6/4/2016
    6/2/2016
    5
    6/12/2016
    6/4/2016
    6
    6/2/2016
    6/12/2016
    7
    6/18/2016
    6/15/2016
    8
    6/21/2016
    6/16/2016
    9
    6/15/2016
    6/17/2016
    10
    6/18/2016
    6/18/2016
    11
    6/18/2016
    6/21/2016
    12
    6/27/2016
    6/24/2016
    13
    6/24/2016
    6/27/2016
    14
    6/1/2016
    15
    6/17/2016
    16
    6/12/2016
    17
    6/16/2016
    18
    6/4/2016


    This formula entered in E3:

    =MIN(A2:A18)

    Format as Date.

    This array formula** entered in E4:

    =IFERROR(1/(1/MIN(IF(A$2:A$18>E3,A$2:A$18))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Format as Date.

    Copy down until you get blanks.

  6. #6
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    316

    Re: Help with an Index Match formula so that it return values in order (dates)

    Hi Tony,

    That works perfectly, thankyou so muh for your help with this one.

    Michelle

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with an Index Match formula so that it return values in order (dates)

    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. INDEX/MATCH Formula issue-comparing dates in 4 columns to return a value
    By hopegriffin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-06-2016, 05:15 PM
  2. [SOLVED] Use INDEX MATCH to return data between dates and get AVERAGE of values in two colums
    By Jay S. in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2015, 08:59 PM
  3. Replies: 2
    Last Post: 05-27-2015, 12:45 AM
  4. [SOLVED] Need Help Creating INDEX/MATCH Formula to Return Values from Data Table
    By trandle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-06-2015, 01:12 PM
  5. Replies: 2
    Last Post: 02-20-2015, 05:23 PM
  6. Replies: 0
    Last Post: 07-08-2014, 09:51 AM
  7. [SOLVED] Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column
    By wfidler in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2012, 07:04 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