help with lookup value between dates range

[stzruya]

Hi,
I need to lookup value in table and return value based on date range.
my table look like that:

[TABLE="width: 320"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Number[/TD]
[TD="align: left"]Date1[/TD]
[TD="align: left"]Date2[/TD]
[TD="align: left"] Model[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]02/12/2003[/TD]
[TD="align: right"]29/07/2005 [/TD]
[TD="align: left"] A[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]29/07/2005[/TD]
[TD="align: right"]23/11/2009[/TD]
[TD="align: left"] B[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]23/11/2009[/TD]
[TD="align: right"]11/11/2010[/TD]
[TD="align: left"] C[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]11/11/2010[/TD]
[TD="align: right"]19/12/2010[/TD]
[TD="align: left"] D[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]19/12/2010[/TD]
[TD="align: right"]25/04/2013[/TD]
[TD="align: left"] E[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]25/04/2013[/TD]
[TD="align: right"]24/03/2015[/TD]
[TD="align: left"] F[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]24/03/2015[/TD]
[TD="align: right"]28/12/2050[/TD]
[TD="align: left"] G[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]02/12/2003[/TD]
[TD="align: right"]29/07/2005[/TD]
[TD="align: left"] A[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]29/07/2005[/TD]
[TD="align: right"]23/12/2009[/TD]
[TD="align: left"] B[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]23/12/2009[/TD]
[TD="align: right"]02/11/2010[/TD]
[TD="align: left"] C[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]02/11/2010[/TD]
[TD="align: right"]19/12/2010[/TD]
[TD="align: left"] D[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]19/12/2010[/TD]
[TD="align: right"]21/03/2013[/TD]
[TD="align: left"] E[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]21/03/2013[/TD]
[TD="align: right"]24/03/2015[/TD]
[TD="align: left"] F[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]24/03/2015[/TD]
[TD="align: right"]20/05/2015[/TD]
[TD="align: left"] G[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]20/05/2015[/TD]
[TD="align: right"]26/05/2015[/TD]
[TD="align: left"] H[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]26/05/2015[/TD]
[TD="align: right"]28/12/2050[/TD]
[TD="align: left"] J[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
In other sheet i have list of numbers such as the numbers above (in column A) with specific date, based on that i need to return the model that was between that time.
for example for number 110 in the date 1/1/2010 the model was C.

is there any function that can do that or even macro that can help in that case.

thank you in advance

Samuel

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Number	Date1	Date2	Model
2	109	12/2/2003	7/29/2005	A
3	109	7/29/2005	11/23/2009	B			110	1/1/2010	C
4	109	11/23/2009	11/11/2010	C
5	109	11/11/2010	12/19/2010	D
6	109	12/19/2010	4/25/2013	E
7	109	4/25/2013	3/24/2015	F
8	109	3/24/2015	12/28/2050	G
9	110	12/2/2003	7/29/2005	A
10	110	7/29/2005	12/23/2009	B
11	110	12/23/2009	11/2/2010	C
12	110	11/2/2010	12/19/2010	D
13	110	12/19/2010	3/21/2013	E
14	110	3/21/2013	3/24/2015	F
15	110	3/24/2015	5/20/2015	G
16	110	5/20/2015	5/26/2015	H
17	110	5/26/2015	12/28/2050	J
Sheet1

Cell Formulas
Range	Formula
I3	{=INDEX($D$2:$D$17,MATCH(G3,IF($B$2:$B$17<=H3,IF($C$2:$C$17>=H3,$A$2:$A$17))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[stzruya]

Works like a charm

thank you so much

Samuel