Strange "N/A" Problem
 

Hi,

I am currently using the VLOOKUP function to look up company names based upon
account numbers. Occasionally the VLOOKUP function only returns a "N/A" value
for any account number used as a search item. For example, if I have a list
of 20 account numbers, sometimes all 20 searches will return N/A. Examination
of the list used to generate the account names reveals that some of the 20
account numbers are on the list and they do have account names. The VLOOKUP
function should retrieve the names but it does not. I have looked deeper in
the problem but as of now I do not see any pattern which might explain the
problem. For example, if I cannot find an account name and search by typing in
the number in the cell the VLOOKUP used to search for the account number (
instead of relying upon the original account number) the VLOOKUP does produce
the account name. The number in the Excel list does not work. The exact number
typed in by me does. Sometimes the format of the two account numbers is
different and the VLOOKUP does not work. Sometimes adding a zero to a list of
numbers to make them appear exactly identical to the list of numbers which also
has the account names works. However, sometimes the format is the same and the
VLOOKUP does not produce an account name when it should. I could go on with
examples. The same thing happens to someone else using the same type of
account number data but using a different computer. It appears to be a
problem related to the data we are using.

I would like to know what I can to do prevent this from happening. One thing I
thought I would do is to set the format of both lists of account numbers to
the same format with the same number of characters. because it would seem that
the problem lies in the account numbers but I don't know if that would help and
do not know how to do that not what format to use. ( But, as I said, sometimes
the formats appear to be same and the function still does not work.) Any
suggestion to prevent this from happening would be appreciated.

Thanks

Strange "N/A" Problem
 

JimFor,

A couple of things you can try . . .

(1) Check to see if the account formatting in your source account numbers
is the same as the account formatting in your lookup account numbers. For
example, if your lookup account number is in cell A1 and your database
account numbers are in cell B1, use a formula like "=A1=B1" to tell if the
formatting is the same. If it is, the formula will return "True". If not,
the formula will return "False". Change your formatting until you are
getting all "True" values.

(2) Use Excel's TRIM function on the account numbers to make sure you are
not picking up any "phantom" spacing. For example, if you have an account
number in cell A1, add the formula "=TRIM(A1)" to cell B1. Copy the formula
all the way down column B to include all of your account numbers. Then, copy
the values in column B back into column A. Delete column B. I've often
found that this will work especially if data has been downloaded from a
mainframe into Excel.

----
Regards,
John Mansfield
http://www.pdbook.com

"JimFor" wrote:

Hi,

I am currently using the VLOOKUP function to look up company names based upon
account numbers. Occasionally the VLOOKUP function only returns a "N/A" value
for any account number used as a search item. For example, if I have a list
of 20 account numbers, sometimes all 20 searches will return N/A. Examination
of the list used to generate the account names reveals that some of the 20
account numbers are on the list and they do have account names. The VLOOKUP
function should retrieve the names but it does not. I have looked deeper in
the problem but as of now I do not see any pattern which might explain the
problem. For example, if I cannot find an account name and search by typing in
the number in the cell the VLOOKUP used to search for the account number (
instead of relying upon the original account number) the VLOOKUP does produce
the account name. The number in the Excel list does not work. The exact number
typed in by me does. Sometimes the format of the two account numbers is
different and the VLOOKUP does not work. Sometimes adding a zero to a list of
numbers to make them appear exactly identical to the list of numbers which also
has the account names works. However, sometimes the format is the same and the
VLOOKUP does not produce an account name when it should. I could go on with
examples. The same thing happens to someone else using the same type of
account number data but using a different computer. It appears to be a
problem related to the data we are using.

I would like to know what I can to do prevent this from happening. One thing I
thought I would do is to set the format of both lists of account numbers to
the same format with the same number of characters. because it would seem that
the problem lies in the account numbers but I don't know if that would help and
do not know how to do that not what format to use. ( But, as I said, sometimes
the formats appear to be same and the function still does not work.) Any
suggestion to prevent this from happening would be appreciated.

Thanks

Strange "N/A" Problem
 

The number 123 is not the same as the string "123".

Vlookup does not see them as the same. It appears you have a mismatch of
this type between the item being searched for and the range being searched.

--
Regards,
Tom Ogilvy

"JimFor" wrote in message
...
Hi,

I am currently using the VLOOKUP function to look up company names based
upon
account numbers. Occasionally the VLOOKUP function only returns a "N/A"
value
for any account number used as a search item. For example, if I have a
list
of 20 account numbers, sometimes all 20 searches will return N/A.
Examination
of the list used to generate the account names reveals that some of the 20
account numbers are on the list and they do have account names. The
VLOOKUP
function should retrieve the names but it does not. I have looked deeper
in
the problem but as of now I do not see any pattern which might explain the
problem. For example, if I cannot find an account name and search by
typing in
the number in the cell the VLOOKUP used to search for the account number (
instead of relying upon the original account number) the VLOOKUP does
produce
the account name. The number in the Excel list does not work. The exact
number
typed in by me does. Sometimes the format of the two account numbers is
different and the VLOOKUP does not work. Sometimes adding a zero to a list
of
numbers to make them appear exactly identical to the list of numbers which
also
has the account names works. However, sometimes the format is the same
and the
VLOOKUP does not produce an account name when it should. I could go on
with
examples. The same thing happens to someone else using the same type of
account number data but using a different computer. It appears to be a
problem related to the data we are using.

I would like to know what I can to do prevent this from happening. One
thing I
thought I would do is to set the format of both lists of account numbers
to
the same format with the same number of characters. because it would seem
that
the problem lies in the account numbers but I don't know if that would
help and
do not know how to do that not what format to use. ( But, as I said,
sometimes
the formats appear to be same and the function still does not work.)
Any
suggestion to prevent this from happening would be appreciated.

Thanks

Strange "N/A" Problem
 

On 31 Dec 2004 01:12:42 GMT, OSPAM (JimFor) wrote:

Hi,


I would like to know what I can to do prevent this from happening. One thing I
thought I would do is to set the format of both lists of account numbers to
the same format with the same number of characters. because it would seem that
the problem lies in the account numbers but I don't know if that would help and
do not know how to do that not what format to use. ( But, as I said, sometimes
the formats appear to be same and the function still does not work.) Any
suggestion to prevent this from happening would be appreciated.

Thanks

I'm not certain I understand everything you've posted. However, it sounds as
if, in some indeterminate fashion, your account numbers (and possibly also the
values you use for a lookup key), although they always look like numbers,
occasionally are entered as text.

If that is the case, if the LookUp key is text, it will not find a Number in
the data table, even if they appear to be the same.

So if that is your problem, and if there is no way to 'force' all the entries
to be one or the other, then you could force it in the formula.

For example, if your Account Numbers are in a one column array named AcctNum
and the corresponding Account Names are in a one column array named AcctName,
then the following array formula will give you the Account Name that
corresponds to the account number in LookUp:

=INDEX(AcctName,MATCH(VALUE(LookUp),VALUE(AcctNum) ,0))

To enter an array formula, hold down <ctrl<shift while hitting <enter (after
you've typed in or pasted in the formula). XL will place braces {...} around
the formula.


--ron