LookUp Help

[mtndewgradon]

I have a spreadsheet that has up to 200 facilities and each facility will be listed up to 6 times. The sheet has current month and the last date of the previous month. I need to do a look up to pull the previous months total to be on the current months row.

Example
Month Current Previous
1/31/2012 $100
2/29/2012 $500 $100

Can this be done if so how

[Ron Coderre]

With your posted example in A1:C3
This regular formula returns the Col_B value where the Col_A date equals the last date of the month prior to the current row's date
C3: =INDEX(B$1:B2,MATCH(EOMONTH(A3,-1),A$1:A2,0))

Is that something you can work with?

[mtndewgradon]

I get alot of #REF! errors. Each facility will be listed 6 times with 6 different dates, is this what messing it up?

[mikeTRON]

Can you post a sample worksheet so we can see specificaly what you are trying to do?

[mtndewgradon]

I have attached the example. thank you guys again for you help

[FDibbins]

Try this array formula in E2 (the table should copy it down for you)...
=iferror(INDEX(C$2:C2,MATCH(A2&EOMONTH(C2,-1),A$2:A2&C$2:C2,0),1),"")
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Note: not all months have a "previous" month

[mtndewgradon]

That worked. Thank you so much. I can now sleep tonight.

[FDibbins]

Happy to help and thanks for the feedback