specific date look up

[superchew]

I have a worksheet and I am trying to find a function and formula to take todays date and the shift chosen, and find the current rotation dates. I have attached a sample.

Sample.xls

[Jonmo1]

Try this in A6 and filled right to C6

=LOOKUP($A$2,OFFSET(I$2:I$20,0,MATCH($B$2&"*",$I$1:$O$1,0)-1))

[tim201110]

_____________________

[superchew]

I think this is the right track. the only issue is for the mid rotation and end rotation it looks up 1 row. the information I need is all on same row, so when ever it starts the mid and end are on the same row.

[superchew]

This gives me the next rotation not the current one. the sample have has current date which should give me a start of 16 October since we are still in that rotation.

[Jonmo1]

Well you didn't really offer a very detailed explaination of what you wanted.

Anyway, try it this way
A6 and filled right.
=LOOKUP($A$2,OFFSET($I$2:I$20,0,MATCH($B$2&"*",$I$1:$O$1,0)-1))

[superchew]

That works awesome, thank you so much for your help. I really appreciate it.

[Jonmo1]

You're welcome.