Solved Need help with vlookup formula

August 31, 2012 at 09:56:29
Specs: Windows XP
I have several worksheet I am working from in Excel 2010. One worksheet has rows with account ID numbers. Also, there ae columns with the months for year 2012. I want to go into another worksheet and pull all the costs by account number for the appropriate months using the Vlookup formula. The formula works fine in the first cell but when I try to drag (copy) the formula, the result is either an REF# or N/A# error. Here is my formula =VLOOKUP($A6,'Subk'!$A7:$F18,4,FALSE)

When I drag the formula, the number 4(column that I want it to look for the return value) does not change. I want it to change to 5, 6, etc as I drag it. so it picks up the value in column 5 of the other worksheet , then column 6, etc to avoid having to do a vlookup formulafor every single entry. What am I doing wrong? Here are samples of my worksheets:

Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
1100-000 Sub Labor
1100-020 Sub Fee
1100-800 Sub Costs Accrued
The other worksheet looks like:
Fy Pd
Acct Id 06 07 08 09 10 11 12
1100-000 B SUB LABOR 32,349.61
1100-020 B SUB FEE 3,234.95
1100-000 E SUB LABOR 400.17
1100-000 B SUB LABOR 5,008.84
1100-800 D COSTS ACCRUED 31,119.91
1100-020 D SUB FEE 550.51

See More: Need help with vlookup formula

Report •

August 31, 2012 at 11:35:59
✔ Best Answer
First, a posting tip:

Before posting any more example data in this fourm, please click on the blue line at the end of this post and read the instructions found via that link.

That said, I'm not following exactly what you want to do, so I'll address this one issue for now. If there's something else you need help with, come on back.

When I drag the formula, the number 4...does not change. I want it to change to 5, 6, etc as I drag it

If you want the 4 to increment, you have to use an argument that will increment automatically.

One way to do that is to use the ROW function. The ROW function does nothing more than return the number of the Row it is used in.

e.g. if B6 contains =ROW(), it will return 6

Well, 6 - 2 = 4, so if you put this in B6, you'll get 4, drag it down one Row, you'll get 5, etc.


Therefore using:

=VLOOKUP($A6, 'Subk'!$A7:$F18, ROW()-2, FALSE)

will return a col_num argument that is always 2 less than the Row in which the formula is used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question