Lookup a value from a range of dates in excel

janilee cantillas

New Member
Joined
Jun 29, 2013
Messages
2
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
eK8qH.gif
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Cell Formulas
RangeFormula
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

New Member
Joined
Jun 29, 2013
Messages
2
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,171,705
Messages
5,877,034
Members
433,228
Latest member
Sarah1989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top