Vlookup doesn't work: results are wrong
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 |
Quote:
Let me know if you need more help. |
Quote:
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'. |
Quote:
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). |
Quote:
Since the sheets contain confidential information and are password protected, I will develop two sample sheets and email them to you. THANKS |
Quote:
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. |
Vlookup doesn't work: results are wrong
"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. |
Quote:
|
Quote:
|
All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com