re- text missing

[Jerie]

In sheet1 I have the word farm in cells B2,B10,B18. I need
to use that word in sheet2 cell D1, but if cell B2 is
blank I get a blank cell for sheet2 D1,how can I get the
word farm from cell B10 or B18 to sheet2 D1

Thanks Jerie

 

[Frank Kabel]

Hi
and what should happen if nonen of these cells contain the word farm?
You could try for example:
=IF(SUMPRODUCT(--('sheet1'!B2:B18="farm"),--(MOD(ROW('sheet1'!B2:B18)-2
,8)=0)),"farm","farm not found")