0

I'm having problem with the formula I'm using in Excel. Since I can't add images here, here's a link:

Here's the link to the sheet, where Sheet 1 is the source data: https://www.dropbox.com/s/ispzi99u884ic9i/New%20Microsoft%20Excel%20Worksheet%20%282%29%20-%20Copy.xlsx?dl=0

I'm using this formula: =VLOOKUP(A2,Sheet1!A1:D1000,2,FALSE)

As of now, I can enter value in column A of Sheet 1 and it will return the whole row containing that. The problem is, when I try to enter "Code1", I notice it doesn't return any data. Can someone explain why that happens? I'm an Excel noob...

  • Looks like it's returning the row on Sheet 2. If you type Code1 into A5, what do you see on row 5? – Fayyaadh Nov 2 '14 at 15:44
  • It doesn't return anything but when I try adding a different value, like Code4, it returns the correct row. It's just the first value, Code1, that I'm hacing problem with. – user3580021 Nov 2 '14 at 15:54
  • Does the word Code1 on Sheet1 maybe have a trailing space? Make sure it doesn't. And if you try doing this: =VLOOKUP("Code1",Sheet1!A1:D1000,2,FALSE)...does that work? – Fayyaadh Nov 2 '14 at 15:57
  • 1
    When you drag across, make sure the first argument of the VLOOKUP is column-locked like this: $A2, this ensures that it will always look up A2 as you move across the columns. – Fayyaadh Nov 2 '14 at 16:04
  • 1
    Don't you mean to lock the Sheet1 values with $ =VLOOKUP("Code1",Sheet1!$A$1:$D$1000,2,FALSE) – Brian Folan Nov 2 '14 at 16:08
1

You need to lock the table_array parameter down as an absolute cell range reference with the $ symbol.

=VLOOKUP($A2,Sheet1!$A$1:$D$1000,2,FALSE)

I've also locked down the column portion of the A2 reference so that is will not change if filled right (mentioned above by @Fayyaadh). When you were filling down, the relative A1:D1000 was changing to A2:D1001, A3:D1002, etc. By the time you got to row 8, this was Sheet1!A7:D1006 which no longer encompassed your Code1 lookup_value.

If you want to fill right (aka drag across), then perhaps this modification may help.

=VLOOKUP($A2,Sheet1!$A$1:$D$1000,COLUMN(B:B),FALSE)

This will change to COLUMN(C:C), etc as it is filled right, effectively incrementing the column that you are returning (e.g. 2, 3, 4, etc).

  • Thanks! I got the correct results using this, and changing $D$1000 to $I$1000! Thanks! :) – user3580021 Nov 2 '14 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.