+ 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
    57

    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
    365
    Posts
    6,266

    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
    57

    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
    365
    Posts
    6,266

    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
    195

    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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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/Index + Re-order
    By DHFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2015, 09:53 AM
  2. [SOLVED] lookup Col and display in order
    By bnwash in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 11-12-2014, 07:41 PM
  3. Replies: 2
    Last Post: 05-19-2013, 08:46 AM
  4. problems doing a lookup because or order
    By nabilqu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2007, 12:15 PM
  5. How do I lookup a value in a array that is not in ascending order
    By Biff in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] How do I lookup a value in a array that is not in ascending order
    By John in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. How do I lookup a value in a array that is not in ascending order
    By John in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11: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