check cell on presence of text from range

[nielsb]

Hi there,
my question posted before is still unresolved.

I need a simple formula (no VBA) which checks (e.g. by means of TRUE/FALSE) whether it contains any text from a given range. Preferably it should neglect blank cells therein.

Illustration is given in below example. A formula in C1 should return TRUE as 'train' in column A forms part of 'trainstation' (B1).

banana	trainstation	TRUE
orange
train
apple
school

<tbody>
</tbody>

Anybody an idea how to do this?

Regards Niels

 

[Michael M]

Maybe not exactly the same....but I think with a large dataset VBA would be better

Excel 2007
	A	B	C
1	banana	train station	FALSE
2	orange		FALSE
3	train		TRUE
4	apple		FALSE
5	school		FALSE
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF($A$1:$A$5,"*"&B1&"*")>0
C2	=COUNTIF($B$1:$B$5,"*"&A2&"*")>0
C3	=COUNTIF($B$1:$B$5,"*"&A3&"*")>0
C4	=COUNTIF($B$1:$B$5,"*"&A4&"*")>0
C5	=COUNTIF($B$1:$B$5,"*"&A5&"*")>0