Excel Formula Vlookup issue
up vote
0
down vote
favorite
I have 2 excel sheets ("sheet 1, sheet 2").
Sheet 1 and Sheet 2 have "product #" in column A and "price" in column C. I'm trying to write a formula that would allow Sheet 1 to lookup on sheet 2 if there is a Product # match In column A and if there is a product # match then to update on Sheet 1 Column C (Price) the product price from sheet 2. If there is no Match of product # from sheet 2 then to leave the price on sheet 1 alone.
I'm using this formula which returns a "0" on an error but I can't figure out how to modify it to leave price alone when there's no product # found.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),0)
Thanks
excel formula
edited Nov 9 at 21:13
Mako212
3,6871624
asked Nov 9 at 19:52
sender russell
32
add a comment |
up vote
0
down vote
favorite
I have 2 excel sheets ("sheet 1, sheet 2").
Sheet 1 and Sheet 2 have "product #" in column A and "price" in column C. I'm trying to write a formula that would allow Sheet 1 to lookup on sheet 2 if there is a Product # match In column A and if there is a product # match then to update on Sheet 1 Column C (Price) the product price from sheet 2. If there is no Match of product # from sheet 2 then to leave the price on sheet 1 alone.
I'm using this formula which returns a "0" on an error but I can't figure out how to modify it to leave price alone when there's no product # found.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),0)
Thanks
excel formula
edited Nov 9 at 21:13
Mako212
3,6871624
asked Nov 9 at 19:52
sender russell
32
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have 2 excel sheets ("sheet 1, sheet 2").
Sheet 1 and Sheet 2 have "product #" in column A and "price" in column C. I'm trying to write a formula that would allow Sheet 1 to lookup on sheet 2 if there is a Product # match In column A and if there is a product # match then to update on Sheet 1 Column C (Price) the product price from sheet 2. If there is no Match of product # from sheet 2 then to leave the price on sheet 1 alone.
I'm using this formula which returns a "0" on an error but I can't figure out how to modify it to leave price alone when there's no product # found.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),0)
Thanks
excel formula
edited Nov 9 at 21:13
Mako212
3,6871624
asked Nov 9 at 19:52
sender russell
32
I have 2 excel sheets ("sheet 1, sheet 2").
Sheet 1 and Sheet 2 have "product #" in column A and "price" in column C. I'm trying to write a formula that would allow Sheet 1 to lookup on sheet 2 if there is a Product # match In column A and if there is a product # match then to update on Sheet 1 Column C (Price) the product price from sheet 2. If there is no Match of product # from sheet 2 then to leave the price on sheet 1 alone.
I'm using this formula which returns a "0" on an error but I can't figure out how to modify it to leave price alone when there's no product # found.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),0)
Thanks
excel formula
excel formula
edited Nov 9 at 21:13
Mako212
3,6871624
asked Nov 9 at 19:52
sender russell
32
edited Nov 9 at 21:13
Mako212
3,6871624
asked Nov 9 at 19:52
sender russell
32
edited Nov 9 at 21:13
Mako212
3,6871624
edited Nov 9 at 21:13
Mako212
3,6871624
edited Nov 9 at 21:13
Mako212
3,6871624
3,6871624
asked Nov 9 at 19:52
sender russell
32
asked Nov 9 at 19:52
sender russell
32
asked Nov 9 at 19:52
sender russell
32
32
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
accepted
The only caveat here is that you need to write this formula in a new column, let's say in D2, because you can't overwrite your original price with the formula.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),C2)
This says, "if A2 matches a value in Column A of the Updated Sheet, return the price from Column C of the updated sheet, unless there is an error, in which case, use the original price on Sheet 1 (C2).
answered Nov 9 at 20:14
Mako212
3,6871624
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
add a comment |
up vote
0
down vote
You want to use INDEX/MATCH for this:
Put the following formula in column C and populate down
=IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),"")
The MATCH will find, the row on Sheet2 with the same Product ID, the INDEX will return the matching price. If it matches the one on Sheet1, the formula returns the value on Sheet1 in column B. If it doesn't it will return the price from Sheet2
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
The only caveat here is that you need to write this formula in a new column, let's say in D2, because you can't overwrite your original price with the formula.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),C2)
This says, "if A2 matches a value in Column A of the Updated Sheet, return the price from Column C of the updated sheet, unless there is an error, in which case, use the original price on Sheet 1 (C2).
answered Nov 9 at 20:14
Mako212
3,6871624
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
add a comment |
up vote
0
down vote
accepted
The only caveat here is that you need to write this formula in a new column, let's say in D2, because you can't overwrite your original price with the formula.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),C2)
This says, "if A2 matches a value in Column A of the Updated Sheet, return the price from Column C of the updated sheet, unless there is an error, in which case, use the original price on Sheet 1 (C2).
answered Nov 9 at 20:14
Mako212
3,6871624
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
The only caveat here is that you need to write this formula in a new column, let's say in D2, because you can't overwrite your original price with the formula.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),C2)
This says, "if A2 matches a value in Column A of the Updated Sheet, return the price from Column C of the updated sheet, unless there is an error, in which case, use the original price on Sheet 1 (C2).
answered Nov 9 at 20:14
Mako212
3,6871624
The only caveat here is that you need to write this formula in a new column, let's say in D2, because you can't overwrite your original price with the formula.
=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),C2)
This says, "if A2 matches a value in Column A of the Updated Sheet, return the price from Column C of the updated sheet, unless there is an error, in which case, use the original price on Sheet 1 (C2).
answered Nov 9 at 20:14
Mako212
3,6871624
edited Nov 9 at 21:13
answered Nov 9 at 20:14
Mako212
3,6871624
answered Nov 9 at 20:14
Mako212
3,6871624
answered Nov 9 at 20:14
Mako212
3,6871624
3,6871624
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
add a comment |
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
See my edited answer
– cybernetic.nomad
Nov 9 at 20:35
add a comment |
up vote
0
down vote
You want to use INDEX/MATCH for this:
Put the following formula in column C and populate down
=IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),"")
The MATCH will find, the row on Sheet2 with the same Product ID, the INDEX will return the matching price. If it matches the one on Sheet1, the formula returns the value on Sheet1 in column B. If it doesn't it will return the price from Sheet2
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
add a comment |
up vote
0
down vote
You want to use INDEX/MATCH for this:
Put the following formula in column C and populate down
=IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),"")
The MATCH will find, the row on Sheet2 with the same Product ID, the INDEX will return the matching price. If it matches the one on Sheet1, the formula returns the value on Sheet1 in column B. If it doesn't it will return the price from Sheet2
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
add a comment |
up vote
0
down vote
up vote
0
down vote
You want to use INDEX/MATCH for this:
Put the following formula in column C and populate down
=IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),"")
The MATCH will find, the row on Sheet2 with the same Product ID, the INDEX will return the matching price. If it matches the one on Sheet1, the formula returns the value on Sheet1 in column B. If it doesn't it will return the price from Sheet2
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
You want to use INDEX/MATCH for this:
Put the following formula in column C and populate down
=IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),"")
The MATCH will find, the row on Sheet2 with the same Product ID, the INDEX will return the matching price. If it matches the one on Sheet1, the formula returns the value on Sheet1 in column B. If it doesn't it will return the price from Sheet2
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
edited Nov 9 at 20:35
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
answered Nov 9 at 20:07
cybernetic.nomad
1,9241515
1,9241515
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
add a comment |
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
my bad I think I was not so clear in the question... I need the price to remain on sheet 1 either way, so for example.. if the formula finds a product # on sheet 2 so it should fill in the price on sheet 1 and if it doesn't find the product # on sheet 2 it should leave the price for that product # on sheet one alone without changing anything. Thanks!
– sender russell
Nov 9 at 20:24
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53232445%2fexcel-formula-vlookup-issue%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
