0

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.

https://ibb.co/6DHfhN3

6
  • 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. Jan 15 '19 at 22:38
  • I attached that, hope it's helpful
    – ndocds
    Jan 15 '19 at 22:59
  • "..my zone listening" did you mean 'listing'?
    – p._phidot_
    Jan 16 '19 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 '19 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 '19 at 22:39
1

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. ( :

1
  • You are my hero sir....
    – ndocds
    Jan 16 '19 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.