+ Reply to Thread
Results 1 to 11 of 11

Lookup values matching date range plus criteria

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

    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
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    427

    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.
    My English is very poor, so please be patient >_<"

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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 2007
    Posts
    5,439

    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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    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
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    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