If (AND) or (OR) statement that returns a value between dates

[southwoman]

`

A B C D E

1 1 Apr 11 31 Mar 12 75%

2 1 Apr 12 23 Jul 12 0%

3 24 Jul 12 31 Mar 13 50%

4 1 Apr 13 31 Mar 14 50%

5 1 Apr 14 31 Mar 15 50%


Hello all,


I have the above data in an excel sheet. What I am trying to acheive is a statement that will look at a date in Column D (for example (22 Jun 12) )and return the correct % in column E.


Can you assist?


Many Thanks


N

 

[Faseeh]

Hi southwomen,


Use

=VLOOKUP($F$1,D2:E8,2,FALSE)

with F1 containing Lookup Date and D2:E8 contain Lookup Table.


Regards,

Faseeh

 

[southwoman]

Hi Faseeh,


Thanks for the reply. Am not sure am following the Vlookup. I thought of doing one but its still does not help me to find a find the % that matches the data that is between two dates. for example


If I have a date that is between 1 April 11 and 31 MArch 12 the % returned should be 75%

If the date is between 1 April 13 and 31 March 14 then the % returned should be 50%


I have assumed that an IF OR statement would have worked but am not sure how to write this to include all the parameters of date values and %


Can you help?

 

[Luke M]

I think this will do the trick:

=SUMPRODUCT(--($A$2:$A$10<=F1),--($B$2:$B$10>=F1),$C$2:$C$10)


Returns the value from column C where col A is <= F1 and col B is >= F1. Assumption is that there is only 1 row that meets the latter 2 criteria (which looks to be the case).

 

[Faseeh]

Hi southwoman,


I have checked and it is working. Here is your data in A1:B5, Lookup Date in E1, enter this formula any where:

[pre]

1-Apr-11	31-Mar-12	25%
1-Apr-12	23-Jul-12	30%
24-Jul-12	31-Mar-13	35%
1-Apr-13	31-Mar-14	45%
1-Apr-14	31-Mar-15	55%[/pre]
=VLOOKUP(E1,A1:C5,3)

I have changed percentage values so that you can see the change.


Regards.

 

[Luke M]

After seeing Faseeh's layout, I'd recommend using his. I over-complicated things. =/

 

[shrivallabha]

Some more options would be (With Faseeh's layout)


=LOOKUP(E1,A1:A5,C1:C5)


=INDEX(C1:C5,MATCH(E1,A1:A5))


You can obviously write a NESTED IF but it'd be very difficult to manage / change.