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)

Dmitry Pavliv · Accepted Answer · 2014-01-31 10:48:56Z

4

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)

edited Jan 31, 2014 at 10:48

answered Jan 31, 2014 at 10:13

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jerry Over a year ago

+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 Over a year ago

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 Over a year ago

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 Over a year ago

The trim function did it! Thank you so much

MukeshKoshyM · Accepted Answer · 2018-08-21 17:31:06Z

0

Below are some of the best practices which resolved the issue.

1) Use the dollar symbol for arguments

2) Provide the complete options

Syntax

VLOOKUP('Lookupvalue','Table Array','Column Index','Range Lookup')

Please note
range_lookup TRUE = approximate match and is by default whereas FALSE = exact match.
Please make it false or 0 if you are looking for an exact match.TRUE value relies on your data being sorted in ascending order to work.

Example

VLOOKUP($A1,SheetName!$A:$G,2,0)

Enjoy!!!

answered Aug 21, 2018 at 17:31

MukeshKoshyM

5642 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

Excel VLOOKUP between two sheets failing

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related