vlookup returns #N/A if value contains letters and range numbers

[Guest]

A B C
040W02193A 040W02193A0B 040W02193A
#N/A 1 1028600680B 1102900068


Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C,1,0)

The value that I am looking for is in the first 10 characters in column B
that match the first 10 characters in column C

The formula works for the first line but returns an #N/A for the second
line
I believe it is because my column B has the letter B in it and column C does
not have any alphabet. Does anyone know a formula to fix this?.

 

[Pete_UK]

I am surprised that this works at all, as your final parameter of the
VLOOKUP is zero, indicating that you want an exact match - you can't
use * as a wildcard. Also, your lookup array is C, but you only want
to return from column 1, so the array could be C:C.

The LEFT function will return a text value, so you must ensure that all
the entries in column C are text values - the way it stands, 1102900068
would be interpreted as numeric, so you won't find an exact match.
Highlight column C and click Format | Cells | Number (tab) and select
Text. Then change your formula to:

=VLOOKUP(LEFT(B1,10),C:C,1,0)

and ensure that all your values in column C are 10 characters long.

Hope this helps.

Pete

 

[Dave Peterson]

my guess...

The value in C2 is a real number--if you used:
=isnumber(c2)
in an empty cell, what gets returned?
I'm guessing True.

And =left() returns a string--and excel treats a string that looks like a number
differently from a that real number.

'123 <> 123
(text 123 <> number 123)

====
You have a couple of solutions. One is to convert all those values in column C
to text.

You can use a helper column (D??) and do something like:
=c1&""
and drag down.

Then select column D
edit|copy
select column C
edit|Paste special|Values
and delete column D (we're done with it).

Then format the "new" column C as Text so that any new entries/changes to
existing entries will be Text.

====

Another way is to use a different formula:

This array formula works ok for me:
=VLOOKUP(LEFT(B2,10)&"*",""&C1:C100,1,0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

====
Since you're actually returning the first column in the lookup range, =vlookup()
seems like an unusual choice. I think I'd use this array formula:

=INDEX(C1:C100,MATCH(LEFT(B2,10)&"*",""&C1:C100,0))

(still array entered)

====
One of the differences between these two alternative formulas is what is
returned. In the first (=vlookup()), a text value is returned.

In the second formula (=index(match()), whatever is in column C will be
returned--text or number.

 

[Dave Peterson]

You can use wildcards in =vlookup().

And if the values in column C are longer than 10 characters, this formula won't
work the way the OP intended (as I read it).

 

[Guest]

and the two values dont match.
1028600680B vs 1102900068

 

[Dave Peterson]

Since the original formula was using all of column C, I hoped that there was a
match later in the column. And that the OP just shortened the list of sample
data.

But I could be wrong.

 

[Guest]

Thanks Dave. I loved your last 2 formulas for the problem.
I was trying to subsitute C1:C100 with C:C but it would not work. As I copy
the formula down C1:C100 change to C2:c101,C3:c102 ect any solutions?
Mark

 

[Guest]

Thanks Pete,but I tried it but it did not work, I guess vlookup cannot return
values if one column contains numbers and a letter and the other only numbers

 

[Dave Peterson]

That's what this warning was for:
Adjust the range to match--but you can't use the whole column.
<vbg>

Choose a range large enough so you don't have to worry. Then make that
reference absolute:

=INDEX($C$1:$C$1000,MATCH(LEFT(B2,10)&"*",""&$C$1:$C$1000,0))

The larger the number of rows, the safer you'll be--but the slower the
recalculation, too. So use a number as large as you need, but no larger <vvbg>.

 

[Guest]

I't me again Pete I just saw in your reply that I would have to adjust the
range since it won't be able to use the whole column C;C. Thanks again

 

[Dave Peterson]

You're getting Pete/Peterson mixed up <bg>.

You have another followup.

 

[Guest]

Thanks Dave. I was at my wits end trying to figure out the problem. thanks a
million!