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[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD][/TD]
[TD]SmallDogOnly[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BigCatOnly[/TD]
[TD][/TD]
[TD]Cat[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dog[/TD]
[TD]=Vlookup("*" & A1 "*",Sheet1!A1:D3,4,False)[/TD]
[/TR]
</tbody>[/TABLE]
The formula above works fine and the answer is 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]BigCatOnly[/TD]
[TD]=Vlookup(A1,Sheet1!A1:D3,4,False)[/TD]
[/TR]
</tbody>[/TABLE]

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))