Between Two Dates

[HollyAnn73]

I am trying to send a value from column B to a column based on the date in Column A. Example:

A B C D E F
05/01 05/08 5/15 5/22

05/03/11 $100.00 100.00
05/17/11 200.00 200.00


Basically, I want to ask it to compare the dates in the top row with the dates in column A and place the value of column B in the column that represents the lowest of the date range between the two. So for example, if A is between C1 and D1 then place B in C and so on. I tried with an "If" and a "lookup" with no avail. Any ideas or am I doing something wrong?

Any help is appreciated. Thank you!

 

[HollyAnn73]

I see the message didn't keep my spaces. In the second row of data, $200 would be in Column D.

 

[texasalynn]

is this what you mean?

Excel Workbook
	A	B	C	D
1	5/1/2011	5/8/2011	5/15/2011	5/22/2011
2
3	5/3/2011	$ 100.00	$0.00	$ 100.00
4	5/17/2011	$ 200.00	$ 200.00	$ 200.00
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C3	=IF(AND($C$1<=A3,$D$1>=A3),B3,0)