Vlookup when lookup criteria is not an exact match

[jtoy]

I realize the formula solution is probably not a vlookup function, but I'm
trying combinations of MATCH, SEARCH, ISNUMBER, INDEX, and still not getting
the right result.

I've got 2 worksheets.

Worksheet 1, column C has a 15-digit ID like 02i40000001fpv0.
Worksheet 2, column A has an 18-digit ID. The first 15 of the 18 are the
same as above, but + an extra 3 numbers added at the end.

I need to find the value in Worksheet 1 C2 in Worksheet column A, and return
the 18 digit value in column A.

Thanks!

 

[Pete_UK]

You can use wildcards with VLOOKUP, so try this:

=VLOOKUP(Sheet1!C2&"*",Sheet2!A:A,1,0)

Hope this helps.

Pete

 

[Jacob Skaria]

Try

=INDEX(Sheet2!A:A,MATCH(C1&"*",Sheet2!A:A,0))

'error handled
=IF(ISNA(MATCH(C1&"*",Sheet2!A:A,0)),"",
INDEX(Sheet2!A:A,MATCH(C1&"*",Sheet2!A:A,0)))


If this post helps click Yes