Copying data using VLOOKUP error

[lumineers01]

I'm having problem with the formula I'm using in Excel.


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... I need the formula to return the whole row of the identifier (Code1, 2 etc..).

Thanks!

 

[Vidar]

Seems like you need to lock the cell reference before copying down the rows:
=VLOOKUP(A2,Sheet1!$A$1:$D$1000,2,FALSE)

 

[lumineers01]

Hi Vidar! I tried that, but now i can't seem to copy it across all the columns. i need the formula to return the whole row.

 

[Vidar]

Hi
Try this formula on sheet2 cell B2, copy across to column I, then down:
=VLOOKUP($A2,Sheet1!$A$1:$I$1000,COLUMNS($A$1:B$1),FALSE)

or by modifying the previous one a little:
=VLOOKUP(A2,Sheet1!A$1:D$1000,2,FALSE)

 

[juweh]

Hi,

Just lock the row reference

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

PS: refreshed the page and answer already provided

 

[lumineers01]

Hi Vidar! The first one works! Though I only locked $A2 and $A$1:$I$1000.
I'm getting the correct results now.

 

[Vidar]

You're welcome