vlookup function

[Miranda]

The following problems are not occurring in all of the
cells with formulas even though the functions are exactly
the same and referencing the correct cells. I apologize
for not having any examples but there is no way to attach
the spreadsheets to this message. I have done numerous
searches for this problem but have found no fix, just
workarounds. It appears to be a problem with versions
later than 5.0.

If you look at the Sheet1 tab, in cell J8 you will see
that the formula errored out (returns # N/A) meaning that
this item was not on the "lookup list". If you select
cell A8 (which the is the cell that J8 references), click
inside the contents box and hit the enter key, it finds
the match and the formula works. Notice that once you
click inside the cell contents and then move out, the
triagle appears indicating this cell is now formatted as
text, apparently the lookup range and the cell being
referenced must have the same format, even if the data is
exactly identical. The problem is that we are unable to
apply modifications of the format of cells which contain
existing data through any other method other than giving
the cell focus (you can highlight the column, row or
cell and change the cell format to text, and it makes no
difference until the cell gains focus) In the instances
where people are dealing with thousands of records and
some are reporting correctly, and others are incorrect,
and many users won't have time to click inside each cell
referenced to ensure that you are receiving the correct
data.

Another example to view of the above problem is found with
the ExcelProblem1.xls attachment. If you will note on row
5, 6, and 7 there is an error (# N/A) in columns b, c, and
d. if you will click inside the cell contents in the A
column for that row and hit enter, it will then lookup the
value. No changes what so ever.

The last example is a bit different, but my belief is that
it is probably also derived from the same culprit...
(whatever that is)
In the attachment ExcelProblem3.xls, cell B2 does not
recognize the formula. It is formatted as text, but Excel
should see the contents begin with the equal sign and
calculate the formula; regardless of what the format
properties are. If you delete the formula, change the
format to general and rekey any formula, it will work.
There happens to be a vlookup formula there now, but any
formula will not work until it is deleted and changed to a
general format.

I would appreciate if someone can provide some insight to
this problem.

Sincerely,
Miranda Laws

 

[Ken Wright]

You are quite correct when you say the formats must be the same, but it is not enough to simply
change the format using the Format menu. If you have a range of numbers that are text, just using
the format menu to change them to numeric will have no effect. You must change the format AND
coerce the data back to numeric by something like copying an empty cell, selecting the adta
affected and doing Edit / Paste Special / Add. This will not change the value of the data, as it
will be adding 0, but it will tell Excel that this data is to be treated as numbers and not text.

As far as the formula goes, you are again correct that the format of the cell must be general or
some other numeric format (ie not text), in order for it to work. Once you have formatted the
cells, simply select the range concerned and do Edit / replace / replacing = with = (yes they are
the same), which should coerce it back to a formula.