Vlookup when lookup criteria is not an exact match
 

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!

Vlookup when lookup criteria is not an exact match
 

You can use wildcards with VLOOKUP, so try this:

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

Hope this helps.

Pete

On Nov 18, 5:01*pm, jtoy wrote:
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!

Vlookup when lookup criteria is not an exact match
 

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
---------------
Jacob Skaria


"jtoy" wrote:

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!