Lookup based on range of dates

D

dls2193

Can someone help with the following.

I need a formula that will select the appropriate value based on a range of
dates:
I have 17 dates that are used to determine a proration based on a specific
date entered and I need to match the entered date to a range of dates and
provide the proration as the result.

Cell B1 = 07/05/05

I need the result in B2 to be .80 (I3) since it is greater than 6/30/05 but
less than 07/14/05.

D2 = 01/01/05 D3= 1
E2 = 05/19/05 E3= .96
F2 = 06/02/05 F3= .92
G2 = 06/16/05 G3= .88
H2 = 06/30/05 H3= .84
I2 = 07/14/05 I3 = .80
J2 = 07/28/05 J3= .76
K2 = 08/11/05 K3= .72
L2 = 08/25/05 L3= .68
M2 = 09/08/05 M3 = .64 etc

Thank you in advance for your assistance!
 
B

Bob Phillips

=INDEX(D3:M3,MIN(MATCH(B1,D2:M2,1)+1,COUNT(D2:M2)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top