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.

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!
==============================================

"tbennett" wrote in message
...
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.

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" wrote:

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!
==============================================

"tbennett" wrote in message
...
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.

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!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tbennett" wrote in message
...
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" wrote:

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!
==============================================

"tbennett" wrote in message
...
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.