+ Reply to Thread
Results 1 to 7 of 7

Help writing an IF statement

  1. #1
    cynichromantique
    Guest

    Help writing an IF statement

    I would like to write an IF statement that would put an asterick in a
    specific column of the same row where any text residing in Column A matches
    specific text.

    Example:

    Column A of Row 125 contains the text "top rated auto insurance". I would
    like for the IF statement to test for the word "insurance" and place an
    asterick in Cell I125.

    Thanks!

  2. #2
    JMB
    Guest

    RE: Help writing an IF statement

    In I125 put

    =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

    copy throughout column I as needed.

    "cynichromantique" wrote:

    > I would like to write an IF statement that would put an asterick in a
    > specific column of the same row where any text residing in Column A matches
    > specific text.
    >
    > Example:
    >
    > Column A of Row 125 contains the text "top rated auto insurance". I would
    > like for the IF statement to test for the word "insurance" and place an
    > asterick in Cell I125.
    >
    > Thanks!


  3. #3
    cynichromantique
    Guest

    RE: Help writing an IF statement

    JMB,

    Is it possible to add additional words to a statement of this nature?

    Examples:

    coverage
    homeowners
    annuity
    blue cross
    group

    Thanks!

    cynichromantique

    "JMB" wrote:

    > In I125 put
    >
    > =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")
    >
    > copy throughout column I as needed.
    >
    > "cynichromantique" wrote:
    >
    > > I would like to write an IF statement that would put an asterick in a
    > > specific column of the same row where any text residing in Column A matches
    > > specific text.
    > >
    > > Example:
    > >
    > > Column A of Row 125 contains the text "top rated auto insurance". I would
    > > like for the IF statement to test for the word "insurance" and place an
    > > asterick in Cell I125.
    > >
    > > Thanks!


  4. #4
    JMB
    Guest

    RE: Help writing an IF statement

    So if A125 contains any of those words, return an "*"?

    IF(SUMPRODUCT(--ISNUMBER(SEARCH({"insurance","coverage","homeowners","annuity","blue cross","group"},A125,1))),"*","")



    "cynichromantique" wrote:

    > JMB,
    >
    > Is it possible to add additional words to a statement of this nature?
    >
    > Examples:
    >
    > coverage
    > homeowners
    > annuity
    > blue cross
    > group
    >
    > Thanks!
    >
    > cynichromantique
    >
    > "JMB" wrote:
    >
    > > In I125 put
    > >
    > > =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")
    > >
    > > copy throughout column I as needed.
    > >
    > > "cynichromantique" wrote:
    > >
    > > > I would like to write an IF statement that would put an asterick in a
    > > > specific column of the same row where any text residing in Column A matches
    > > > specific text.
    > > >
    > > > Example:
    > > >
    > > > Column A of Row 125 contains the text "top rated auto insurance". I would
    > > > like for the IF statement to test for the word "insurance" and place an
    > > > asterick in Cell I125.
    > > >
    > > > Thanks!


  5. #5
    Peo Sjoblom
    Guest

    Re: Help writing an IF statement

    Or use OR <g>

    =IF(OR(ISNUMBER(SEARCH({"insurance","coverage","homeowners","annuity","blue
    cross","group"},A125,1))),"*","")


    --

    Regards,

    Peo Sjoblom

    http://nwexcelsolutions.com


    "JMB" <[email protected]> wrote in message
    news:[email protected]...
    > So if A125 contains any of those words, return an "*"?
    >
    > IF(SUMPRODUCT(--ISNUMBER(SEARCH({"insurance","coverage","homeowners","annuity","blue
    > cross","group"},A125,1))),"*","")
    >
    >
    >
    > "cynichromantique" wrote:
    >
    >> JMB,
    >>
    >> Is it possible to add additional words to a statement of this nature?
    >>
    >> Examples:
    >>
    >> coverage
    >> homeowners
    >> annuity
    >> blue cross
    >> group
    >>
    >> Thanks!
    >>
    >> cynichromantique
    >>
    >> "JMB" wrote:
    >>
    >> > In I125 put
    >> >
    >> > =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")
    >> >
    >> > copy throughout column I as needed.
    >> >
    >> > "cynichromantique" wrote:
    >> >
    >> > > I would like to write an IF statement that would put an asterick in a
    >> > > specific column of the same row where any text residing in Column A
    >> > > matches
    >> > > specific text.
    >> > >
    >> > > Example:
    >> > >
    >> > > Column A of Row 125 contains the text "top rated auto insurance". I
    >> > > would
    >> > > like for the IF statement to test for the word "insurance" and place
    >> > > an
    >> > > asterick in Cell I125.
    >> > >
    >> > > Thanks!




  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Perhaps just

    =IF(COUNT(SEARCH({"insurance","coverage","homeowners","annuity","blue cross","group"},A125)),"*","")

  7. #7
    JMB
    Guest

    Re: Help writing an IF statement

    good call <g>

    "daddylonglegs" wrote:

    >
    > Perhaps just
    >
    > =IF(COUNT(SEARCH({"insurance","coverage","homeowners","annuity","blue
    > cross","group"},A125)),"*","")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=528396
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1