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:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Apples
[/TD]
[TD]Fruit
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Pears
[/TD]
[TD]Fruit
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Bananas
[/TD]
[TD]Fruit
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Grapes
[/TD]
[TD]Fruit
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Corn
[/TD]
[TD]Veg
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Beets
[/TD]
[TD]Veg
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Value to Look up
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]White Corn
[/TD]
[TD]=INDEX(B1:B6,MATCH(A9,A1:A6,0))

Would like this cell to return "Veg"
[/TD]
[/TR]
</tbody>[/TABLE]


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