Index/Match and return value when date falls within a range..Please Help!

[Maggie Barr]

Thank you in advance if you can help.
I started a new thread because I couldn’t edit my previous thread and I wanted to refine my request better.
I found an old post here on the forum that is very much like my issue:
https://www.mrexcel.com/forum/excel...ng-multiple-values-one-date-within-range.html
However, I am unable to modify the formula to my circumstances.
In sheet 1 I have four columns, Column A is a species; code, Column B is a date range, column C is the start date, and column D is the end date. In sheet 2 I have three columns, column A I have a species; code, in Column B I have a specific date, and in column C I have an observation number. I need to match Column A from both sheets and if the specific date in Column B in sheet 2 is outside the start and end dates in sheet one, have it return the observation numbers from sheet 2. I will likely have multiple matches, so I have solved this in the past with a MYVLOOKUP so it will return multiple values in one cell; however, I have never had to match things with a date range. If anyone can assist with this I would greatly appreciate it.
Thank you,
Maggie
Sample data below:

Sheet 1:
[TABLE="width: 0"]
<tbody>[TR]
[TD]Combined for Search
[/TD]
[TD]Safe Date Range
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD]Species 1; S
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 1; H
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 1; S7
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 1; M
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 1; P
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 1; T
[/TD]
[TD]1/25 - 8/1
[/TD]
[TD]25-Jan
[/TD]
[TD]1-Aug
[/TD]
[/TR]
[TR]
[TD]Species 2; S
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 2; H
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 2; S7
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 2; M
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 2; P
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 2; T
[/TD]
[TD]3/1 - 7/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Jul
[/TD]
[/TR]
[TR]
[TD]Species 3; S
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 3; H
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 3; S7
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 3; M
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 3; P
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 3; T
[/TD]
[TD]3/1 - 8/25
[/TD]
[TD]1-Mar
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Species 4; S
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 4; H
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 4; S7
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 4; M
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 4; P
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 4; T
[/TD]
[TD]3/1 - 11/1
[/TD]
[TD]1-Mar
[/TD]
[TD]1-Nov
[/TD]
[/TR]
[TR]
[TD]Species 5; S
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
[TR]
[TD]Species 5; H
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
[TR]
[TD]Species 5; S7
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
[TR]
[TD]Species 5; M
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
[TR]
[TD]Species 5; P
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
[TR]
[TD]Species 5; T
[/TD]
[TD]3/15 - 7/25
[/TD]
[TD]15-Mar
[/TD]
[TD]25-Jul
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="width: 0"]
<tbody>[TR]
[TD]Combined Name and Code
[/TD]
[TD]Month/Day
[/TD]
[TD]JUST OBS #
[/TD]
[/TR]
[TR]
[TD]Species 1; P
[/TD]
[TD]7/2
[/TD]
[TD]OBS515846307
[/TD]
[/TR]
[TR]
[TD]Species 2; S
[/TD]
[TD]7/16
[/TD]
[TD]OBS519564671
[/TD]
[/TR]
[TR]
[TD]Species 3; S
[/TD]
[TD]6/24
[/TD]
[TD]OBS513701323
[/TD]
[/TR]
[TR]
[TD]Species 4; S
[/TD]
[TD]6/28
[/TD]
[TD]OBS515074851
[/TD]
[/TR]
[TR]
[TD]Species 5; C
[/TD]
[TD]7/7
[/TD]
[TD]OBS516895128
[/TD]
[/TR]
[TR]
[TD]Species 6; C
[/TD]
[TD]7/20
[/TD]
[TD]OBS520017414
[/TD]
[/TR]
[TR]
[TD]Species 7; C
[/TD]
[TD]6/12
[/TD]
[TD]OBS510580788
[/TD]
[/TR]
[TR]
[TD]Species 8; C
[/TD]
[TD]6/20
[/TD]
[TD]OBS512629275
[/TD]
[/TR]
[TR]
[TD]Species 9; C
[/TD]
[TD]6/15
[/TD]
[TD]OBS511300253
[/TD]
[/TR]
[TR]
[TD]Species 10; S
[/TD]
[TD]7/24
[/TD]
[TD]OBS521005417
[/TD]
[/TR]
[TR]
[TD]Species 11; S
[/TD]
[TD]6/22
[/TD]
[TD]OBS513142107
[/TD]
[/TR]
[TR]
[TD]Species 12; H
[/TD]
[TD]6/8
[/TD]
[TD]OBS509643634
[/TD]
[/TR]
[TR]
[TD]Species 13; S
[/TD]
[TD]7/11
[/TD]
[TD]OBS518064152
[/TD]
[/TR]
[TR]
[TD]Species 14; M
[/TD]
[TD]7/21
[/TD]
[TD]OBS521093944
[/TD]
[/TR]
[TR]
[TD]Species 15; H
[/TD]
[TD]6/8
[/TD]
[TD]OBS509643633
[/TD]
[/TR]
[TR]
[TD]Species 16; H
[/TD]
[TD]6/8
[/TD]
[TD]OBS509641766
[/TD]
[/TR]
[TR]
[TD]Species 17; H
[/TD]
[TD]6/12
[/TD]
[TD]OBS510580786
[/TD]
[/TR]
[TR]
[TD]Species 18; H
[/TD]
[TD]6/20
[/TD]
[TD]OBS512629266
[/TD]
[/TR]
[TR]
[TD]Species 19; S
[/TD]
[TD]7/5
[/TD]
[TD]OBS516527783
[/TD]
[/TR]
[TR]
[TD]Species 20; S
[/TD]
[TD]7/24
[/TD]
[TD]OBS521005422
[/TD]
[/TR]
[TR]
[TD]Species 21; NY
[/TD]
[TD]6/11
[/TD]
[TD]OBS512920757
[/TD]
[/TR]
[TR]
[TD]Species 22; H
[/TD]
[TD]6/10
[/TD]
[TD]OBS511548393
[/TD]
[/TR]
[TR]
[TD]Species 23; F
[/TD]
[TD]7/24
[/TD]
[TD]OBS521005427
[/TD]
[/TR]
[TR]
[TD]Species 24; FY
[/TD]
[TD]7/8
[/TD]
[TD]OBS517081913
[/TD]
[/TR]
[TR]
[TD]Species 25; S
[/TD]
[TD]7/25
[/TD]
[TD]OBS521781854
[/TD]
[/TR]
[TR]
[TD]Species 26; S
[/TD]
[TD]7/27
[/TD]
[TD]OBS521484264
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming your data:
Sheet1: columns A:D; headers in row 1
Sheet2: columns A:C; headers in row 1

