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

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
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:eek:
Sample data below:

Sheet 1:
Combined for Search
Safe Date Range
Start Date
End Date
Species 1; S
1/25 - 8/1
25-Jan
1-Aug
Species 1; H
1/25 - 8/1
25-Jan
1-Aug
Species 1; S7
1/25 - 8/1
25-Jan
1-Aug
Species 1; M
1/25 - 8/1
25-Jan
1-Aug
Species 1; P
1/25 - 8/1
25-Jan
1-Aug
Species 1; T
1/25 - 8/1
25-Jan
1-Aug
Species 2; S
3/1 - 7/1
1-Mar
1-Jul
Species 2; H
3/1 - 7/1
1-Mar
1-Jul
Species 2; S7
3/1 - 7/1
1-Mar
1-Jul
Species 2; M
3/1 - 7/1
1-Mar
1-Jul
Species 2; P
3/1 - 7/1
1-Mar
1-Jul
Species 2; T
3/1 - 7/1
1-Mar
1-Jul
Species 3; S
3/1 - 8/25
1-Mar
25-Aug
Species 3; H
3/1 - 8/25
1-Mar
25-Aug
Species 3; S7
3/1 - 8/25
1-Mar
25-Aug
Species 3; M
3/1 - 8/25
1-Mar
25-Aug
Species 3; P
3/1 - 8/25
1-Mar
25-Aug
Species 3; T
3/1 - 8/25
1-Mar
25-Aug
Species 4; S
3/1 - 11/1
1-Mar
1-Nov
Species 4; H
3/1 - 11/1
1-Mar
1-Nov
Species 4; S7
3/1 - 11/1
1-Mar
1-Nov
Species 4; M
3/1 - 11/1
1-Mar
1-Nov
Species 4; P
3/1 - 11/1
1-Mar
1-Nov
Species 4; T
3/1 - 11/1
1-Mar
1-Nov
Species 5; S
3/15 - 7/25
15-Mar
25-Jul
Species 5; H
3/15 - 7/25
15-Mar
25-Jul
Species 5; S7
3/15 - 7/25
15-Mar
25-Jul
Species 5; M
3/15 - 7/25
15-Mar
25-Jul
Species 5; P
3/15 - 7/25
15-Mar
25-Jul
Species 5; T
3/15 - 7/25
15-Mar
25-Jul

<tbody>
</tbody>

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

<tbody>
</tbody>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

Combined for SearchSafe Date RangeStart DateEnd Date
Species 1; S6/10 - 8/510-Jun5-Aug
Species 1; H6/10 - 8/510-Jun5-Aug
Species 1; S76/10 - 8/510-Jun5-Aug
Species 1; M6/10 - 8/510-Jun5-Aug
Species 1; P6/10 - 8/510-Jun5-Aug
Species 1; T6/10 - 8/510-Jun5-Aug

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

AND IN SHEET 2 I GET:

Combined Name; CodeMonth-DayJUST OBS #Your formulaWhat I needAnother option
Species 1; H20-JunOBS515755667OKOK
Species 1; H18-JunOBS509395661OKOK
Species 1; H2-JunOBS515816397OK
Species 1; H21-JunOBS515809456OKOK
Species 1; H7-JunOBS511605418OK
Species 1; H2-JunOBS512397535OK
Species 1; H21-JunOBS512988307OKOK
Species 1; H6-JunOBS513696468OK
Species 1; H20-JunOBS515815724OKOK
Species 1; H7-JunOBS513693272OK
Species 1; H21-JunOBS510650034OKOK
Species 1; H21-JunOBS513431465OKOK
Species 1; H16-JunOBS520264053OKOK
Species 1; H18-JunOBS519977767OKOK
Species 1; H21-JunOBS518741381OKOK
Species 1; Z2-JunOBS512988301#N/A
Species 1; Z21-JunOBS514934927#N/A
Species 1; Z7-JunOBS510602341#N/A
Species 1; T2-JunOBS507979862#N/A
Species 1; T21-JunOBS516527787#N/A
Species 1; T6-JunOBS519491708#N/A

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top