+ Reply to Thread
Results 1 to 7 of 7

Lookup and order

  1. #1
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    53

    Lookup and order

    Hi,

    I need to lookup and order dates from earliest till latest based on a company name and a month number.
    please see attachment
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Lookup and order

    Try this in I6:

    =IFERROR(SMALL(IF(I$3&J$3=D$3:D$16&B$3:B$16,C$3:C$16),ROWS($1:1)),"") Ctrl Shift Enter

  3. #3
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    53

    Re: Lookup and order

    Hi,

    this formula doesn't work
    i got a error: too many arguments in this if function.
    have you tried it and if possible can you share with me the file

  4. #4
    Registered User
    Join Date
    10-11-2018
    Location
    Gurgaon
    MS-Off Ver
    XP
    Posts
    10

    Re: Lookup and order

    I also tried this one but not get the result.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Lookup and order

    this formula doesn't work
    I guess you mean that you can't get it to work on your end. It works fine on my end.

    Try this instead:
    =IFERROR(SMALL(IF((D$3:D$16=I$3)*(B$3:B$16=J$3),C$3:C$16),ROWS($1:1)),"") Ctrl Shift Enter

    I have inputted both formulas into the sample that you shared in post #1 and attached it here for you to see.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    04-30-2015
    Location
    The Netherlands
    MS-Off Ver
    office 365
    Posts
    122

    Re: Lookup and order

    The formula works fine for me.

    double check if your seperation key is a comma or a semicolon

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


    And make sure you enter it as an Array Formula, that means that you need to confirm it with CTRL+SHIFT+ENTER, instead of just enter.

    You will see if this worked when you see these "{" "}" brackets around the formula. (adding these manually doesn't work)
    Remember to mark as Solved and give out rep.

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,084

    Re: Lookup and order

    Here's a non-array (just enter...) alternative:

    =IFERROR(AGGREGATE(15,6,$C$3:$C$16/(($B$3:$B$16=$J$3)*($D$3:$D$16=$I$3)),ROWS($1:1)),"")

    Please amend your location to show us,in general terms, where you are in the world. lulu may mean Honolulu, but might also mean....????
    Glenn



+ 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