Lookup Partial Match of Characters

Here's an approach that works by finding the maximum length of a string that matches (by looking at 3, 4, 5, 6 and 7 characters) and then using that for the lookup value:

=IFERROR(VLOOKUP(LEFT(A3,MAX(IF(COUNTIF(Sheet2!A:A,LEFT(A3,3)&"*"),3,0),IF(COUNTIF(Sheet2!A:A,LEFT(A3,4)&"*"),4,0),IF(COUNTIF(Sheet2!A:A,LEFT(A3,5)&"*"),5,0),IF(COUNTIF(Sheet2!A:A,LEFT(A3,6)&"*"),6,0),IF(COUNTIF(Sheet2!A:A,LEFT(A3,7)&"*"),7,0)))&"*",Sheet2!A:B,2,0),"")

though it might be easier to follow if I lay it out like this:

=IFERROR(VLOOKUP(LEFT(A3,MAX(
IF(COUNTIF(Sheet2!A:A,LEFT(A3,3)&"*"),3,0),
IF(COUNTIF(Sheet2!A:A,LEFT(A3,4)&"*"),4,0),
IF(COUNTIF(Sheet2!A:A,LEFT(A3,5)&"*"),5,0),
IF(COUNTIF(Sheet2!A:A,LEFT(A3,6)&"*"),6,0),
IF(COUNTIF(Sheet2!A:A,LEFT(A3,7)&"*"),7,0)))
&"*",Sheet2!A:B,2,0),"")

the 3 to 7 characters are arbitrary, but could be extended for as many characters as you like. You could probably reduce the multiple-IFs by using an array formula. I used Glenn's attachment (saves me typing it up from scratch) and put the formula in cell D3 then copied it down.

Hope this helps.

Pete

Thank you all!!