CODEWITHSUNDEEP
excelvlookup
matthew
matthew

Reputation: 377

Excel VLOOKUP between two sheets failing

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)

Upvotes: 4

Views: 12902

Answers (2)

MukeshKoshyM
MukeshKoshyM

Reputation: 544

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!!!

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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)

Upvotes: 4

Related Questions