vlookup returning #n/a

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I am having a problem with getting vlookup to return the
correct value. The spreadsheet that contains the lookup
value is imported from Access. The lookup table is in a
second workbook.

When I enter the formula initially, I get #N/A. All I have
to do to get the correct value from the lookup table is to
select the lookup value on the original sheet, press F2,
then enter. Suddenly, the #N/A changes to the correct
value. However, this would be a rather time consuming fix
since I would have to manually edit 1000 rows.

Any suggestions?
 
try this
Sub FixRangeValues()
For Each C In Selection
C.Value = Format(C, "00")
Next
End Sub
 
Diane

I would suggest that the values you get from Access are brought in as Text
rather than Numbers. That's why the F2>Enter works. It changes the value
back to a real Number.

Copy an empty cell. Select your range of "values" and Paste
Special>Add>OK>Esc.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
So what was the answer? Nice to share so the rest of us can learn also.

Thanks, Gord
 
Hi Diane,

Just wondering if you could share your resolution to this
problem with me, or the site you found it on, since I am
having the exact same issue.

Thanks!
Corina
 
Hi,

I guess that the problem arises because excel just recognised the
imported data as text format even though the format is already set as
"General" or "Number".

I just solve this problem by adding a new column and adding 1 and
subtracting 1 (i.e. +1-1) to the original value. The effect is to force
excel to calculate the cell value and make it a "calculation enabled"
cell. Then u can make any others calculation.

Actually, I don't think it's the best solution but it's the only
solution that I can think of. It's appreciated that if anyone could give
a better answer.

Thanks!

TC.
 
TC

No need for an helper column.

Copy an empty cell. Select your range you want to convert. Paste
Special>Add>OK>Esc.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
If you use a helper column (which you don't need -- see Gord's reply), there's no need to add 1,
then subtract 1 -- you can simply add 0.
 
Back
Top