sumproduct adjustment - search for cells containing a word

[heathball]

=SUMPRODUCT(--ISNUMBER(SEARCH({"red","blue","green","black"},EN2)))>0

Hello, Im using SUMPRODUCT for the first time, to search for cells containing words.

Is there a way to adjust this formula, so that the actual word that is found eg. 'blue' is the result, instead of TRUE
and also for the result cell to be blank if there is is a FALSE result?

Thanks in advance

 

[oldbrewer]

=if(iserror(..................),"",(....................))
for 2nd question - no idea on first one

 

[Marcelo Branco]

Maybe...

=IFERROR(LOOKUP(9.99E+307,SEARCH({"red","blue","green","black"},EN2),{"red","blue","green","black"}),"")

M.

 

[oldbrewer]

					red	blue	green	black		helper1	helper2	helper3
				this is a blue door		2				1	2	blue
				this is a red door	1					2	1	red
				this is a pink door						3	0
				this is a yellow door						4	0
				this is a black door				4		5	4	black
				blue in helper 3 obtained by
				=IF(ISERROR(OFFSET(E2,-K2,MATCH(L2,F2:I2,0))),"",OFFSET(E2,-K2,MATCH(L2,F2:I2,0)))

<colgroup><col span="4"><col><col span="10"></colgroup><tbody>
</tbody>

 

[heathball]

thanks for both your posts

Marcelo, that works perfectly. I see 9.99E+307 is the largest number that excel can store....very mysterious ( i will think about how this works) and skillful work

 

[heathball]

thanks oldbrewer, that is a good way to do it. I will check out this OFFSET idea!

 

[oldbrewer]

offset combined with match is a very useful tool.