+ Reply to Thread
Results 1 to 7 of 7

Modify this Search Formula to search across a range of cells

  1. #1
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2010
    Posts
    108

    Modify this Search Formula to search across a range of cells

    Hi all,

    I'm using Excel 2010....I have the following formula in cell W3: =IFERROR(SEARCH("educ",G3),"NO")

    As it is, it will search only cell G3 and return the answer. I need the formula to search cells G3 to S3 and return the answer if it finds the value being searched in any of those cells.

    I tried the following but it does not work: =IFERROR(SEARCH("educ",G3:S3),"NO")


    Can anyone help please?

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Modify this Search Formula to search across a range of cells

    Perhaps.

    =if(countif(g3:s3,"educ"),"yes","no")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,945

    Re: Modify this Search Formula to search across a range of cells

    Hi,

    =IFERROR(INDEX(G3:S3,1,MATCH("*educ*",G3:S3,FALSE)),"NO")
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Modify this Search Formula to search across a range of cells

    Just a lil modification in Fortis's formula to adapt Search behabior..

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  5. #5
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Modify this Search Formula to search across a range of cells

    Wow! You guys are cool! Thanks a load!

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Modify this Search Formula to search across a range of cells

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,945

    Re: Modify this Search Formula to search across a range of cells

    Hi,

    How are you defining 'the answer'

    Is that the fact that the string exists in the range or not - (i.e. the binary value Yes or No), or the whole string in the cell where the sub string is found?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula To Search IP Range for Location
    By atnextc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2012, 10:36 AM
  2. [SOLVED] Modify left-search formula to replace letter with number
    By Mikey7346 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-27-2012, 05:30 AM
  3. [SOLVED] conditional formatting formula to search a range of cells
    By drfarmkid in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 03:25 PM
  4. Replies: 5
    Last Post: 03-29-2012, 04:12 AM
  5. Formula to search if a value is in range of 2 columns
    By tvanhooz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2011, 01:15 AM

Tags for this Thread

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