Results 1 to 10 of 10

Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

This is a discussion on Using the =IF(ISNUMBER(SEARCH function to search multiple cells... within the Excel Questions forums, part of the Question Forums category; Hi, I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells. I can get it to work to search ...

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    55

    Default Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Hi,

    I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

    I can get it to work to search one cell (as below):

    =IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

    (In this case the respone is No Change as Cell G10 contains "Same")

    However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

    =IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

    Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

    Any idea?

    Any help much appreciated!

    Best regards

    Nick

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,966

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Are you looking for?...

    =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("Same",G10:R10))),"No Change",'Aug 09 Matrix'!F10)

  3. #3
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,237

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    try this
    Code:
     
    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Same",G10:R10)))),"No Change",'Aug 09 Matrix'!F10)

  4. #4
    Board Regular
    Join Date
    Aug 2009
    Posts
    55

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Thanks for the quick reply!

    I tried that but for the cell below where one of the cells contains 40 instead of "Same" it has come up with "No Change" again where it should be reading off the 'Aug 09 Matrix' sheet

    Really appreciate the help though!

    N

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,966

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Quote Originally Posted by nickwood1066 View Post
    Thanks for the quick reply!

    I tried that but for the cell below where one of the cells contains 40 instead of "Same" it has come up with "No Change" again where it should be reading off the 'Aug 09 Matrix' sheet

    Really appreciate the help though!

    N
    Care to state exactly what you are after in words, not in terms of formulas?

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Posts
    55

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Thanks Texasalynn,

    I've tried that too but it doesn't work either....

    Basically row G10:R10 Contains the Text "Same"

    Cell G11 Contains 40, F11:R11 contain "Same"

    As such I want the Formula Cells (F10 & F11) to either contain "No Change" (if all the cells to up to column R contain "Same"), or to import the data from 'Aug 09 Matrix' tab if any of the cells from Row G to Row R contain anything other than "Same".

    BR

    N

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Location
    North Carolina
    Posts
    2,466

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    the formula

    =IF(ISNUMBER(SEARCH("Same",G10:R10)), "No Change",'Aug 09 Matrix'!F10)


    will only look at the first cell G10

    do you want all the cells to have "same"

    then

    Code:
    =if(sum(ISNUMBER(SEARCH("Same",G10:R10)))=columns(G10:R10)," "No Change",'Aug 09 Matrix'!F10)
    confirmed with control shift enter
    should work
    May I deal with Honor
    May I act with Courage
    May I strive for Humility
    "Straight" By ■■■■ Francis

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,966

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    Quote Originally Posted by nickwood1066 View Post
    Thanks Texasalynn,

    I've tried that too but it doesn't work either....

    Basically row G10:R10 Contains the Text "Same"

    Cell G11 Contains 40, F11:R11 contain "Same"

    As such I want the Formula Cells (F10 & F11) to either contain "No Change" (if all the cells to up to column R contain "Same"), or to import the data from 'Aug 09 Matrix' tab if any of the cells from Row G to Row R contain anything other than "Same".

    BR

    N
    =IF(COUNTIF(G10:R10,"Same")=COLUMNS(G10:R10),"No Change",'Aug 09 Matrix'!F10)

  9. #9
    Board Regular
    Join Date
    Jun 2008
    Location
    North Carolina
    Posts
    2,466

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    I had gotten the impression that there might be other things in the cell than "Same"
    if there is

    =IF(COUNTIF(G10:R10,"*Same*")=COLUMNS(G10:R10),"No Change",'Aug 09 Matrix'!F10)
    May I deal with Honor
    May I act with Courage
    May I strive for Humility
    "Straight" By ■■■■ Francis

  10. #10
    Board Regular
    Join Date
    Aug 2009
    Posts
    55

    Default Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

    WOW!

    It worked thank you so much! Been trying to figure it out for ages!

    Really appreciate you're help!

    Have a fantastic day

    Best Regards

    N

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
  •  


DMCA.com