Excel VLOOKUP between two sheets failing

Question

I am trying to copy data from column E (cost) in sheet1 where the value in column A (code) in sheet2 matches to the value in column A (code) on sheet1.

1
+--------+--------+---------------+---------+--------+
| A      | B      | C             | D       | E      |
+--------+--------+---------------+---------+--------+
| Code   | Name   | Description   | Price   | Cost   |
+--------+--------+---------------+---------+--------+
| AC33   | Prod 1 | Prod Desc 1   |  3.99   | 2.00   |
+--------+--------+---------------+---------+--------+
| AC34   | Prod 2 | Prod Desc 2   |  4.99   | 3.00   |
+--------+--------+---------------+---------+--------+
| AC35   | Prod 3 | Prod Desc 3   |  5.99   | 4.00   |
+--------+--------+---------------+---------+--------+

2

+--------+--------+---------------+---------+
| A      | B      | C             | D       |
+--------+--------+---------------+---------+
| Code   | Name   |Updated Price  | Cost    |
+--------+--------+---------------+---------+
| AC33   | Prod 1 |    16.99      |         | 
+--------+--------+---------------+---------+
| AC37   | Prod 2 |    18.99      |         |
+--------+--------+---------------+---------+
| AC38   | Prod 3 |    21.99      |         | 
+--------+--------+---------------+---------+

I have used a VLOOKUP but it isn't working, can anyone help please? Am I right in using Vlookup?

This is the formula I am dragging down in column D of sheet2

=VLOOKUP(A2,'1'!$A$2:$A$811,5)

Solution

Try this one:

=VLOOKUP(A2,'1'!$A$2:$E$811,5,0)

I changed '1'!$A$2:$A$811 to '1'!$A$2:$E$811 (as mentioned @Jerry in the comment below, "the table range you're using VLOOKUP on should contain both the column of lookup value and the column containing the result you wish to extract" ) and I also specified last argument of VLOOKUP equals to 0, which says VLOOKUP to find an exact match.

UPD:

As follows up from comments, next formula works for OP:

=VLOOKUP(TRIM(A2),'1'!$A$2:$E$811,5,0)

Comments

[Jerry]

+1 For the explanation, the table range you're using VLOOKUP on should contain both the column of lookup value and the column containing the result you wish to extract.

[matthew]

Thankyou @simoco I knew I must have been missing something! Perhaps there is still an error in the formula though? I am still getting #N/A in each cell

[Dmitry Pavliv]

try to check, maybe you have some extra spaces in column A (in sheet 1 or in sheet 2, i.g. "AC38 " instead "AC38") and also try =VLOOKUP(TRIM(A2),'1'!$A$2:$E$811,5,0)

[matthew]

The trim function did it! Thank you so much

[matthew]

Thankyou @jerry for your advice too :-)