Look Up Fiscal Year and Period from Table

**bberks** · 09-20-2010, 11:57 AM

I want to have a fiscal year and period returned when I enter a calender date.

I have a table with calender start and end dates that correlate to the fiscal year and period.

See attached worksheet.

Thank you ahead of time for your help.

BB

**BigBas** · 09-21-2010, 12:17 AM

Try this and report back.

I entered a date into cell E2. Next, I entered this formula for fiscal year:

=LOOKUP(E2,$A$2:$B$61,$C$2:$C$61)

and this formula for period

=LOOKUP(E2,$A$2:$B$61,$D$2:$D$61)

Let me know if that worked

**bberks** · 09-21-2010, 09:51 AM

This works. I am not sure why. But, it does work fine.

What I can't figure out is how excel knows that the lookup date E2 must be equal, greater and less than the two dates that correlate to fiscal year and period in the lookup table.

Thank you,

BB

**daddylonglegs** · 09-21-2010, 09:58 AM

The formula doesn't use column B at all; you would normally write the first one like this:

=LOOKUP(E2,$A$2:$A$61,$C$2:$C$61)

Lookup finds the greatest value in A2:A61 that's also less than or equal to E2.....and returns then corresponding value in C2:C61.....so it wouldn't be appropriate for situations where there are gaps in the ranges because it doesn't check that the date is also below the end of the range in column B........but I assume you have no gaps

You can also write the formula like this

=LOOKUP(E2,$A$2:$C$61)