vlookup argument type

[Guest]

I have two instances using vlookup in which I look up the value '1234' in
table array. Vlookup returns #NA in case 1, and the correct result in case
2. In case 1, the value argument is a cell reference to '1234' and has a
TYPE value of 1. In case 2, value is a cell reference to '1234' and has a
TYPE value of 2. The TYPE value in the lookup column of the lookup array is
2.

Is the wrong data type the cause for the #NA result and, if so, how do I
change it? Formatting the lookup value as text doesn't do the trick.

 

[RagDyer]

Just changing the format to Text doesn't change the value to Text.
You can verify that by using your Type() function, can't you?

BUT, if you select the "number" cell *after* changing it to Text, hit <F2>,
then <Enter>, you'll see that the Type() function will return a "2".

You could do this to all your values, going either way, all to Text, or all
to a Number.

Just how many would you have to change?

I would say that if the data looks like numbers, it'd probably be wiser to
make them all numbers.

You could select a new, unused cell, that has the default format of
"General", and right click in it and choose "Copy".
Select all your numbers that are, or might be Text, and right click in that
selection.
Choose "Paste Special", and click on "Add", then <OK>.

Now, all your values are true numbers, and all your formulas should work.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Guest]

Thanks. I changed the type successfully per your advice, yet the vlookup
still returns #NA. Perhaps it's not the data type afterall. When I copy the
value from the lookup array into the cell containing the lookup value, the
lookup works. Are there other hidden attributes of the cell that affect the
success of vlookup?

 

[RagDyer]

Are you saying that all your data is now *verified* as being numeric and
you're still having this problem?

AFAIK, if a value is numeric, there cannot be any invisible, imbedded
characters in the cell, otherwise it would be converted to text.

Double check some of your problem cells and make double sure that they *are*
all numeric.

Try using
=ISNUMBER()
and make sure everything equates to TRUE!