Match 9 to 11 digits - vlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
=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)
 
Thanks - the index and match did the trick!

Peo Sjoblom said:
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)
Click to expand...
 
Back
Top