Vlookup - Partial Match

[Jplace]

Im trying to find a way to lookup the "part" to the "list" although there is only a partial match and cannot find a way to do this.

Part	Partial Match	List
ABCATASD		CAT
BDOGD		DOG
ASCXCOW		COW
MOUSEAQ		MOUSE

<colgroup><col><col><col></colgroup><tbody>
</tbody>

If someone could offer a solution that would be greatly appriciated

thanks

 

[rayk8301]

try flipping the columns and using the below..not sure if it helps what you're trying to do because what you're looking for is on the same row of the part.

=VLOOKUP(LEFT(C2,SEARCH(C2,A2)),$C$2:$D$5,2)

 

[mole999]

This provides a guide > https://exceljet.net/formula/partial-match-with-vlookup

 

[inactiveuserps07]

@Jplace, this can definitely be done; but it's not clear whether you are trying to locate partials within wholes, or use the wholes to find the partials. And the approach would differ depending on which. It's also not clear what kind of result you want or where you want that result to be placed (i.e., where the formulas would go).

What would your ideal results look like? Please provide actual Column letters and Row numbers.

 

[Jplace]

A 1 Part	B Partial Match	C List
2 ABCATASD	CAT	CAT
3 BDOGD	DOG	DOG
4 ASCXCOW	COW	COW
5 MOUSEAQ	MOUSE	MOUSE

<tbody>
</tbody>

Hi @ErikTyler

Sorry i was in a rush when i made the above post. I'm looking to match the whole against the partial, I've tried using a vlookup with "*" but I can only see this working to match the partial against the whole and not the other way around. The result I'm looking for is in column "B" which would be returning the partial match against the whole.

Thanks for your help.

 

[inactiveuserps07]

@Jplace, I would suggest putting your match list (what is shown above in Column C) in Column A of its own tab (perhaps called "Legend"). The following formula assumes you will have done this and that your "Legend" tab list has a header (i.e., that the complete list begins in A2).

I don't know how many rows that legend might contain. I've included through A100 in my formula, but you can adjust according to the length of your actual list.

Lastly, this is an array formula, so it will need to be entered with Ctrl+Shift+Enter (not just Enter). You'll see curly brackets {} around the formula if you've done it correctly. Any time you might need to edit the formula, you'll need to be sure to confirm changes with Ctrl+Shift+Enter as well.

The formula would be placed in your main sheet in B2, then copy-dragged down as far as you need it to go:

=INDEX(Legend!$A$2:$A$100,MATCH(TRUE,ISNUMBER(SEARCH(Legend!$A$2:$A$100,A2)),0))

 

[mole999]

would you have cattle, instead of cow ?