Excel 2000 VLOOKUP returns #N/A unless press F2 return on source?
 

In Excel 2000 I am using the following to get data from another sheet. Also I
am using it in other cases to get data from another workbook but the issue is
the same.

=VLOOKUP(A2,'BV data'!A:B,2,FALSE)

This returns '#N/A'. Clearly it is not finding a value. If however, I go to
the source cell (key) and press F2 to edit the cell and then press return
without making ANY changes, the formula works! With a lot of data this is a
completely impractical workaround.

Changing the format to number, cutting and pasting as values... all of this
doesn't work. It's not a precision issue as turning on the 'as displayed'
function on the Calculation tab under options doesn't help.

It's as if the number is appearing in the cell but its not really
reginstered there as far as Excel is concerned.

Really causing problems. Would really appreciate any help. Thanks in advance.

Colin

S
Have solved it but still confused. I used 'Format/cells' to change the keys
to text. No good. If I use 'Data/Columns to Text' then all is OK.

What's the difference? Is the former just for display and the latter for how
the data is displayed?

Colin

"Colin" wrote:

In Excel 2000 I am using the following to get data from another sheet. Also I
am using it in other cases to get data from another workbook but the issue is
the same.

=VLOOKUP(A2,'BV data'!A:B,2,FALSE)

This returns '#N/A'. Clearly it is not finding a value. If however, I go to
the source cell (key) and press F2 to edit the cell and then press return
without making ANY changes, the formula works! With a lot of data this is a
completely impractical workaround.

Changing the format to number, cutting and pasting as values... all of this
doesn't work. It's not a precision issue as turning on the 'as displayed'
function on the Calculation tab under options doesn't help.

It's as if the number is appearing in the cell but its not really
reginstered there as far as Excel is concerned.

Really causing problems. Would really appreciate any help. Thanks in advance.

Colin

The general rule is that changing the formatting of an existing # to Text
does not convert it to text. A digit or series of digits entered into a cell
ALREADY formatted as text are treated as text. That is why editing a value
after you had changed the format worked - Excel saw that as a new entry.

Text to Columns worked because, basically, Excel re-entered all the values
for you.

The other approach would be to convert the left-most column of your lookup
table from text to numbers. A simple way is to copy an EMPTY cell, select
the data in the left-most column, and use Edit-Paste Special-Values-Add.
Excel will then convert all the text values to numeric

"Colin" wrote:

S
Have solved it but still confused. I used 'Format/cells' to change the keys
to text. No good. If I use 'Data/Columns to Text' then all is OK.

What's the difference? Is the former just for display and the latter for how
the data is displayed?

Colin

"Colin" wrote:

In Excel 2000 I am using the following to get data from another sheet. Also I
am using it in other cases to get data from another workbook but the issue is
the same.

=VLOOKUP(A2,'BV data'!A:B,2,FALSE)

This returns '#N/A'. Clearly it is not finding a value. If however, I go to
the source cell (key) and press F2 to edit the cell and then press return
without making ANY changes, the formula works! With a lot of data this is a
completely impractical workaround.

Changing the format to number, cutting and pasting as values... all of this
doesn't work. It's not a precision issue as turning on the 'as displayed'
function on the Calculation tab under options doesn't help.

It's as if the number is appearing in the cell but its not really
reginstered there as far as Excel is concerned.

Really causing problems. Would really appreciate any help. Thanks in advance.

Colin

I run into similar issues with data that gets recreated regularly, so I
change between text and numbers within my vlookup function. Instead of
vlookup(a2, ...), I might use vlookup(trim(a2),...) to force my keys to be
treated as text or vlookup(value(a2),...) if I need them treated as numbers.


"Duke Carey" wrote:

The general rule is that changing the formatting of an existing # to Text
does not convert it to text. A digit or series of digits entered into a cell
ALREADY formatted as text are treated as text. That is why editing a value
after you had changed the format worked - Excel saw that as a new entry.

Text to Columns worked because, basically, Excel re-entered all the values
for you.

The other approach would be to convert the left-most column of your lookup
table from text to numbers. A simple way is to copy an EMPTY cell, select
the data in the left-most column, and use Edit-Paste Special-Values-Add.
Excel will then convert all the text values to numeric

"Colin" wrote:

S
Have solved it but still confused. I used 'Format/cells' to change the keys
to text. No good. If I use 'Data/Columns to Text' then all is OK.

What's the difference? Is the former just for display and the latter for how
the data is displayed?

Colin

"Colin" wrote:

In Excel 2000 I am using the following to get data from another sheet. Also I
am using it in other cases to get data from another workbook but the issue is
the same.

=VLOOKUP(A2,'BV data'!A:B,2,FALSE)

This returns '#N/A'. Clearly it is not finding a value. If however, I go to
the source cell (key) and press F2 to edit the cell and then press return
without making ANY changes, the formula works! With a lot of data this is a
completely impractical workaround.

Changing the format to number, cutting and pasting as values... all of this
doesn't work. It's not a precision issue as turning on the 'as displayed'
function on the Calculation tab under options doesn't help.

It's as if the number is appearing in the cell but its not really
reginstered there as far as Excel is concerned.

Really causing problems. Would really appreciate any help. Thanks in advance.

Colin