# Formula to return values based on criteria and date range

1. ## 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. ## 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. ## 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.

4. ## 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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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