How to combine a sumproduct formula with vlookup or index match

[ripina]

Using SUMPRODUCT I can test to see if a value (things) is found withing the Text cell (text column B).
I want to expand on that result and if TRUE, then fetch the value in the Other Things (column G) and place it in column D.

In the example I included the correct result or value that the formula should produce.

Excel 2010
	A	B	C	D	E	F	G	H
1
2
3
4		Text	Result	Result 2		Things	Other Things
5		Yellow dog on the grass	TRUE	spoon		yellow	spoon
6		Warm gray sweater	FALSE	FALSE		orange	couch
7		A red sun on the horizon	FALSE	FALSE		green	car
8		Blue neon signs everywhere	TRUE	house		blue	house
9		Waves of color of Turquois	FALSE	FALSE		pink	bird
10		Wearing hot pink socks	TRUE	bird
11		Deep black eyes	FALSE	FALSE
12
13
14
Sheet3

Cell Formulas
Range	Formula
D8	=G8
D10	=G9
D5	=G5
C5	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B5)))>0
C6	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B6)))>0
C7	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B7)))>0
C8	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B8)))>0
C9	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B9)))>0
C10	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B10)))>0
C11	=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B11)))>0

 

[DRSteele]

Let's see if this will float the boat.

	B	C	D	E	F	G
Text	Result	Result 2	Things	Other Things
Yellow dog on the grass	yellow	spoon
Warm gray sweater	orange	couch
A red sun on the horizon	green	car
Blue neon signs everywhere	blue	house
Waves of color of Turquois	pink	bird
Wearing hot pink socks
Deep black eyes

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="bgcolor: #A9D08E"]spoon[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #A9D08E"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #A9D08E"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="bgcolor: #A9D08E"]house[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #A9D08E"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="bgcolor: #A9D08E"]bird[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #A9D08E"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B5)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=IF(SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B5)))>0,INDEX($G$5:$G$9,SUMPRODUCT(ISNUMBER(SEARCH($F$5:$F$9,B5))*(ROW($G$5:$G$9)-ROW($G$4)))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]