Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Redmond, WA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Pizzio

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default 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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default 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 ...
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    MrExcel MVP Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,806
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: IF "x" is within all columns

    =IF(COUNTIF(A1:IP1,"*exclude*"),"exclude included","exclude not included")
    Office 2010/365

  5. #5
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    250
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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