IF "x" is within all columns

[Pizzio]

I'm looking for a formula to put in 100 rows to tell me if "exclude" is populated in any of the 250 columns. If yes, then "exclude included" If no, then "Exclude not included" Any ideas?

 

[Joe4]

Use a COUNTIF function to count the number of times it occurs. You just want to see if there is at least one there, i.e.
=IF(COUNTIF(range to check,"exclude")>0,"exclude included","exclude not included")

Just substitute the range you are checking into the formula.

 

[JoeMo]

Maybe:

=IF(COUNTIF(column range,"*exclude*")>0,"exclude included","exclude not included")

where column range would be something like B1:XFD1 ...

 

[Scott Huish]

=IF(COUNTIF(A1:IP1,"*exclude*"),"exclude included","exclude not included")

 

[Toadstool]

I see you have COUNTIF covered so I'll go with MATCH.

The A2 to A6 formula looks for "exclude" by itself.
The A7 to A101 formula includes "exclude" as a part of any string.

	A	B	C	D	E	F	G
Exclude not included	and	to	which	and	for	of
exclude included	exclude	soundly	givet	protestation	of	of
Exclude not included	the	better	i	first that tis ask exclude better the	tis	that
Exclude not included	tell	man	poor	body	I	were
exclude included	demand	nights	fly	render	hereford	lazar
Exclude not included	though	mean	cannot	and	the	well
exclude included	to	mingle	bid	first that tis ask exclude better the	be	king
Exclude not included	i	will	to	shall	likewise	to
Exclude not included	for	desire	and	with	defunct	tides
exclude included	take	devil	bid	starving	objects	exclude
exclude included	the	shalt	gone	grey	and	Exclude

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col 1[/TD]
[TD="align: center"]Col 2[/TD]
[TD="align: center"]Col 3[/TD]
[TD="align: center"]Col 4[/TD]
[TD="align: center"]Col 5[/TD]
[TD="align: center"]Col 6[/TD]

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Pizzio​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2 to A6
[/TH]
[TD="align: left"]=IF(ISNA(MATCH("exclude",B2:IR2,0)),"Exclude not included","exclude included")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A7
to A101
[/TH]
[TD="align: left"]=IF(ISNA(MATCH("*exclude*",B7:IR7,0)),"Exclude not included","exclude included")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



P.S. Test data from my random extract of 100,000 words from Shakespeares plays, which apparently did not include "exclude" until I made it so.