+ Reply to Thread
Results 1 to 11 of 11

Lookup values matching date range plus criteria

  1. #1
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Lookup values matching date range plus criteria

    Dear Excel-Experts,

    Needing a bit of help with a date range lookup with additional criteria problem.

    Sample file is attached with the expected outcome.

    There are two tables.
    Column A & B contain an Account number plus a start date.
    In Column C, I need the corresponding Value (column I) which matches the account number (column H) and is within the date range (columns J:K) for that account.

    Accounts can have multiple listings in either table.

    I have highlighted a few samples. if you need any clarifications etc. just let me know.

    I try to stay clear of array formula to prevent slowing down excel as the full file has a fair amount of data.

    Any help/pointers are much appreciated.
    Attached Files Attached Files
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup values matching date range plus criteria

    Please try

    C2
    =IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW(H$1:H$19)*(1/(--(H$1:H$19=A2)*--(J$1:J$19<=B2)*--(K$1:K$19>=B2))),1)),NA())


    Regards.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup values matching date range plus criteria

    Be aware that some of the dates in your lookup table are actually text!

    Non-CSE option:

    =LOOKUP(2,1/(($H$2:$H$19=A2)*($J$2:$J$19<=B2)*($K$2:$K$19>=B2)),$I$2:$I$19)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Lookup values matching date range plus criteria

    Thank you Khun menem,
    I have configured your formula into the actual date (which is over a few sheets).
    Great, fast and perfect solution. Works very well.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup values matching date range plus criteria

    Thanks for completely ignoring my suggestion, which was posted just as fast and works just as well.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Lookup values matching date range plus criteria

    Quote Originally Posted by AliGW View Post
    Thanks for completely ignoring my suggestion, which was posted just as fast and works just as well.
    a few seconds later than menem, you are number two!
    LOL
    Quang PT

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup values matching date range plus criteria

    It's fine - a lack of common courtesy simply means that I am far less likely to offer help in the future. Saying "thank you" costs you nothing.

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Lookup values matching date range plus criteria

    Thanks AliGW,
    I had already fixed the date issue in my file, but thanks for pointing it out.
    Your formula works perfectly too. Just need to adjust Account 22501 which should be M2 not 51. The issue here is that the start date should prevail over end date, hence this record should pull M2, so just adapting the '>=' to '>'.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup values matching date range plus criteria

    Next time, please acknowledge ALL contributors, regardless of how good you think their responses are. We are all giving of our free time, and you must respect that.

  10. #10
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Lookup values matching date range plus criteria

    Sorry AliGW,
    My apologies. I received menems formula tested it and replied. Then moved on to your solution, tested it and replied. I am a bit slow to adjust the formula to the actual spreadesheet and testing, but certainly do value you formula, which works great too as well.
    Thanks again to ALL who contributed.

  11. #11
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Lookup values matching date range plus criteria

    Sorry again AliGW,
    It was no judgment of the solution, I was just testing the first one, replied, testing the second one, replied. I certainly respect everyone's time and efforts. My apologies once more.

+ 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. Replies: 6
    Last Post: 12-17-2018, 05:45 PM
  2. Multiple Criteria - Within Date Range and Matching Value Within Row
    By ForProse in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2017, 05:11 PM
  3. Replies: 2
    Last Post: 01-20-2017, 04:27 PM
  4. [SOLVED] Lookup Date minus 1 day and return Values from the Matching Headings in 2 Worksheets
    By hammer2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2015, 09:07 AM
  5. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  6. LOOKUP Having one Exact matching criteria and one between 2 date ranges.
    By Grahame Hamilton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2012, 06:28 AM
  7. Count a Matching criteria to a date range
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2010, 02:25 PM

Tags for this Thread

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