Lookup a value from a range of dates in excel

[janilee cantillas]

I have two tables. A list of accounts and the other is a change log. I need to add a new column in table 1 where the value is the amount in table 2 for the correct account and correct validity period.

Please help

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D
1	Account	Start	End	Amount
2	1	January 1, 2012	January 31, 2012	30
3	2	January 12, 2012	February 12, 2012	#N/A
4
5	Account	Amount	Start	End
6	1	10	January 1, 2009	December 5, 2010
7	1	20	December 6, 2010	June 1, 2011
8	1	30	June 2, 2011	December 1, 2012
9	2	13	January 15, 2011	December 15, 2011
10	2	20	December 16, 2011	February 10, 2012
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX(B$6:B$10,MATCH(1,INDEX((A$6:A$10=A2)*(C$6:C$10<=B2)*(D$6:D$10>=C2),),FALSE))
D3	=INDEX(B$6:B$10,MATCH(1,INDEX((A$6:A$10=A3)*(C$6:C$10<=B3)*(D$6:D$10>=C3),),FALSE))

 

[janilee cantillas]

Hi Andrew,

Thanks for this. Not sure why I can't match some of the formula...date is within the correct range..may i know what 1 in (match(1, index...) means? thanks.

 

[Andrew Poulsom]

Can you give some examples that don't work as expected? Multiplying booleans returns 1 or 0. Match returns the relative position of the first 1.