Check value against multiple ranges

[X-Ray]

I have a date and want to know in what period this is.

Reference ranges:
period begin end
jan 1/1/2008 14/1/2008
feb 15/1/2008 14/2/2008
mrt 15/2/2008 14/3/2008
apr 15/3/2008 14/4/2008

test value: result:
12/3/2008 mrt
1/4/2008 apr

I can do this with an nested IF function, but then I´m limited to 6 nested
IF statements. And I need more, in this case 7 or 8.

Is there some case statement like worksheet function ?

 

[Mike H]

Hi,

Somewhere out of the way build a table that looks like this. Mine is in E1 -
F4

01/01/2008 Jan
15/01/2008 Feb
15/02/2008 Mar
15/03/2008 Apr

I've only done a part table because I don't understand the logic of it but
you would need to build a full one and the left column must be sorted.

With your date in A1 use this formula
=VLOOKUP(A1,E1:F4,2,TRUE)

Mike

 

[Ashish Mathur]

Hi,

Assume the data is in range A5:C9 and 12/3/2008 and 1/4/2008 are in cells
B17 and B18. In D69, enter 1-4. In cell C17, enter the following formula

CHOOSE(SUMPRODUCT(($B$6:$B$9<=$B17)*($C$6:$C$9>=$B17)*($D$6:$D$10)),A$6,A$7,A$8,A$9)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

=MyDate-14
Formatted as [$-409]mmm;@