# Thread: IF "x" is within all columns Thanks: 0 Likes: 0

1. ## IF "x" is within all columns

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?

2. ## Re: IF "x" is within all columns

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.

3. ## Re: IF "x" is within all columns

Maybe:
Code:
`=IF(COUNTIF(column range,"*exclude*")>0,"exclude included","exclude not included")`
where column range would be something like B1:XFD1 ...

4. ## Re: IF "x" is within all columns

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

5. ## Re: IF "x" is within all columns

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
1 Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
2 Exclude not included and to which and for of
3 exclude included exclude soundly givet protestation of of
4 Exclude not included the better i first that tis ask exclude better the tis that
5 Exclude not included tell man poor body I were
6 exclude included demand nights fly render hereford lazar
7 Exclude not included though mean cannot and the well
8 exclude included to mingle bid first that tis ask exclude better the be king
9 Exclude not included i will to shall likewise to
10 Exclude not included for desire and with defunct tides
11 exclude included take devil bid starving objects exclude
12 exclude included the shalt gone grey and Exclude
Pizzio

Worksheet Formulas
Cell Formula
A2 to A6 =IF(ISNA(MATCH("exclude",B2:IR2,0)),"Exclude not included","exclude included")
A7
to A101
=IF(ISNA(MATCH("*exclude*",B7:IR7,0)),"Exclude not included","exclude included")

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•