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="width: 535"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Table 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Color
[/TD]
[TD]TOTAL
[/TD]
[/TR]
[TR]
[TD]Apples
[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples are red [/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]Banana are yellow[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Watermelon [/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brown pear[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]Banana [/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oranges are round[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]Watermelon are green[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

 

[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="width: 535"]
<tbody>[TR]
[TD]Table 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Color
[/TD]
[TD]TOTAL
[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples are red[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]Banana are yellow[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Watermelon[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brown pear[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oranges are round[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]Watermelon are green[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Fruit​

[/td][td]

Total​

[/td][td][/td][td]

Color​

[/td][td]

TOTAL​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Apples​

[/td][td]

12​

[/td][td][/td][td]

Apples are red​

[/td][td]

12​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Oranges​

[/td][td]

15​

[/td][td][/td][td]

Banana are yellow​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Watermelon​

[/td][td]

25​

[/td][td][/td][td]

Brown pear​

[/td][td]

29​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Banana​

[/td][td]

7​

[/td][td][/td][td]

Oranges are round​

[/td][td]

15​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Pear​

[/td][td]

29​

[/td][td][/td][td]

Watermelon are green​

[/td][td]

25​

[/td][/tr]
[/table]

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

M.