• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP formula no longer working

H

Heavynbound

New Member
We just migrated to Excel 2010 and I cannot no longer drag my original formula horizontally across rows and get values. I keep getting an N/A# or REF #. My workbook have employee ID number, and I do a vlookup by employee ID number to get their monthly costs incurred.


For example:


Apr May Jun Jul Aug

EMPLOYEE ID

1111

2222

3333

4444

Say, i do a vlookup for employee 1111 to get his July costs. The costs are in another worksheet. The vlookup formula works fine for the Apr costs but when I try to drag the formula to get the values for May through Aug I get N/A# but the worksheet does have their costs. The formula I ma using is =VLOOKUP($A1,'Pivot-DL$'!$A:$L,3,FALSE)


$A1 = Employee ID number

'Pivot-DL$'!$A:$L = worksheet that has the employee costs by month

3 = column that has the employee costs for April which is the first month I need

FALSE = exact match


If I drag this vormula vertical, it works fine for all the months needed but if I drag it across horizontally (which is what I need for the months), it will no longer work. I used this all the time in Excel 2007 but it will not work in Excel 2010.


Please help! thanks in advance.

BTW, I tried hlookup and that did not work.
 
Hi, Heavynbound!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


You can try to change your VLOOKUP formula as (assuming your start column is B, 2):

=VLOOKUP($A1,'Pivot-DL$'!$A:$L,COLUMN()+1,FALSE)

then you can copy down and across safely, taking care or the relative COLUMN()+1 expression to get 3 as first column, and so on.


Regards!
 
I did do many searches on VLOOKUP but I did not see what I was looking for. I will try this and hopefully it will work.


Thanks for welcoming me and helping me with this.
 
I just tried the formula recommended here and it worked but it gave me the return value for Column E vice Column C. The months of the Pivot table I need the return value for in my current worksheet was in Column B through E.


Column B Employee ID

Column C (May)

Column D (Jun)

Column E (July)


I used the following formula as recommended

=VLOOKUP($B49,'Pivot-DL$(A1)'!$A:$E,COLUMN()+1,FALSE)


The first return value I needed was for Column C (May) but it gave me the return value for Column E (July). So, I changed the formula to:


=VLOOKUP($B49,'Pivot-DL$(A1)'!$A:$C,COLUMN()+1,FALSE)


Now, I get an REF# message. Can someone please tell me what is wrong?
 
I think you're on the right track. Starting with your original formula:

=VLOOKUP($A1,'Pivot-DL$'!$A:$L,3,FALSE)

The 3rd argument is what we need to be changing. rather than just using COLUMN() which would be dependent on which cell we put the formula in, let's define it to be more specific as:

=VLOOKUP($A1,'Pivot-DL$'!$A:$L,COLUMN(C$1),FALSE)

That way, no matter where we move the cell with this formula, we're going to start by looking at col C within the array given in the 2nd arguement. As you copy the formula to the right, the C$1 will change to D$1, and you should get the values you want.
 
You must log in or register to reply here.
Back
Top