Try this formula in Sheet2 D2 and copy down
=IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!C:C,"<="&B2,Sheet1!D:D,">="&B2),"OK","")

If the date in column B is inside the correspondent period in Sheet1 the formula returns OK; otherwise returns an empty string.

Hope this helps

M.

 

[Maggie Barr]

Marcelo,
First and foremost, thank you for looking into this. I tried the formula, but it is putting an OK in cells for species whose observation date is within acceptable limits. I need the species names; code, in both columns a, when they match, to look at column C in sheet 2 and tell me when it is outside the date range in column C & D (or be if it can be in one column). I played with the formula a bit to see if I could reverse it, by changing the signs <= to > (I intentionally left out the =)etc., but it didn't work. If we cannot get the formula to work to flag the ones outside the date range, is there any way to have it put an #N/A when the species; code in sheet 2 does not match anything in sheet 1. My sheet 2 has many species; codes that are not in my sheet 1 because my sheet 1 is just the list of potential code errors to look for. Running your formula right now those are blank, like all the ones I want flagged. I hope this is making sense to you. Can you please tell me how to make the formula flag the species codes with observation dates outside the specified range in sheet 1? I really appreciate your help, as if I can get this running, I will not have to learn a whole new program, and I am going to have hundreds of thousands of records to check.
Again, thank you for helping.
Sincerely,
Maggie

 

[Marcelo Branco]

Hi Maggie

Thank you for the very polite reply. I really appreciated.
I'll try to help you.

Comments

Marcelo,
I tried the formula, but it is putting an OK in cells for species whose observation date is within acceptable limits.

I thought that was what you wanted.

I need the species names; code, in both columns a, when they match, to look at column C in sheet 2 and tell me when it is outside the date range in column C & D (or be if it can be in one column)

Please, try to show us the desired outcome - location of search values (sheet, columns/rows), criteria (i think this is clear) and some expected results (5 to 10 rows is enough).

M.

 

[Marcelo Branco]

By the way, to get the species names in Sheet2 whose date are outside the date range in Sheet1 we can easily adapt the formula i suggested.

