+ Reply to Thread
Results 1 to 4 of 4

Formula to return values based on criteria and date range

  1. #1
    Registered User
    Join Date
    11-26-2014
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    2

    Formula to return values based on criteria and date range

    Hello,

    I've been experimenting and trying to google an answer for this, but I haven't had much luck.

    I'm trying to find a formula (if it's even possible) that will look up and return values based on specific criteria and date range.

    I have the following: E3 is my "Associate field", E5 & E6 are my start and end dates.

    I have a second sheet (Errors) with columns "Associate", "Date", "Reference", "Comments".

    What I would like is when someone enters a person's name in the first sheet with a start and end date, a list of valuse will be returned on the same sheet giving the Reference and Comments from my Errors sheet. So something like this:

    E3 = Test, Test
    E5 = 11/1/2014
    E6 = 11/25/2014

    then below would display all reference and comments pertaining to that name and date range.

    Is this possible?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula to return values based on criteria and date range

    It can be done, but it will be easier with more than one formula. Please attach a sample workbook (the FAQ describes how to).

    Pete

  3. #3
    Registered User
    Join Date
    11-26-2014
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    2

    Re: Formula to return values based on criteria and date range

    Hello,

    I hope I attached it correctly. I can't send my actual spreadsheet with the data I have unfortunately because of the confidential information on it. However, I did rework what I had into "test" data with the same purpose.

    In my scenario, however, there will be more than one "Associate" name other than Test, Test.

    I want a section (that I will create on the "Main" sheet) where it will look up the reference number on Sheet2 by the associate name and only return values based on the specified dates entered in E5 and E6.

    So, for my attachment, it should pull #s 1234567 and 111222333 and list them.

    There will be a lot of data that it will be searching through with 70+ possible names.

    I'm not sure if this is possible in Excel. Any help is appreciated.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula to return values based on criteria and date range

    Put this formula in J2 of Sheet2:

    =IF(AND(D2=Main!$E$3,B2>=Main!$E$5,B2<=Main!$E$6),MAX(J$1:J1)+1,"-")

    and copy down beyond your data to allow for new data being added.

    Then put this formula in C24 of the Main sheet (or wherever you want the reference numbers to appear):

    =IFERROR(INDEX(Sheet2!F:F,MATCH(ROWS($1:1),Sheet2!$J:$J,0)),"")

    Copy this into D24, then copy C24:D24 down as far as you need to. The attached file shows this in operation.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ 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. Return several values based upon date range
    By tsmor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2014, 08:47 PM
  2. Array formula to return multiple values based on various (OR) criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2013, 05:12 AM
  3. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  4. [SOLVED] Formula to Return Bottom 3 Values From a Table Based on a Criteria
    By JungleJme in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-15-2012, 06:57 AM
  5. [SOLVED] Return all values based on a date range
    By stupidname in forum Excel General
    Replies: 3
    Last Post: 06-04-2012, 08:49 AM

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