Finding a substring within a range

[Rizarita]

Hello. To keep it simple, if column A contains the letters A-C and column B contains strings, how do I return the correct letter (B) in the cell ??? (D1) when I type dog into C1 please? I believe I need to use an array formula containing SEARCH, INDEX and MATCH. I have to use a formula in this instance, VBA is no good. Can you help please?

[TABLE="width: 420"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]There's a cat in here somewhere[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]There's a dog in here somewhere[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]There's a fish in here somewhere[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much for your time.

 

[Rizarita]

Sorry that should say 'dog' in the illustration below the question - and I didn't know how to edit the post.

 

[Rick Rothstein]

Give this formula a try...

=INDEX($A$1:$A$3,MATCH("*"&C1&"*",$B$1:$B$3,0))

 

[Rizarita]

My word! I never knew you could use a wildcard in a match function! Thank you very much Rick.

 

[Rick Rothstein]

My word! I never knew you could use a wildcard in a match function! Thank you very much Rick.

If it helps any, here are the function that I am aware of (true for versions of Excel up to XL2010 which is my latest current version) that can make use of wildcards...

• SUMIF - all versions of Excel
• COUNTIF - all versions of Excel
• SEARCH - all versions of Excel
• MATCH - all versions of Excel
• HLOOKUP - all versions of Excel
• VLOOKUP - all versions of Excel
• SUMIFS - Excel 2007 and later
• COUNTIFS - Excel 2007 and later

 

[Rizarita]

Thank you very much. Very helpful. I will make good use of this.