Excel vlookup not working
I am using a Mac.
I have 3 categories of ZIP Codes, Zone 1, Zone 2 and Zone 3. I have a list of ZIP Codes and I need to see easily if a zip code is zone 1, 2 or 3.
Column E holds my ZIP Code. Column F has my zone listing, with value 1, 2 or 3.
Sheet 2 holds my ZIP Code list, but it is massive. One is 514 rows. I am currently doing this manually and it's taking forever.
My Vlookup formula is shown in the image, and gives me an error.
Realistically, it would be great if it returned a value of 1, 2 or 3. However if it just returned anything and I had to go through each column of ZIP Codes, even that would be great.
microsoft-excel worksheet-function
edited Jan 16 at 5:10
fixer1234
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
|
show 1 more comment
I am using a Mac.
I have 3 categories of ZIP Codes, Zone 1, Zone 2 and Zone 3. I have a list of ZIP Codes and I need to see easily if a zip code is zone 1, 2 or 3.
Column E holds my ZIP Code. Column F has my zone listing, with value 1, 2 or 3.
Sheet 2 holds my ZIP Code list, but it is massive. One is 514 rows. I am currently doing this manually and it's taking forever.
My Vlookup formula is shown in the image, and gives me an error.
Realistically, it would be great if it returned a value of 1, 2 or 3. However if it just returned anything and I had to go through each column of ZIP Codes, even that would be great.
microsoft-excel worksheet-function
edited Jan 16 at 5:10
fixer1234
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
1
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39
|
show 1 more comment
I am using a Mac.
I have 3 categories of ZIP Codes, Zone 1, Zone 2 and Zone 3. I have a list of ZIP Codes and I need to see easily if a zip code is zone 1, 2 or 3.
Column E holds my ZIP Code. Column F has my zone listing, with value 1, 2 or 3.
Sheet 2 holds my ZIP Code list, but it is massive. One is 514 rows. I am currently doing this manually and it's taking forever.
My Vlookup formula is shown in the image, and gives me an error.
Realistically, it would be great if it returned a value of 1, 2 or 3. However if it just returned anything and I had to go through each column of ZIP Codes, even that would be great.
microsoft-excel worksheet-function
edited Jan 16 at 5:10
fixer1234
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
I am using a Mac.
I have 3 categories of ZIP Codes, Zone 1, Zone 2 and Zone 3. I have a list of ZIP Codes and I need to see easily if a zip code is zone 1, 2 or 3.
Column E holds my ZIP Code. Column F has my zone listing, with value 1, 2 or 3.
Sheet 2 holds my ZIP Code list, but it is massive. One is 514 rows. I am currently doing this manually and it's taking forever.
My Vlookup formula is shown in the image, and gives me an error.
Realistically, it would be great if it returned a value of 1, 2 or 3. However if it just returned anything and I had to go through each column of ZIP Codes, even that would be great.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Jan 16 at 5:10
fixer1234
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
edited Jan 16 at 5:10
fixer1234
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
edited Jan 16 at 5:10
fixer1234
18.8k144982
edited Jan 16 at 5:10
fixer1234
18.8k144982
edited Jan 16 at 5:10
fixer1234
18.8k144982
18.8k144982
asked Jan 15 at 22:31
ndocdsndocds
206
asked Jan 15 at 22:31
ndocdsndocds
206
asked Jan 15 at 22:31
ndocdsndocds
206
206
1
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39
|
show 1 more comment
1
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39
1
1
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39
|
show 1 more comment
1 Answer
1
active
oldest
votes
in F2.. put :
=IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"")
& drag downwards.
Idea : check if match found, print zone number, else print blank. concatenate all.
catch : if there is a postcode is repeated in more than 1 zone, It'll print both zone.
Hope it helps. ( :
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
You are my hero sir....
– ndocds
Jan 16 at 19:22
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%2f1394718%2fexcel-vlookup-not-working%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
in F2.. put :
=IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"")
& drag downwards.
Idea : check if match found, print zone number, else print blank. concatenate all.
catch : if there is a postcode is repeated in more than 1 zone, It'll print both zone.
Hope it helps. ( :
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
You are my hero sir....
– ndocds
Jan 16 at 19:22
add a comment |
in F2.. put :
=IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"")
& drag downwards.
Idea : check if match found, print zone number, else print blank. concatenate all.
catch : if there is a postcode is repeated in more than 1 zone, It'll print both zone.
Hope it helps. ( :
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
You are my hero sir....
– ndocds
Jan 16 at 19:22
add a comment |
in F2.. put :
=IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"")
& drag downwards.
Idea : check if match found, print zone number, else print blank. concatenate all.
catch : if there is a postcode is repeated in more than 1 zone, It'll print both zone.
Hope it helps. ( :
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
in F2.. put :
=IFERROR(IF(MATCH(E2,H:H,0)>1,1,""),"")&IFERROR(IF(MATCH(E2,I:I,0)>1,2,""),"")&IFERROR(IF(MATCH(E2,J:J,0)>1,3,""),"")
& drag downwards.
Idea : check if match found, print zone number, else print blank. concatenate all.
catch : if there is a postcode is repeated in more than 1 zone, It'll print both zone.
Hope it helps. ( :
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
edited Jan 17 at 0:12
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
answered Jan 16 at 3:23
p._phidot_p._phidot_
686412
686412
You are my hero sir....
– ndocds
Jan 16 at 19:22
add a comment |
You are my hero sir....
– ndocds
Jan 16 at 19:22
You are my hero sir....
– ndocds
Jan 16 at 19:22
You are my hero sir....
– ndocds
Jan 16 at 19:22
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%2f1394718%2fexcel-vlookup-not-working%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
Welcome. Can you please post a screenshot of examples you are talking about along with your VLOOKUP which is returning an error? It makes it easier than trying to visualise and piece together your description of what you have.
– Chris Rogers
Jan 15 at 22:38
I attached that, hope it's helpful
– ndocds
Jan 15 at 22:59
"..my zone listening" did you mean 'listing'?
– p._phidot_
Jan 16 at 3:05
Your question isn't clear. What does sheet2 look like? Where is the source data you want to look up? Where is the destination you want to fill in? Is Q:S your lookup table where you see what ZIPs are in what zones, or is that the table you're trying to build based on what's in the data?
– fixer1234
Jan 16 at 5:15
The screenshot is really hard to see & I would suggest including the VLOOKUP formula in text, but you also do need to give a hint about Sheet 2 data. However, I see a reference to "X1" where there should be a column number reference instead - the column number from the reference range - and you also don't specify -what- error. If VLOOKUP doesn't find a match, it normally will show an "error".
– Debra
Jan 16 at 22:39