Index Match Wiildcard Partial Match

[moscott75]

I am trying to use a formula similar to match index that will allow me to index based like/similar words in cell. I want the result in Table 2. Table 2 does contain the exact words in Table 1, fruit column I am unable to use index to bring over the totals to Table 2

Table 1				Table 2
Fruit	Total			Color	TOTAL
Apples	12			Apples are red	12
Oranges	15			Banana are yellow	7
Watermelon	25			Brown pear	29
Banana	7			Oranges are round	15
Pear	29			Watermelon are green	25

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[ttray33y]

I am trying to use a formula similar to match index that will allow me to index based like/similar words in cell. I want the result in Table 2. Table 2 does contain the exact words in Table 1, fruit column I am unable to use index to bring over the totals to Table 2

Table 1				Table 2
Fruit	Total			Color	TOTAL
Apples	12			Apples are red	12
Oranges	15			Banana are yellow	7
Watermelon	25			Brown pear	29
Banana	7			Oranges are round	15
Pear	29			Watermelon are green	25

<tbody>
</tbody>

using index+match
=INDEX($J$7:$J$9,MATCH("APPLES*",$I$7:$I$9,0))
=INDEX($J$7:$J$9,MATCH("BANANA*",$I$7:$I$9,0))
=INDEX($J$7:$J$9,MATCH("ORANGE*",$I$7:$I$9,0))

using vlookup

=VLOOKUP("Apples*",$I$14:$J$16,2,FALSE)
=VLOOKUP("BANANA*",$I$14:$J$16,2,FALSE)
=VLOOKUP("ORANGE*",$I$14:$J$16,2,FALSE)

modify as required.

 

[Marcelo Branco]

Maybe this...

	A​	B​	C​	D​	E​
1​	Fruit​	Total​		Color​	TOTAL​
2​	Apples​	12​		Apples are red​	12​
3​	Oranges​	15​		Banana are yellow​	7​
4​	Watermelon​	25​		Brown pear​	29​
5​	Banana​	7​		Oranges are round​	15​
6​	Pear​	29​		Watermelon are green​	25​

Formula in E2 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$6,D2)),$B$2:$B$6)

M.