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
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

exp vgAuthor Commented:
I also tried the following without success:

=VLOOKUP(H2,'Lookup Tables'!A$2:B$410,2,FALSE)
Subodh Tiwari (Neeraj)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),"")

Open in new window

Subodh Tiwari (Neeraj)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.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

exp vgAuthor 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.
exp vgAuthor Commented:
Basically - I believe he blanks are there since the formula is read as an error for the entire Columns K.
Saurabh Singh TeotiaCommented:
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...
exp vgAuthor Commented:
Please find this attached. The VLOOKUP formula is in column K on the first tab.
VLOOKUP.xlsx
Ryan ChongBusiness 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), "")

Open in new window

column L, place this formula at cell L2:
=IFERROR(VLOOKUP(TEXT(H2,"00000"),'Lookup Tables'!A:D,3,FALSE),"")

Open in new window

column M, place this formula at cell M2:
=IFERROR(VLOOKUP(TEXT(H2,"00000"),'Lookup Tables'!A:D,4,FALSE),"")

Open in new window

VLOOKUP_b.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Rob HensonFinance 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.
exp vgAuthor Commented:
Thank you.
exp vgAuthor 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.