+ Reply to Thread
Results 1 to 8 of 8

Formula that will return a result based criteria found through searching multiple cells

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Allover
    MS-Off Ver
    Excel 2007,2010
    Posts
    13

    Formula that will return a result based criteria found through searching multiple cells

    Hi,

    I've been trying to create a formula in the attached spreasheet that searches through columns A, C & D and returns the result in column E based on the criteria in red in columns A,C&D.

    Also, if no criteria exists the cell should remain blank.

    Any help or advice anyone can provide would be greatly appreciated.

    Thank you,

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,346

    Re: Formula that will return a result based criteria found through searching multiple cell

    Your request is not too clear to me, sorry Perhaps you could include a few examples of your expected outcome and how you arrived atthem?

    Are you, for instance, trying to find RED text in a cell and then return something based on that "find"?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    Allover
    MS-Off Ver
    Excel 2007,2010
    Posts
    13

    Re: Formula that will return a result based criteria found through searching multiple cell

    Sorry, yes, the formula should find the RED text and then return the text in BLUE in column E.

    Column E is basically a list of possible returns.

    There will be approx 500-1000 rows of data to that will contain various forms of the text in columns A, C & D, but the text in RED will be constant.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,346

    Re: Formula that will return a result based criteria found through searching multiple cell

    Excel cannot "see" colors, so unless there is some logic to how you change the colors, you will need someone to put some VBA together for you

  5. #5
    Registered User
    Join Date
    04-26-2013
    Location
    Allover
    MS-Off Ver
    Excel 2007,2010
    Posts
    13

    Re: Formula that will return a result based criteria found through searching multiple cell

    The colour doesn't matter --- i just used red text to hightlight the criteria.

    Essentially I need the formulas to search through columns A,C&D for any of text in the `Search for`column below and return the result in the column E from the `Return Result`column below. I was trying to use VLookup but i don`t think Vlookup can search through 3 different cells.

    Search for

    ROC
    24200
    TC
    EO
    ARP
    NCS
    CCD
    Trade Receipts
    MNPAD
    48ELO
    GAPOSA


    Return Result
    ROC
    AR
    TC
    EO
    ARP
    NCS
    CCD
    Total
    PAD
    AR
    AR
    Last edited by clickclick; 09-06-2013 at 10:29 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,346

    Re: Formula that will return a result based criteria found through searching multiple cell

    I added your criteria to G2:G12, you can movethem where you want, just adjust the references in the formula...
    =IF(OR(ISNUMBER(SEARCH("*"&$G2&"*",A2,1)),ISNUMBER(SEARCH("*"&$G2&"*",C2,1)),ISNUMBER(SEARCH("*"&$G2&"*",D2,1))),E2,"Not Found")

  7. #7
    Registered User
    Join Date
    04-26-2013
    Location
    Allover
    MS-Off Ver
    Excel 2007,2010
    Posts
    13

    Re: Formula that will return a result based criteria found through searching multiple cell

    Ok thats great. Thanks, (I apologize for being so confusing earlier).

    But I need it to search G2:G12, it looks like this formula (when i tried it anyway) was only searching for the criteria in G2?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,346

    Re: Formula that will return a result based criteria found through searching multiple cell

    OK replace the formula with this array formula...
    =IF(OR(ISNUMBER(SEARCH("*"&$G$2:$G$12&"*",A2,1)),ISNUMBER(SEARCH("*"&$G$2:$G$12&"*",C2,1)),ISNUMBER(SEARCH("*"&$G$2:$G$12&"*",D2,1))),E2,"Not Found")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

+ 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. [SOLVED] Formula to return specific text based on multiple cells meeting a single criteria
    By missydanni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2013, 09:48 AM
  2. An IF formula result that based on multiple criteria
    By johnfash in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2013, 08:15 AM
  3. [SOLVED] Formula to Return ID based on Multiple Criteria
    By boldcode in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 02:54 PM
  4. Return Result based on Multiple Criteria
    By franciz in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-20-2009, 01:30 PM
  5. how can I have a formula result based on multiple criteria/columns
    By nicky_p in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-05-2006, 08:50 AM

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