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
Sample data below:
Sheet 1:
<tbody>
</tbody>
Sheet 2:
<tbody>
</tbody>
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:
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>