JonRowland
Active Member
- Joined
- May 9, 2003
- Messages
- 417
- Office Version
- 365
- Platform
- Windows
Hi,
I would have previously done this use and IF and AND function as only had 2 or 3 periods to check but I now have a lot. So here is my question.
I have a long list and what to find what period the date falls in against in a list containing Start and End dates and then return the value if date falls in that range.
Worksheet One
I have in Col U a date as dd/mm/yyyy hh:mm:ss. I want to check this against worksheet 2 and if falls in the date range return the value in Col C, similar to a X or V LOOKUP
So if U2 is greater or equal to Worksheet2.A1 AND less than or equal to Worksheet 2.B1 value WORKSHEET2.C1 is returned
For example
Worksheet Two
All ranges are dynamic.
I'd prefer a formula rather than VBA but not discounting VBA. I did think I could write a loop but would have to run through each series individually (I think).
Any guidance as always greatly received and hope I'm understandable.
I would have previously done this use and IF and AND function as only had 2 or 3 periods to check but I now have a lot. So here is my question.
I have a long list and what to find what period the date falls in against in a list containing Start and End dates and then return the value if date falls in that range.
Worksheet One
I have in Col U a date as dd/mm/yyyy hh:mm:ss. I want to check this against worksheet 2 and if falls in the date range return the value in Col C, similar to a X or V LOOKUP
So if U2 is greater or equal to Worksheet2.A1 AND less than or equal to Worksheet 2.B1 value WORKSHEET2.C1 is returned
For example
01:01:23 01/02/2022 | P1 |
23:59:01 02/01/2022 | No |
11:11:01 07/01/2022 | P2 |
Worksheet Two
00:01:00 01/02/2022 | 23:59:00 02/01/2022 | P1 |
23:10:40 06/01/2022 | 11:11:01 07/01/2022 | P2 |
13:22:01 08/02/2022 | 16:00 09/02/2022 | P3 |
All ranges are dynamic.
I'd prefer a formula rather than VBA but not discounting VBA. I did think I could write a loop but would have to run through each series individually (I think).
Any guidance as always greatly received and hope I'm understandable.