Vlookup based on "contains"? Is this possible?

[Echo_711]

Hi, I am looking for a formula that will run like a vlookup, but will look for a "contains" match rather than an exact "match". I need a formula that will grab the "base part plus ending" from list 1 and go search for the base part in List 2 and return the base part as the result.

List 1: Base numbers plus endings
List 2: Base numbers

[Olly]

Are all base part number 13 characters long?

[Jonmo1]

How about this in the Base + Ending sheet, B2 and filled down.

=LEFT(A2,13)

[shg]

In the examples, it's just the first 13 characters of the part number -- you don't need a lookup.

[Echo_711]

No, unfortunately there is no set character count for the base part. I was thinking that a "contains" formula may work, but I don't know if there is one.

To explain more: There are an untold mixture of base part numbers. If the base part number exists within the "base part + ending", I need to understand what that base part is.

[Jonmo1]

If you can sort the Base Table in ascending order, then try

=LOOKUP(A2,Base!$A$2:$A$9)

[Olly]

Or use a small UDF:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[Echo_711]

Olly, You got it! That's it!

Jonmo1, yours returned results that I could not determin.

[Jonmo1]

Jonmo1, yours returned results that I could not determin.

Did you sort the table on the Base page in Ascending Order?

Or are there sometimes no matching part # ?
In that case, the lookup would return incorrect results.

Try
=IFERROR(LOOKUP(2^15,SEARCH(Base!$A$2:$A$9,A2),Base!$A$2:$A$9),"")

Note, there cannot be any blanks in Base!$A$2:$A$9

[Echo_711]

Jonmo1! That's it also!!

Yes, there are sometimes with no matching part #'s and yes I did sort ascending.

This is great and works perfect as the UDF.

[Jonmo1]

You're welcome.