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)
Best Answer
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 usingVLOOKUP
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 ofVLOOKUP
equals to0
, which saysVLOOKUP
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)