Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange "N/A" Problem
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Problem with "On error resume next" with "custom VLookup" | Excel Programming | |||
strange "subscript out of range" error! | Excel Programming |