Vlookup text with wildcards into text ranges

[nhinx]

Dear Excel Expert,

I been searching for Vlookup function in google for the partial text with a wild cards but it always give me the formula looking for a partial text looking up into ranges with wild cards. My question is vice versa. Example I need to Vlookup the certain text with wild card below

A	B	C	D
Dog		SmallDogOnly	2
BigCatOnly		Cat	10

<tbody>
</tbody>

Dog	=Vlookup("*" & A1 "*",Sheet1!A1:D3,4,False)

<tbody>
</tbody>

The formula above works fine and the answer is 2

BigCatOnly

=Vlookup(A1,Sheet1!A1:D3,4,False)

<tbody>
</tbody>

How can I express the Vlookup formula above without changing A1 into a word "Cat"

Thanks

 

[steve the fish]

This array formula:

=INDEX($D$1:$D$2,MATCH(TRUE,ISNUMBER(SEARCH($C$1:$C$2,A2)),0))

Enter it CTRL-SHIFT-ENTER

Bear in mind it can produce unexpected results depending on the data. If you want it case sensitive:

=INDEX($D$1:$D$2,MATCH(TRUE,ISNUMBER(FIND($C$1:$C$2,A2)),0))