select date in date range

[adam.doe]

I want to be able to do the following:


A B
1 26/03/2006 $20.00
2 1/12/2006 $40.00
3 1/10/2007 $100.00
4
5
6
7 28/11/2006 2
8 29/11/2006 2

I need a formula that says if the date in A7 is between date in A1 to
A2, then muliply B7 by B1, else if the date in A7 is between date in
A2 to A3, then multiply B7 by B2, and so on.

 

[Max]

Assuming the dates in A1:A3 are in chrono order (as posted)
Put in C1: =IF(A2="",A1,A2)
Copy C1 down to C3

Then place this in C7's formula bar, press CTRL+SHIFT+ENTER (CSE) to confirm
the formula, instead of just pressing ENTER:
=IF(A7="","",INDEX($B$1:$B$3,MATCH(1,($A$1:$A$3<=A7)*($C$1:$C$3>=A7),0)))
Copy C7 down to return required results

Adapt the above to suit your actual extents

 

[Max]

Just to add on a little more .. The formula suggested in C7 is essentially
an array formula which needs to be array-entered via CSE.

If you did the CSE confirmation correctly, you should see Excel wrap curly
braces: { } around the formula in the formula bar (ie in C7's formula bar).
If you don't see it (the curly braces), click inside the formula bar and try
the CSE confirmation again.

The visual check on the curly braces is the *only way* (afaik) to check that
the formula is correctly array-entered. If the formula is NOT array-entered,
of course it won't return correctly.

 

[Teethless mama]

=VLOOKUP(A7,$A$1:$B$3,2)*B7

 

[adhide]

Thanks, just trying to wrap my mind around the formula.

I need to mutiply the number in B7 with the date result returned in either
B1,B2,B3

IE

if A7 isbetween $A$1:$A$2
then B7*$B$1, else
if A7 isbetween $A$2:$A$3
then B7*$B$2 else
if A7 isbetween $A$3:NOW()
then A7*$B$3, else,""

I know these arent the Excel formula codes but my SQL gets in the way.

 

[adhide]

Or....I could use a simple vlookup table...thansk

 

[Max]

Errata, formula in C7 should be:
=IF(A7="","",INDEX($B$1:$B$3,MATCH(1,($A$1:$A$3<=A7)*($C$1:$C$3>=A7),0)))*B7
(array-entered)

Forgot you wanted to multiply it by the value in B7

P/s: There's a subtle difference between TM's vlookup & the array above. If
you have repeated reference dates in A1 down, and the lookup date happens to
coincide with that ..

 

[Max]

Wasn't sure if you were the original poster??
For info, I've posted a correction to the formula in C7
(yes, I forgot you wanted to multiply it by the value in B7)