Thread: Lookup a value from a range of dates in excel Thanks: 0 Likes: 0

1. Lookup a value from a range of dates in excel

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.   Reply With Quote

2. Re: Lookup a value from a range of dates in excel

Example:

Excel 2010
ABCD
1AccountStartEndAmount
21January 1, 2012January 31, 201230
32January 12, 2012February 12, 2012#N/A
4
5AccountAmountStartEnd
6110January 1, 2009December 5, 2010
7120December 6, 2010June 1, 2011
8130June 2, 2011December 1, 2012
9213January 15, 2011December 15, 2011
10220December 16, 2011February 10, 2012

Sheet1

Worksheet Formulas
CellFormula
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))  Reply With Quote

3. Re: Lookup a value from a range of dates in excel

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.  Reply With Quote

4. Re: Lookup a value from a range of dates in excel

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.  Reply With Quote

User Tag List

Tags for this Thread

excel .net, excel 07, vba & excel  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•