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
4	Text	Result	Result 2		Things	Other Things
5	Yellow dog on the grass	TRUE	spoon		yellow	spoon
6	Warm gray sweater	FALSE			orange	couch
7	A red sun on the horizon	FALSE			green	car
8	Blue neon signs everywhere	TRUE	house		blue	house
9	Waves of color of Turquois	FALSE			pink	bird
10	Wearing hot pink socks	TRUE	bird
11	Deep black eyes	FALSE

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula C5 =SUMPRODUCT(--ISNUMBER(SEARCH($F$5:$F$9,B5)))>0 D5 =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)))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>