Vlookup problems in Excel 1

[cciitman]

I have a vlookup() function that uses a Company Name as a lookup value, checks a table for the same company and pulls some sales data from that table. Seems easy enough, right?

Problem is I'm getting only about 10 returns where I should be getting about 500. The other 490 are giving me #N/A returns. The company names in both tables are identical in spelling, capitalization, and punctuation. The lookup table is sorted by customer name, both the "customer name" collumns in the primary table and the sales data table are formated as text (some of the companies have numbers in their names), but still I get these errors in the formula return.

Anyone have any other ideas I may be missing?

Here's the formula in case it helps:
=vlookup(A2, Sales!$A$2:$E$568, 5, False)

 

[DaleWatson123321]

cciitman,

I've tested out your scenario and can't find any problem, but I do have a suggestion as to the cause of your problem.

First, a couple of points:

1) Appreciate that by using the "False" argument, this means the Company Names do NOT have to be in any particular order.

2) Having numbers within a Company Name does NOT cause a problem.

Now for my "suspicion" as to the cause of the problem. There could be one of two possibilities...

1) There is one or more <space> characters at the far right side of the Company Names in your Table.

2) There is a &quot;non-visible&quot; character - other than the <space> character - at the far right side of the Company Names in your Table. An example of such a character... Hold down the <Alt> key while you enter the number 0160 on the number pad. What you'll get will &quot;look&quot; like the <space> character, but it's NOT the same.

While you say that the names are &quot;identical&quot;, the only 100 percent way of making certain they are identical, is to copy one of the names from the Table to the cell in Column A, being reference by a lookup formula currently returing #N/A.

If it happens that your names have trailing <space> characters, then there's a fairly simple solution. Use the TRIM function in an adjacent column, and copy it down for all your names. Convert the formulas to values, and copy the names back to the proper location in your table.

If that other character - created by <ALT> 0160 - happens to be at the end of your Company Names, then the TRIM function will NOT work. So in this case, you would have to use Search and Replace - <Control> H . Under &quot;Find what&quot;, enter the <Alt> 0160, and leave &quot;Replace with&quot; blank.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[cciitman]

Trim function did the trick. Thanks for the help, Dale.

Marc

 

[tastie]

Hi
I have had this problem before check:

1. That in the look up table the company names are in descending order.
2. That none of the company names are repeated - the fist one will be picked and if this is a blank or does not match what is being looked for then the result will be N/A

I know this is simple - I'm not a technical expert but this is a problem that I have had in the past.