vlookup in a cell that contains mid formula
I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.
My vlookup formula is
=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)
Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?
microsoft-excel microsoft-excel-2007
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
add a comment |
I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.
My vlookup formula is
=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)
Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?
microsoft-excel microsoft-excel-2007
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
1
It shouldn't make a difference whether the cell targeted by theVLOOKUPcontains a formula or a fixed value. Can you describe what is in columns B and C on theLOCATION LOOKUP TABLEsheet and what is in cellF57?
– bdr9
Jul 21 '14 at 1:28
Since you set the fourth argument toFALSE(0),VLOOKUPreturns#N/Aif an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.
– ben
Jul 21 '14 at 12:01
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43
add a comment |
I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.
My vlookup formula is
=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)
Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?
microsoft-excel microsoft-excel-2007
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.
My vlookup formula is
=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)
Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?
microsoft-excel microsoft-excel-2007
microsoft-excel microsoft-excel-2007
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
edited Jul 21 '14 at 2:59
Sathyajith Bhat♦
52.8k29156252
52.8k29156252
asked Jul 21 '14 at 1:05
user348257user348257
1112
asked Jul 21 '14 at 1:05
user348257user348257
1112
asked Jul 21 '14 at 1:05
user348257user348257
1112
1112
1
It shouldn't make a difference whether the cell targeted by theVLOOKUPcontains a formula or a fixed value. Can you describe what is in columns B and C on theLOCATION LOOKUP TABLEsheet and what is in cellF57?
– bdr9
Jul 21 '14 at 1:28
Since you set the fourth argument toFALSE(0),VLOOKUPreturns#N/Aif an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.
– ben
Jul 21 '14 at 12:01
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43
add a comment |
1
It shouldn't make a difference whether the cell targeted by theVLOOKUPcontains a formula or a fixed value. Can you describe what is in columns B and C on theLOCATION LOOKUP TABLEsheet and what is in cellF57?
– bdr9
Jul 21 '14 at 1:28
Since you set the fourth argument toFALSE(0),VLOOKUPreturns#N/Aif an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.
– ben
Jul 21 '14 at 12:01
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43
1
1
It shouldn't make a difference whether the cell targeted by the
VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?– bdr9
Jul 21 '14 at 1:28
It shouldn't make a difference whether the cell targeted by the
VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?– bdr9
Jul 21 '14 at 1:28
Since you set the fourth argument to
FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.– ben
Jul 21 '14 at 12:01
Since you set the fourth argument to
FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.– ben
Jul 21 '14 at 12:01
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43
add a comment |
1 Answer
1
active
oldest
votes
To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.
So, if you expect a number, you can just multiply the result by 1:
for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .
If you do the same inside your vlookup -->
Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)
you'll assure that both your values are numbers.
It should work.
answered May 13 '15 at 16:02
P. O.P. O.
245112
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f785632%2fvlookup-in-a-cell-that-contains-mid-formula%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.
So, if you expect a number, you can just multiply the result by 1:
for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .
If you do the same inside your vlookup -->
Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)
you'll assure that both your values are numbers.
It should work.
answered May 13 '15 at 16:02
P. O.P. O.
245112
add a comment |
To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.
So, if you expect a number, you can just multiply the result by 1:
for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .
If you do the same inside your vlookup -->
Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)
you'll assure that both your values are numbers.
It should work.
answered May 13 '15 at 16:02
P. O.P. O.
245112
add a comment |
To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.
So, if you expect a number, you can just multiply the result by 1:
for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .
If you do the same inside your vlookup -->
Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)
you'll assure that both your values are numbers.
It should work.
answered May 13 '15 at 16:02
P. O.P. O.
245112
To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.
So, if you expect a number, you can just multiply the result by 1:
for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .
If you do the same inside your vlookup -->
Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)
you'll assure that both your values are numbers.
It should work.
answered May 13 '15 at 16:02
P. O.P. O.
245112
answered May 13 '15 at 16:02
P. O.P. O.
245112
answered May 13 '15 at 16:02
P. O.P. O.
245112
answered May 13 '15 at 16:02
P. O.P. O.
245112
245112
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f785632%2fvlookup-in-a-cell-that-contains-mid-formula%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
1
It shouldn't make a difference whether the cell targeted by the
VLOOKUPcontains a formula or a fixed value. Can you describe what is in columns B and C on theLOCATION LOOKUP TABLEsheet and what is in cellF57?– bdr9
Jul 21 '14 at 1:28
Since you set the fourth argument to
FALSE(0),VLOOKUPreturns#N/Aif an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.– ben
Jul 21 '14 at 12:01
If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
– datatoo
Jul 23 '14 at 20:43