# Excel - VLOOKUP Formula

Please offer where get an N/A as a result of the following formula:

=VLOOKUP(H2,'Lookup Tables'!A\$1:D\$410,2,FALSE)

Sheet1 - where the values from Lookup are being populated.
Column H on Sheet1 - is the ZipCode

'Lookup Tables' - the sheet that has the lookup values
ColumnA - Zip Code
ColumnB - City

Basically - I want to use the zipcode from ColumnH in Sheet1 to match to zipcode in ColumnA from sheet 'Lookup Tables' to return the city located in Column B in 'Lookup Tables'

The city name in Sheet1 is located in ColumnK - where the Vlookup formula is being entrered.

H2 is in Sheet1 where I am trying to populate the from the sheet 'Lookup Tables' - column A to D - rows 1 to 410. In the sheet 'Lookup Tables' column 2 - zip
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
I also tried the following without success:

=VLOOKUP(H2,'Lookup Tables'!A\$2:B\$410,2,FALSE)
Excel & VBA ExpertCommented:
What is your question? I couldn't get it.

If you are trying to get rid of #N/A returned by the VlookUp formula where no result is found, use IFERROR function like this.....

``````=IFERROR(VLOOKUP(H2,'Lookup Tables'!A\$1:D\$410,2,FALSE),"")
``````
Excel & VBA ExpertCommented:
Hmm. It seems that your VlookUp formula is not working.

In this case check the ZipCode columns on both the sheets to make sure that they have the same format.
Author Commented:
I tried this

=IFERROR(VLOOKUP(H2,'Lookup Tables'!A\$2:D\$410,2,FALSE)," ")

And the N/A were removed - but now I get blank values.

The zip codes are in the same format on both sheets

In order to ensure clarity

Sheet1 - ColumnH is Zip Code

'Lookup Tables' - ColumnA is Zip Code, and ColumnB is City - the entire table is Columns A - D

I am placing this formula in Sheet1 in ColumnK to place the City where Zip Code columns are the identification columns.

Thank you.
Author Commented:
Basically - I believe he blanks are there since the formula is read as an error for the entire Columns K.
Commented:
exp vg,

Can you please post your sample workbook and where you are getting an error as it will become easy to write the necessary formula for you..

Saurabh...
Author Commented:
Please find this attached. The VLOOKUP formula is in column K on the first tab.
VLOOKUP.xlsx
Business Systems Analyst , ex-Senior Application EngineerCommented:
you probably can try:

column K, place this formula at cell K2:
``````=IFERROR( VLOOKUP(TEXT(H2,"00000"),'Lookup Tables'!A:D,2,FALSE), "")
``````
column L, place this formula at cell L2:
``````=IFERROR(VLOOKUP(TEXT(H2,"00000"),'Lookup Tables'!A:D,3,FALSE),"")
``````
column M, place this formula at cell M2:
``````=IFERROR(VLOOKUP(TEXT(H2,"00000"),'Lookup Tables'!A:D,4,FALSE),"")
``````
VLOOKUP_b.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Finance AnalystCommented:
In the sample file the format of the zip codes in the two sheets are different.

Sheet1 has zip code as a true number, Lookup has them as text.

The text conversion as suggested by Ryan will resolve it.

Alternatively, change the entries on the lookup sheet to numbers.
Author Commented:
Thank you.
Author Commented:
Additionally - in the research I did - I did not come across examples that had 'TEXT' or '00000' in the formula.

Do you know of a good resource that will assist with the VLOOKUP formula?

Thank you.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.