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