Mixing
Updating Price List in Excel with vlookup or Index/Match
up vote
0
down vote
favorite
I'm a book wholesaler and need to update my price and stock list constantly to share with some customers in excel format; also to use in importing to websites.
As in the image above, what I need is a formula for Column D to search for SKU in C2 in A-column for an exact match; return the value from B to the corresponding cell in D column.
I have searched countless topics, even found a solution in previous weeks but every time I use the same formula I get N/A or REF error.
TIA
excel excel-2010
edited 2 days ago
Tobias Wilfert
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
I'm a book wholesaler and need to update my price and stock list constantly to share with some customers in excel format; also to use in importing to websites.
As in the image above, what I need is a formula for Column D to search for SKU in C2 in A-column for an exact match; return the value from B to the corresponding cell in D column.
I have searched countless topics, even found a solution in previous weeks but every time I use the same formula I get N/A or REF error.
TIA
excel excel-2010
edited 2 days ago
Tobias Wilfert
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm a book wholesaler and need to update my price and stock list constantly to share with some customers in excel format; also to use in importing to websites.
As in the image above, what I need is a formula for Column D to search for SKU in C2 in A-column for an exact match; return the value from B to the corresponding cell in D column.
I have searched countless topics, even found a solution in previous weeks but every time I use the same formula I get N/A or REF error.
TIA
excel excel-2010
edited 2 days ago
Tobias Wilfert
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm a book wholesaler and need to update my price and stock list constantly to share with some customers in excel format; also to use in importing to websites.
As in the image above, what I need is a formula for Column D to search for SKU in C2 in A-column for an exact match; return the value from B to the corresponding cell in D column.
I have searched countless topics, even found a solution in previous weeks but every time I use the same formula I get N/A or REF error.
TIA
excel excel-2010
excel excel-2010
edited 2 days ago
Tobias Wilfert
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
Tobias Wilfert
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
Tobias Wilfert
2701417
edited 2 days ago
Tobias Wilfert
2701417
edited 2 days ago
Tobias Wilfert
2701417
2701417
asked 2 days ago
Gehun
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Gehun
81
asked 2 days ago
Gehun
81
81
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Gehun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.
So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:
=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)
Hope you can adapt this to your needs.
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.
So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:
=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)
Hope you can adapt this to your needs.
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
add a comment |
up vote
0
down vote
accepted
Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.
So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:
=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)
Hope you can adapt this to your needs.
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.
So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:
=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)
Hope you can adapt this to your needs.
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.
So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:
=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)
Hope you can adapt this to your needs.
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
answered 2 days ago
Foxfire And Burns And Burns
1,8711314
1,8711314
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
add a comment |
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Thank you for pointing out the Text/Number difference as I had many of the rows in different format. I've just edited em all to numbers by copying 1 and multipliyng all to get them in proper Number format and Vlookup worked just fine. Thank you again.
– Gehun
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
Glad to hear it worked :)
– Foxfire And Burns And Burns
2 days ago
add a comment |
Gehun is a new contributor. Be nice, and check out our Code of Conduct.
Gehun is a new contributor. Be nice, and check out our Code of Conduct.
Gehun is a new contributor. Be nice, and check out our Code of Conduct.
Gehun is a new contributor. Be nice, and check out our Code of Conduct.
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%2f53373590%2fupdating-price-list-in-excel-with-vlookup-or-index-match%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
