vlookup problem

[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

 

[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

 

[Jim Berglund]

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

Any other suggestions?

Jim

 

[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???

 

[Anne Troy]

I can't imagine why, Jim. Can you send your file?
************
Anne Troy
www.OfficeArticles.com