Date formula to find value

[Forestq]

Hi,

I have below table1 (sheet1):

StartDate(A1) <tbody> </tbody>	EndDate(B1) <tbody> </tbody>	Month(C1)	Quater(D1)	Year(E1)
29/07/2012 <tbody> </tbody>	25/08/2012 <tbody> </tbody>	1	Q1	2013 <tbody> </tbody>
26/08/2012 <tbody> </tbody>	22/09/2012 <tbody> </tbody>	2	Q1	2013 <tbody> </tbody>
23/09/2012 <tbody> </tbody>	27/10/2012 <tbody> </tbody>	3	Q1	2013 <tbody> </tbody>
28/10/2012 <tbody> </tbody>	24/11/2012 <tbody> </tbody>	4	Q2	2013 <tbody> </tbody>
25/11/2012 <tbody> </tbody>	22/12/2012 <tbody> </tbody>	5	Q2	2013 <tbody> </tbody>
23/12/2012 <tbody> </tbody>	26/01/2013 <tbody> </tbody>	6	Q2	2013 <tbody> </tbody>
27/01/2013 <tbody> </tbody>	23/02/2013 <tbody> </tbody>	7	Q3	2013 <tbody> </tbody>
24/02/2013 <tbody> </tbody>	23/03/2013 <tbody> </tbody>	8	Q3	2013 <tbody> </tbody>
..	..	..	..	..

<tbody>
</tbody>

Table is filled in this way till 2017.
Now into tab "sheet2" into col A, I have dates:

Dates(A1)	Month (B1)
24/08/2012	1
25/09/2012	3
20/12/2012	5
01/11/2013	6
03/10/2012	3

<tbody>
</tbody>

How to add month value based on table1?

I don't want to do a lot of if....is other way? How to write simple function to fill month based on table1?

 

[Jonmo1]

Try LOOKUP

=LOOKUP(Sheet2!A2,Sheet1!A$2:C$9)

 

[Forestq]

but the date is between two dates...

 

[Jonmo1]

but the date is between two dates...

I know.
Try it.

 

[Forestq]

seems to works, thx Jonmo1!

 

[Jonmo1]

You're welcome.

The only issues you might have with it is when the given date is before the earliest date in column A (29/07/2012), or after the latest date in column B (23/03/2013)

Here's an ammendment to handle those issues.
=IF(Sheet2!A2>Sheet1!B$9,"Date is later",IFERROR(LOOKUP(Sheet2!A2,Sheet1!A$2:C$9),"Date is earlier"))

 

[Forestq]

ok, thanks

I did:
=IFERROR(LOOKUP(Sheet2!A2,Sheet1!A$2:C$61),"")

but i think my function not eliminate issue as your code.

 

[Jonmo1]

The iferror will only eliminate the error when Sheet2!A2 is before the earliest date (Sheet1!A2)

And you've expanded it to 60 rows.
Is the pattern still the same, and are there any blank rows in that 60 rows?
Blanks in Sheet1 column A can cause problems.

 

[Forestq]

blank rows stars from 62

 

[Jonmo1]

Should be fine then

=IF(Sheet2!A2>Sheet1!B$61,"",IFERROR(LOOKUP(Sheet2!A2,Sheet1!A$2:C$61),""))