I have start date (E2) in table 1. Table 2 has a start date,an end date, and a budget number.

StartDate EndDate BudgetNo
9/1/09 2/28/10 1
3/1/10 8/31/10 2
9/1/10 2/28/11 3

My formula needs to show that if Table1!E2 falls between these certain dates than it will provide the corresponding budget no.

I dont know if I am explaining this correctly or even if it am going about this properly. If anyone understands please HELP!

Try something like:

=LOOKUP(E2,$X$2:$Z$4)

where X2:Z4 contains the table 2 with the first column being the lower bound of the dates and in ascending order...

Another way is with (especially if there are gaps in sequential date ranges): =SUMPRODUCT(--($X$2:$X$4<=E2),--($Y$2:$Y$4>=E2),$Z$2:$Z$4)

The SUBPRODUCT worked perfectly! Thanks!