You'll struggle with VLookUp.

In the attached are two solutions because in your examples all the smaller strings are to be found at the beginning of the longer strings, but I've included a formula to look the smaller strings anywhere within the longer strings.

In column B is the formula to find the smaller strings anywhere within the longer strings:
In cell B1:
=IFERROR(INDEX(Sheet1!$B$1:$B$4,MATCH("*" & A1 & "*",Sheet1!$A$1:$A$4,0)),"")
copied down.
Note that cell B8 contains a value because it's looking for an empty string and finds it in the row 1.

To find the smaller strings only at the beginning of the longer strings, in C1 array-enter the following (array-entering means committing the formula to thesheet with Ctrl+Xhift+Enter, not just Enter):
=IFERROR(INDEX(Sheet1!$B$1:$B$4,MATCH(A1,LEFT(Shee t1!$A$1:$A$4,LEN(A1)),0)),"")
and copy down.

Attached Files

msofficeforums42148example.xlsx (10.4 KB, 13 views)