vlookup problem

J

Jim Berglund

This one really blows me away!

I just reinstalled ExcelXP and created a worksheet to determine a state
value (D) from an area code(C) with the following columns:
A B C D
#N/A 405 201 NJ
#N/A 800 202 DC
#N/A 620 203 CT
#N/A 800 204 MB
#N/A 888 205 AL
#N/A 303 206 WA
#N/A 734 207 ME
#N/A 800 208 ID
#N/A 806 209 CA
#N/A 816 210 TX
#N/A 770 212 NY
#N/A 610 213 CA
#N/A 404 214 TX
#N/A 314 215 PA
#N/A 501 216 OH
#N/A 404 217 IL
#N/A 336 218 MN
#N/A 207 219 IN
#N/A 203 224 IL
#N/A 877 225 LA
#N/A 608 228 MS
#N/A 905 229 GA

....to 350 rows

The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
Everything is formatted as General (I tried number & Text as well)
It looks like something is wrong with the formula or all the data, but in
fact, 3 values work.

I've never seen this before. Doe you have any ideas on what might be wrong?

Thanks,
Jim
 
A

Anne Troy

Jim, copy a blank cell. Then select all your #N/As and hit Edit-->Paste
special-->Add. See if that forces Excel to see your column A data as
numbers. If that doesn't work, you might want to do it on B and/or C as
well.
************
Anne Troy
www.OfficeArticles.com
 
J

Jim Berglund

Nope! No joy!
I did get one cell to change from #NA to #VALUE, but that's all.

Any other suggestions?

Jim
 
D

Dave Peterson

Some more things to check...

First, I would think that your formula would be more like:

=vlookup(B1,$C$1:$D$350,2,false)

(You want an exact match)

If you can pick out the cell that contains the "match" in column C, you could
double check to see if the values are the same:

=b1=c###

If it comes back false, then there's a difference. Either number vs. text (like
Anne suggested) or maybe leading/trailing spaces???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP problem 4
VLOOKUP problem 3
vlookup problem 2
transpose address information for each customer number 2
vlookup problem 4
Text to Columns 1
Vlookup Problem 6
VlOOKUP/MATCH/INDEX 2

Top