Match 9 to 11 digits - vlookup

[Guest]

I have a list of 9 digit numbers (missing the end 2). I need to lookup the
closest match in a list of 11 digit numbers. I tried vlookup but it pulls in
the value above the one that I want. How do I make it find the one with all
9 digits matching?
EX:

List1 List2
300005555 30000555201 - vlookup pulls in this one
30000555502 - i want this one

 

[Peo Sjoblom]

Maybe this will work

=INDEX(B2:B20,MATCH(TEXT(C1,"@"),LEFT(A2:A20,9),0))

entered with ctrl + shift & enter

replacing


=VLOOKUP(C1,A2:B20,2,TRUE)

 

[Bob Phillips]

=INDEX(K1:K20,MATCH(TRUE,300005555=--LEFT(J1:J20,9),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks - the index and match did the trick!

Maybe this will work

=INDEX(B2:B20,MATCH(TEXT(C1,"@"),LEFT(A2:A20,9),0))

entered with ctrl + shift & enter

replacing


=VLOOKUP(C1,A2:B20,2,TRUE)