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

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)


--
Regards,

Peo Sjoblom


"Laury" wrote in message
...
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

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

"Laury" wrote in message
...
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

Thanks - the index and match did the trick!

"Peo Sjoblom" wrote:

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)


--
Regards,

Peo Sjoblom


"Laury" wrote in message
...
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