vlookup fails, and previous ones also now fail!

[KateB]

I sent a file to a colleague that has several vlookups in it so that she
could adapt it for her reports. For some reason the vlookup has stopped
working (it was OK when she first started changing values). For example, the
first vlookup is =VLOOKUP(U4,'Audit tool'!$C$1:$F$514,2,0). In cell U4 is the
text 1.1, and the vlookup returns the value appropriate from the audit tool
sheet. In the next row is 1.2 and so on. If I change cell U4 to 1.2, it
returns #NA, and if I retype 1.1 it still won't return a value. I opened
another spreadsheet with lookups in and the same things happens.

I know the values are correct, no blanks, the vlookup string is correct, I
even checked the tools ... options...calculations and its set to automatic.
Can anyone suggest what is happening? I can only assume it is something from
my colleague's set up / computer that has maybe corrupted this file and is
also causing my lookups (including the original one I sent her) to fail. I
tried rebooting too and that didn't help either! I have tried recreating the
lookups in a new file and they still won't work.

HELP!!

Kate

 

[Paul C]

Check the formatting on U4 and your data. Text and numbers get treated
differently.

My guess is that either when you typed 1.2 Excel is treating it as a number
and not text.

Confirm that your data is in fact text and not numbers also.

Try formatting U4 as Text or changing the formula to =VLOOKUP("1.2",'Audit
tool'!$C$1:$F$514,2,0). If the formula either of these work than the issue
is related to the text/number format.

 

[KateB]

Hi Paul,

I checked your suggestion (using speech marks around the number instead of
using the cell reference in the vlookup) and that worked, as did typing a
single quote before the number. The format was OK before so I don't
understand why it is changing on that cell? I've tried copying the format
from a cell that currently works and that has no effect either!

Also, there are over 300 cells - I really don't want to re-type all the
lookups instead of using the cell reference, or have to type a single quote
before the number in them all to force it to recognise as text! Is this an
excel 'bug' is there an easy solution?

Many thanks for your help,

Kate

 

[Manuel]

Convert the column stored as text to a number by using the VALUE function,
i.e.:

=VALUE(A1)

Or convert the column stored as a number to text using the TEXT function,
i.e.:

=TEXT(A1,"#0.0#")

Add additional poud signs (#) to account for additional places either in
front or behind the decimal.

Manuel