Check if date falls b/twn start date in one array and end date in 2nd arr & return value

1. Check if date falls b/twn start date in one array and end date in 2nd arr & return value

Hi All,

Does anyone ever get to that point where you're working on Excel for so long that you forget how to put together a simple formula? Well, I think I'm at that point.

With reference to the attached, I have a string of periods. Each period has a start date and end date. The start date of the second period is the day after the end date of the first period and so on. Each period has a value assigned to it.

What I need is to create a formula in cell G4 that checks which of the periods that the date in cell F4 falls into, then returns the value for that period. I'm getting Excel block here; I know how to do this using long code, but I was hoping to create a very short code that basically references columns as opposed to a cell range i.e. date in F4 must be greater or equal to date in column B and also less than or equal to date (on the same row) in column C.

2. Re: Check if date falls b/twn start date in one array and end date in 2nd arr & return val

Try

=INDEX(D\$4:D\$10,MATCH(F4,B\$4:B\$10,1),1)

3. Re: Check if date falls b/twn start date in one array and end date in 2nd arr & return val

=lookup(f4,b4:b10,d4:d10)
since your end date+1 is start of next row the above formula will work for you

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