Index Match with wildcards

[DavidH56]

Hi All,

I'm having a problem getting index/match to work with wild cards, and I haven't come across a solution to my particular issue.

Here's a simplified version:

	A	B
1	Apples	Fruit
2	Pears	Fruit
3	Bananas	Fruit
4	Grapes	Fruit
5	Corn	Veg
6	Beets	Veg
7
8	Value to Look up	Result
9	White Corn	=INDEX(B1:B6,MATCH(A9,A1:A6,0)) Would like this cell to return "Veg"

<tbody>
</tbody>

I'd like to match on the "Corn" part of A9 and return Veg in B9. Here's the formula
I'm using in B9: =INDEX(B1:B6,MATCH(A9,A1:A6,0))
If I enter "Corn" in A9, I get "Veg" in B9 as expected. If I Enter "White Corn" in A9, B9 returns "#N/A." I thought I could simply add wild cards to cell A5, like "*Corn*", to get a match on "White Corn," but that doesn't work. Is it possible to create a formula that does what I'm trying to accomplish?

Thanks in advance for your help!

David

 

[AhoyNC]

See if this works for you.
Formula is from Jon von der Heyden
at this post.
http://www.mrexcel.com/forum/excel-questions/976156-index-match-wildcards-match-array.html

Excel Workbook
	A	B
1	Apples	Fruit
2	Pears	Fruit
3	Bananas	Fruit
4	Grapes	Fruit
5	Corn	Veg
6	Beets	Veg
7
8
9	White Corn	Veg
Sheet