Formula to see if a date falls between a certain period

[Sundance_Kid]

Hi,

I have data with a list of dates spanning 10 years.

I want to do a lookup to determine which category does the date fall under.

So I will have a table to lookup into like this

01/04/2010 - 31/03/2011 - Year 1
01/04/2011 - 31/03/2012 - Year 2

and so on, it will bring me up to Year 10 I think.

If I have a date, lets say 25/03/2012, I want the lookup to return Year 2. Also, if the date was either 01/04/2012 or 31/03/2012 I would also like it to return Year 2, so the date is between or on the two dates.

Is there a quick formula to use considering there will be around 10 potential 1 year periods a date may fall into.

Thank you

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	01/04/2010	31/03/2011	Year 1
3	01/04/2011	31/03/2012	Year 2
4
5
6
7	23/03/2011	Year 1
Data

Cell Formulas
Range	Formula
B7	B7	=XLOOKUP(A7,A2:A3,C2:C3,"",-1)

 

[Sundance_Kid]

Hey, thank you for your reply.

But the Microsoft Office I am using does not currently have the xlookup option

 

[Fluff]

In that case how about

=VLOOKUP(A7,A2:C3,3,1)

 

[Sundance_Kid]

In that case how about

=VLOOKUP(A7,A2:C3,3,1)

Thank you that worked.

How does it work if the lookup value (the date) is not evident in the table array? Is excel clever enough to look between a range of dates to decide which value to pull? Is it something to do with the 1 (approx match) at the end of the formula rather than it being a 0 (exact match) ?

 

[Fluff]

The approx match will find the exact value or next smallest.