VLOOKUP / CONCATENATE “#N/A” error
I am trying to put two columns together and then look up the value from another sheet. If I hardcode the numbers the VLOOKUP works but with CONCATENATE I get #N/A. Both columns are General format. So I do not understand what I am doing wrong.
This is what I am doing, in Excel 2010:
=VLOOKUP(CONCATENATE($W26,W$1),Sheet2!$A$1:$B$200,2,FALSE)
Any ideas?
excel excel-formula
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
add a comment |
I am trying to put two columns together and then look up the value from another sheet. If I hardcode the numbers the VLOOKUP works but with CONCATENATE I get #N/A. Both columns are General format. So I do not understand what I am doing wrong.
This is what I am doing, in Excel 2010:
=VLOOKUP(CONCATENATE($W26,W$1),Sheet2!$A$1:$B$200,2,FALSE)
Any ideas?
excel excel-formula
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
add a comment |
I am trying to put two columns together and then look up the value from another sheet. If I hardcode the numbers the VLOOKUP works but with CONCATENATE I get #N/A. Both columns are General format. So I do not understand what I am doing wrong.
This is what I am doing, in Excel 2010:
=VLOOKUP(CONCATENATE($W26,W$1),Sheet2!$A$1:$B$200,2,FALSE)
Any ideas?
excel excel-formula
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
I am trying to put two columns together and then look up the value from another sheet. If I hardcode the numbers the VLOOKUP works but with CONCATENATE I get #N/A. Both columns are General format. So I do not understand what I am doing wrong.
This is what I am doing, in Excel 2010:
=VLOOKUP(CONCATENATE($W26,W$1),Sheet2!$A$1:$B$200,2,FALSE)
Any ideas?
excel excel-formula
excel excel-formula
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
edited Nov 29 '15 at 17:30
pnuts
48.5k76297
48.5k76297
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
asked Jul 14 '15 at 14:51
mrmcgmrmcg
84214
84214
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Just use simple comma at begin of cell
like '#N/A
It will treat column as String, and not as number
answered Jul 29 '16 at 22:05
IsraelIsrael
1
add a comment |
=VLOOKUP(CONCATENATE($W26,W$1)+0,Sheet2!$A$1:$B$200,2,FALSE)
Someone sent this to me and it works great.
edited Nov 26 '18 at 0:53
Pang
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
add a comment |
concatenate cannot have a range, you can give a single cell or value in double quotation mark. When you drag down the formula will change as per the request.
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f31410048%2fvlookup-concatenate-n-a-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Just use simple comma at begin of cell
like '#N/A
It will treat column as String, and not as number
answered Jul 29 '16 at 22:05
IsraelIsrael
1
add a comment |
Just use simple comma at begin of cell
like '#N/A
It will treat column as String, and not as number
answered Jul 29 '16 at 22:05
IsraelIsrael
1
add a comment |
Just use simple comma at begin of cell
like '#N/A
It will treat column as String, and not as number
answered Jul 29 '16 at 22:05
IsraelIsrael
1
Just use simple comma at begin of cell
like '#N/A
It will treat column as String, and not as number
answered Jul 29 '16 at 22:05
IsraelIsrael
1
answered Jul 29 '16 at 22:05
IsraelIsrael
1
answered Jul 29 '16 at 22:05
IsraelIsrael
1
answered Jul 29 '16 at 22:05
IsraelIsrael
1
1
add a comment |
add a comment |
=VLOOKUP(CONCATENATE($W26,W$1)+0,Sheet2!$A$1:$B$200,2,FALSE)
Someone sent this to me and it works great.
edited Nov 26 '18 at 0:53
Pang
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
add a comment |
=VLOOKUP(CONCATENATE($W26,W$1)+0,Sheet2!$A$1:$B$200,2,FALSE)
Someone sent this to me and it works great.
edited Nov 26 '18 at 0:53
Pang
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
add a comment |
=VLOOKUP(CONCATENATE($W26,W$1)+0,Sheet2!$A$1:$B$200,2,FALSE)
Someone sent this to me and it works great.
edited Nov 26 '18 at 0:53
Pang
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
=VLOOKUP(CONCATENATE($W26,W$1)+0,Sheet2!$A$1:$B$200,2,FALSE)
Someone sent this to me and it works great.
edited Nov 26 '18 at 0:53
Pang
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
edited Nov 26 '18 at 0:53
Pang
6,9011664102
edited Nov 26 '18 at 0:53
Pang
6,9011664102
edited Nov 26 '18 at 0:53
Pang
6,9011664102
6,9011664102
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
answered Jul 14 '15 at 15:11
mrmcgmrmcg
84214
84214
add a comment |
add a comment |
concatenate cannot have a range, you can give a single cell or value in double quotation mark. When you drag down the formula will change as per the request.
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
add a comment |
concatenate cannot have a range, you can give a single cell or value in double quotation mark. When you drag down the formula will change as per the request.
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
add a comment |
concatenate cannot have a range, you can give a single cell or value in double quotation mark. When you drag down the formula will change as per the request.
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
concatenate cannot have a range, you can give a single cell or value in double quotation mark. When you drag down the formula will change as per the request.
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
answered Jul 14 '15 at 14:58
Sandesh Jadhav MSandesh Jadhav M
408
408
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
add a comment |
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
Not sure on what you are saying to do. Can you do a quick sample of what you are asking me to do. Sorry. Better way for me to understand and learn it better. I am a visual learner.
– mrmcg
Jul 14 '15 at 15:01
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
he hasnt given concatenate a range...
– user844705
Jul 14 '15 at 15:07
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f31410048%2fvlookup-concatenate-n-a-error%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