Try this new formula in Sheet2 D2 and copy down
=IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!C:C,"<="&B2,Sheet1!D:D,">="&B2)=0,"Outside",""),"Species not found")

M.

 

[Maggie Barr]

Marcelo,
The key wording, and perhaps I made it confusing, is "outside the date range". Because I have so many other species; codes in sheet 2 that are not in my sheet 1 that come back blank, I cannot filter for the ones that fall outside the date range because those too, are currently blank. In the example below for sheet 2, you can see where the OK came back using your formula, what I need in the next column, or another option in the last column. Essentially it is the reverse of what your formula calculates. You can see below, in sheet 2 row 3, that for species 1; H the observation date is outside the date range for Species 1; H in sheet 1, and it is blank, but I need the formula to show an OK there instead. That being said, I need it to flag the species outside the date range on BOTH ends of the date range, so if that is too difficult, if we could somehow have the formula put an #N/A if the species; code in sheet 2 is not found in sheet 1, and continue functioning as you currently have it, then I could simply filter for the blanks. I think we are really close to getting it, and I really appreciate your help. As well, could you show me in your formula what part is doing the Column A name matches, as I am unable to decipher it clearly, and if I can see the parts of your equation better I am more apt to be able to learn the language.
Thank you,
Maggie

SO IN SHEET 1 I HAVE:

[TABLE="width: 376"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Combined for Search[/TD]
[TD]Safe Date Range[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Species 1; S[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
[TR]
[TD]Species 1; S7[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
[TR]
[TD]Species 1; M[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
[TR]
[TD]Species 1; P[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
[TR]
[TD]Species 1; T[/TD]
[TD]6/10 - 8/5[/TD]
[TD="align: right"]10-Jun[/TD]
[TD="align: right"]5-Aug[/TD]
[/TR]
</tbody>[/TABLE]

AND IN SHEET 2 I GET:

[TABLE="width: 683"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Combined Name; Code[/TD]
[TD]Month-Day[/TD]
[TD]JUST OBS #[/TD]
[TD]Your formula[/TD]
[TD]What I need[/TD]
[TD]Another option[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]20-Jun[/TD]
[TD]OBS515755667[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]18-Jun[/TD]
[TD]OBS509395661[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]2-Jun[/TD]
[TD]OBS515816397[/TD]
[TD][/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]21-Jun[/TD]
[TD]OBS515809456[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]7-Jun[/TD]
[TD]OBS511605418[/TD]
[TD][/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]2-Jun[/TD]
[TD]OBS512397535[/TD]
[TD][/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]21-Jun[/TD]
[TD]OBS512988307[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]6-Jun[/TD]
[TD]OBS513696468[/TD]
[TD][/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]20-Jun[/TD]
[TD]OBS515815724[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]7-Jun[/TD]
[TD]OBS513693272[/TD]
[TD][/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]21-Jun[/TD]
[TD]OBS510650034[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]21-Jun[/TD]
[TD]OBS513431465[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]16-Jun[/TD]
[TD]OBS520264053[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]18-Jun[/TD]
[TD]OBS519977767[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; H[/TD]
[TD]21-Jun[/TD]
[TD]OBS518741381[/TD]
[TD]OK[/TD]
[TD][/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Species 1; Z[/TD]
[TD]2-Jun[/TD]
[TD]OBS512988301[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Species 1; Z[/TD]
[TD]21-Jun[/TD]
[TD]OBS514934927[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Species 1; Z[/TD]
[TD]7-Jun[/TD]
[TD]OBS510602341[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Species 1; T[/TD]
[TD]2-Jun[/TD]
[TD]OBS507979862[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Species 1; T[/TD]
[TD]21-Jun[/TD]
[TD]OBS516527787[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Species 1; T[/TD]
[TD]6-Jun[/TD]
[TD]OBS519491708[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Maggie Barr]

Marcelo!!!!
Here I was typing away and I didn't see your most recent post. THAT WORKED PERFECTLY! Thank you so very much, you are an angel! I am going to try to sort out your formula and what each part of it is doing so that I can actually learn what it is doing and perhaps someday be clever enough to write it myself, but in the mean time, you were a god send.
Bless you, and thank you!
Best Wishes,
Maggie

 

[Marcelo Branco]

Great news!

You are very welcome. Glad to help

M.