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

"Dave Peterson" wrote:

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.

checkQ wrote:

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

"Dave Peterson" wrote:

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).



Pete_UK wrote:

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:D, 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

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


--

Dave Peterson