vlookup problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Berglund - 28 Sep 2005 16:57 GMT

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

Reply to this Message

Anne Troy - 28 Sep 2005 17:01 GMT

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

> This one really blows me away!
>
[quoted text clipped - 36 lines]
> Thanks,
> Jim

Reply to this Message

Jim Berglund - 28 Sep 2005 17:33 GMT

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

Any other suggestions?

Jim

> 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
[quoted text clipped - 44 lines]
>> Thanks,
>> Jim

Reply to this Message

Anne Troy - 28 Sep 2005 17:45 GMT

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

> Nope! No joy!
> I did get one cell to change from #NA to #VALUE, but that's all.
[quoted text clipped - 51 lines]
>>> Thanks,
>>> Jim

Reply to this Message

Dave Peterson - 28 Sep 2005 17:39 GMT

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

> This one really blows me away!
>
[quoted text clipped - 35 lines]
> Thanks,
> Jim

Signature

Dave Peterson

Reply to this Message