Vlookup doesn't work: results are wrong

G

GijsKijlstra

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

I have 5 worksheets (2008 Expenses, 2009 Expenses, 2010 Expenses, 201
Expenses and 2012 Expenses);
each worksheet containing 12 tabs (for 2008 Expenses the tabs are Ja
08, Feb 08, Mar 08 etc.);
Each tab contains in column C the description of the various expense
across rows 2 through 123.
Columns E through AI, the daily expenses, totaled, per row, in colum
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 Monthl
Expenses from earlier years, for reference purposes.
Column H lists the description of the various expenses across rows
through 123 (same as in the other worksheets);
Column K, per row, I want to show the total of Jan 12, in column L tota
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 entere
=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,

Gij
 
J

joeu2004

GijsKijlstra said:
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 said:
(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.
 
G

GijsKijlstra

Spencer101;1608365 said:
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,'[201
Expenses.xlsx]Jan 12'!$A$2:$A$1231),2,FALSE)

or

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

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

Hi Spencer. Your formula =INDEX('[2012 Expenses.xlsx]Ja
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 t
get everying in one sheet, but it works perfectly. Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top