+ Reply to Thread
Results 1 to 2 of 2

Two Date Range Lookup based on Index Formula

  1. #1
    Registered User
    Join Date
    02-03-2015
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    18

    Two Date Range Lookup based on Index Formula

    I'm currently working on this date range formula to capture a project name based on the date range. The formula works but only to a certain extent; I have 3 projects in July of 2021 and this formula gives me the 3 projects and then additional projects from other months.

    I should only have 3 project totals, not a whole list of projects or at times only one project for July. The end date is not a concern as the formula is only to capture Column W as the start date for the project.

    Project List:
    Column H Column W Column Y
    M2 7/21/2021 9/10/2021
    M4 7/23/2021 9/10/2021
    WC1 7/28/2021 9/1/2021

    Here is my formula:

    =IFERROR(INDEX('Project Name 21-22'!$H$3:$H$313,AGGREGATE(15,6,ROW('Project Name 21-22'!$W$3:$W$313)/(('Project Name 21-22'!$W$3:$W$313>=$F$3)+('Project Name 21-22'!$W$3:$W$313<=$H$3)*('Project Name 21-22'!$W$3:$W$313=$Z$5)+('Project Name 21-22'!$W$3:$W$313<=$H$3)*('Project Name 21-22'!$W$3:$W$313>"")>0),ROWS(Z$5:Z5))),"")

    Dates are located in Cell F3 for 6/1/2021, H3 for 7/1/2021, and J3 for 8/1/2021

    I can't figure out why this formula won't display only the 3 projects for July 2021 based on July 1 beginning date and Aug 1 as the end date.

    Thanks!!!!

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,444

    Re: Two Date Range Lookup based on Index Formula

    See the yellow banner on how to add a sample sheet

+ 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] INDEX MATCH based on Date Range and Name
    By treegee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2019, 11:58 AM
  2. Replies: 6
    Last Post: 04-01-2019, 11:08 AM
  3. Add a date lookup to index Match formula
    By Just_Hooch in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-16-2018, 09:37 AM
  4. INDEX'ing Lookup Values Using a Date Range
    By hansonc01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2017, 05:22 PM
  5. lookup value based on date range
    By shcsbaker in forum Excel General
    Replies: 4
    Last Post: 06-11-2014, 01:01 PM
  6. [SOLVED] Index Match Based on date range and name criteria
    By darkhangelsk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-02-2014, 11:33 PM
  7. [SOLVED] Formula to Lookup value based on date within a date range
    By MHamid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2013, 02:24 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