A happy and healthy new year to everyone. I have a problem I hope you can help me with.

I have 5 worksheets (2008 Expenses, 2009 Expenses, 2010 Expenses, 2011 Expenses and 2012 Expenses);
each worksheet containing 12 tabs (for 2008 Expenses the tabs are Jan 08, Feb 08, Mar 08 etc.);
Each tab contains in column C the description of the various expenses across rows 2 through 123.
Columns E through AI, the daily expenses, totaled, per row, in column A.
(so e.g. a2 = total i.e. sum(e2:ai2), c2 WAGES (description in words), e2 through ai2 the daily amounts)

In a sixth worksheet (2013 Expenses) I want to include the Monthly Expenses from earlier years, for reference purposes.
Column H lists the description of the various expenses across rows 2 through 123 (same as in the other worksheets);
Column K, per row, I want to show the total of Jan 12, in column L total Jan 11, in M total Jan 10, in N total Jan 09 in O total Jan 08.

In k2 I wanted to show the WAGES for January 2012 and entered =VLOOKUP(H2,'[2012 Expenses.xlsx]Jan 12'!$A$2:$C$123,1,FALSE)
I did get a result, but the figure is wrong.

Can anyone help me to solve this puzzle? Thanks in advance,

Gijs

You need to change the 1 at the end of the formula. So if your lookup reference is in column A and the value you wish to return is in column C then your formula should end ,3,false)

Let me know if you need more help.

Thank you Spencer, for your fast response. Regrettably it didn't work.
For the WAGES (h2 in 2013 Expenses), its lookup reference WAGES is in column C (in [2012 Expenses.xlsx]Jan 12') and the value I wish to return (in k2 in 2013 Expenses) is to be found in column A of [2012 Expenses.xlsx]Jan 12'.

Ok. VLOOKUP doesn't work where the value to be returned is in a column before the one with the reference in. You can use a combination of VLOOKUP and CHOOSE to accomplish this, or INDEX and MATCH.

Without seeing example workbooks it's difficult to provide you with an exact formula. If you need more help, feel free to email example workbooks to me on pubnut @ gmail . com (without spaces, obviously).

Thank you Spencer. And thank you for the offer.
Since the sheets contain confidential information and are password protected, I will develop two sample sheets and email them to you. THANKS

Not a problem. I'm here to help :)

In the mean time, perhaps try the below formula for one way to do it.

=VLOOKUP(H2,CHOOSE({1,2},'[2012 Expenses.xlsx]Jan 12'!$c$2:$C$123,'[2012 Expenses.xlsx]Jan 12'!$A$2:$A$1231),2,FALSE)

or

=INDEX('[2012 Expenses.xlsx]Jan 12'!$A$2:$A$1231,MATCH(H2,'[2012 Expenses.xlsx]Jan 12'!$c$2:$C$123,0))

Obviously without sample workbooks I'm unable to properly test these formulas, but the idea is there and they should work.

"GijsKijlstra" wrote:
I have 5 worksheets (2008 Expenses, 2009 Expenses, 2010 Expenses, 2011
Expenses and 2012 Expenses); each worksheet containing 12 tabs

FYI, those 5 __workbooks__. The "tabs" are the __worksheets__.


"GijsKijlstra" wrote:
(so e.g. a2 = total i.e. sum(e2:ai2), c2 WAGES (description in words),
e2 through ai2 the daily amounts)
[....]
In k2 I wanted to show the WAGES for January 2012 and entered
=VLOOKUP(H2,'[2012 Expenses.xlsx]Jan 12'!$A$2:$C$123,1,FALSE)
I did get a result, but the figure is wrong.

If you want wages (column C), that is column 3 of the range $A$2:$C$123.
So the formula should be:

=VLOOKUP(H2,'[2012 Expenses.xlsx]Jan 12'!$A$2:$C$123,3,FALSE)

Note the 3 instead 1 in the 3rd parameter.

Hi Spencer. Your formula =INDEX('[2012 Expenses.xlsx]Jan 12'!$A$2:$A$1231,MATCH(H2,'[2012 Expenses.xlsx]Jan 12'!$c$2:$C$123,0)) worked like a charm. Needless to say it required some manipulation to get everying in one sheet, but it works perfectly. Thanks!

Thank you for the clarification "workbooks and "tabs" being the worksheets". I've meanwhile solved the issue with Index/Match. Thanks.