Solved

Microsoft Excel 2013 VLOOKUP Return Error From Lookup Sheet Duplicate Values

Posted on 2014-03-12
2
601 Views
Last Modified: 2014-03-13
I have a file list of about 200 prospective clients which I've gathered all contact info, minus the state for each unique prospect (Did not have a state column at all during initial contact information gathering; now decided we do need the state). In the midst of attempting to avoid any notable time spent manually adding the states via typing them in, I found an excel file that provides a comprehensive zip code-state listing, where I had hoped to link a VLOOKUP formula in my state column. In using this list (the V-lookup uses the already-known prospect zip code column), I realized that I was getting #N/A returns. This is likely due to the fact that excel does not like your lookup columns to have duplicate values. Is there any short way of fixing this, or a somewhat simple workaround? I have two screenshots, and attached the .xlsx file in reference. Thanks for any help in advance.
excel.png
excel-1.png
REVISED-LEAD-list.xlsx
0
Comment
Question by:Lee Richardson
2 Comments
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
Comment Utility
You zip codes on sheet Revised are a mix of text and numbers from different sheets (and some are typed), and likewise on sheet State where some are text and some are numbers.

Changes all zip codes on sheet State to be numbers, and changed the formula in column L to look for numbers.
REVISED-LEAD-list.xlsx
0
 

Author Closing Comment

by:Lee Richardson
Comment Utility
Well done. Thank you, very much!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now