VLOOKUP Coming back #N/A

[PrintPostage]

So. Here is my dilemma.....

All of my columns were returning #N/A, I figured out I had to convert my lookup value to numbers, so I did that. Hurrah, it worked, or so I thought. I scrolled down and only half the values populated, the rest are still #N/A.

Now some of my lookup values in the column are pure numbers, the other half start with a letter. The ones with the letters are returning the #N/A. How do I fix this? Can a VLOOKUP not continue to look up the value if it changes formats?

Example:

5739218 = Results OK
A48589 = #N/A

Any advice? Thanks a bunch everyone.

 

[philwojo]

What is your vlookup, how about you post that and a sample of your data.

 

[PrintPostage]

Looking for the element number:
[TABLE="width: 1307"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Element[/TD]
[TD]Cost Center[/TD]
[TD]Payment[/TD]
[TD] element descr.[/TD]
[TD]Name[/TD]
[TD] offsetting account[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]564125[/TD]
[TD="align: right"]12356[/TD]
[TD]2,563.00[/TD]
[TD]Materials[/TD]
[TD][/TD]
[TD]Supply 1[/TD]
[TD="align: center"]Business Expense[/TD]
[/TR]
[TR]
[TD]564126[/TD]
[TD="align: right"]155[/TD]
[TD]155.00[/TD]
[TD]Materials[/TD]
[TD][/TD]
[TD]Supply 2[/TD]
[TD="align: center"]Business Expense[/TD]
[/TR]
[TR]
[TD]A47871[/TD]
[TD="align: right"]4588[/TD]
[TD]585.00[/TD]
[TD]Materials[/TD]
[TD][/TD]
[TD]Supply 3[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]M43922[/TD]
[TD="align: right"]115616[/TD]
[TD]51.00[/TD]
[TD]Materials[/TD]
[TD][/TD]
[TD]Supply 4[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]483748[/TD]
[TD="align: right"]15615[/TD]
[TD]56.00[/TD]
[TD]Materials[/TD]
[TD][/TD]
[TD]Supply 5[/TD]
[TD="align: center"]Business Expense[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


And trying to gather the category from this sheet:
[TABLE="width: 576"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Element[/TD]
[TD] Description [/TD]
[TD] Category [/TD]
[TD] Sub-Category [/TD]
[/TR]
[TR]
[TD="align: right"]5454[/TD]
[TD]Debt[/TD]
[TD] Business Expense [/TD]
[TD] Bad Debt [/TD]
[/TR]
[TR]
[TD="align: right"]5545[/TD]
[TD]Storeroom[/TD]
[TD] Material [/TD]
[TD] Material [/TD]
[/TR]
[TR]
[TD]A4433[/TD]
[TD]Materials[/TD]
[TD] Material [/TD]
[TD] Material [/TD]
[/TR]
[TR]
[TD="align: right"]493943[/TD]
[TD] NonStock Material [/TD]
[TD] Material [/TD]
[TD] Material [/TD]
[/TR]
[TR]
[TD="align: right"]5101750[/TD]
[TD] N-Stk Mtl Nonstorerm [/TD]
[TD] Material [/TD]
[TD] Material [/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I'm using:
=VLOOKUP(A2,Sheet2!$A$1:$D$101,3,0)

It's only pulling data which has an element number of only numbers, the element number that starts with an "A" or any other letter, it's pulling N/A. Even if I sort it from ascending ---> descending, it's giving the same result.

Any thoughts?

 

[Joe4]

VLOOKUP only works if the value you are looking up and the list you are matching on are the same data-type.
So they either both need to be Text, or they both need to be Numeric. You cannot compare Text to Numeric (the number 15 will not match up with a text entry of 15).
Since some of your entries have letters, it is probably best to make them all Text.

 

[PrintPostage]

VLOOKUP only works if the value you are looking up and the list you are matching on are the same data-type.
So they either both need to be Text, or they both need to be Numeric. You cannot compare Text to Numeric (the number 15 will not match up with a text entry of 15).
Since some of your entries have letters, it is probably best to make them all Text.

When I change them to all text, or all number, the results are still the same. Any tips?

 

[Joe4]

When I change them to all text, or all number, the results are still the same. Any tips?

HOW are you changing them all to Text?
Changing their format isn't enough. Then values need to "re-entered".
One easy way of doing that is to select the entire column, go a Text to Columns, and select the "Text" option from Step 3.

 

[BGY23]

For the vlookup to work both sets ofvalues in the lookup column and the lookup table have to be identical. To enable you to do this you can insert a column to the left of the columns you want to use on sheet 1 and sheet 2. In a cell adjacent to the first lookup value type

=Trim(Text($b4,"#")

The formula assumes your data starts on row 4 and your lookup values are now in column B. What the formula does is turn whatever is in B4 into text, even if it is a numeric value. The trim will remove any leading or trailing spaces (these tend to occur if you import the data from another program and can't be seen). Do the same on sheet 2 and you should have 2 colums where the values appear as text in the same format.

Things that may still give N/A#

You may have layout issues i.e A 4413 on sheet 1 and A4413 on sheet 2 or even A 4413 on sheet 2.
The lookup value isn't on sheet 2.


You should also be aware that if there are duplicate values in sheet 2 a vlookup will only return the result for the first instance in all cases.

Good luck.