and the two values dont match.
1028600680B vs 1102900068
--
paul

remove nospam for email addy!



"Dave Peterson" wrote:

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.




checkQ wrote:

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


Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,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?.




--

Dave Peterson