1

Please see my public spreadsheet here:

https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687

My goal is to get the Location Code from tab 1 into tab 2 (tab 2 is a subset of specific zip codes and I need them with their matching Location Codes alongside them).

I was attempting to use a VLOOKUP to do it, but got this error:

Error: Did not find value '30096' in VLOOKUP evaluation.

In this case, I would expect to see the Location Code 79459 in this cell.

Guess I'm misunderstanding how VLOOKUP works and/or how to use it. I tried to follow the prompts.

0

It's not so much an error as a statement of fact. 30096 does not exist in column A which is where you are looking for it. The way vlookup works is the column you are looking for a match is always to the left. The index is the column you want to return when it finds a match.

| improve this answer | |
0

There are a few issues with your sheet. But can be fixed.

  • Your "Zip Code" values in column B in your sheet Lookup are text instead of numbers.
    Tip: You can use =ISNUMBER()to check the value of a cell.
  • You have a lot of data in your Lookup sheet
  • VLOOKUP does not work because (as also mentioned by CodeCamper) it always "looks" from left to right.

Steps to fix the sheet.

Step 1:
Change the formula you use in Lookup!B to:

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(C2:C,"[0-9]+")*1))

This way (using IFERROR) you avoid the #N/A in your empty lines and by using *1 you change them all to numbers.

Step 2:
Move column Lookup!B to the beginning of the sheet, thus making it Lookup!A.
We do this because of the fact that you have a lot of data and use your formula (in sheet AdamHelp)

=ARRAYFORMULA(IF(A2:A="",,IFERROR(VLOOKUP(A2:A,{VALUE(Lookup!B2:B),VALUE(Lookup!A2:A)},2,FALSE))))

the VLOOKUP calculations take forever to complete.

When using {VALUE(Lookup!B2:B),VALUE(Lookup!A2:A)} you do solve the left search issue of the VLOOKUP function but at the same time you create unnecessary -in your case- arrays that really slow down the sheet.
Tip: If moving the column to the beginning is not possible, move it after column C (Display name) and make necessary adjustments to the final formula. Left VLOOKUP is to be avoided by all means when possible.

Step 3:
Finally, change your formula in AdamHelp to:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,Lookup!A2:B,2,0)))

Following these steps you will not only get the correct results but will also see a huge improvement on the calculation time of the sheet.

| improve this answer | |
  • Hi, thanks for this. I created a new spreadsheet copy for testing: docs.google.com/spreadsheets/d/… There are some issues - you can see them. 1. Any leading zeros are being stripped from the zip codes. 2. There is an error in the 'Relevant Zip Codes' tab (Error VLOOKUP evaluates to an out-of-bounds range.) – Drewdavid Dec 22 '19 at 14:01
  • "There is an error in the 'Relevant Zip Codes' tab (Error VLOOKUP evaluates to an out-of-bounds range.)". You had a mistake in the formula you copied. I fixed it. It works fine now. "". Keeping the leading zeros is a completely different thing from what you were asking. In any case you need to choose Format > ` Number` > More Formats > Custom number formats > 00000 > Apply if the specific format is not there you have to create it. – marikamitsos Dec 22 '19 at 16:20

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.