+ Reply to Thread
Results 1 to 10 of 10

Formula to find multiple part matches in a range of cells and return value if each exists

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Formula to find multiple part matches in a range of cells and return value if each exists

    Hi,

    I've been playing around with this for some time now and cannot seem to get the right set up, not with search, match or find!

    I have a range of cells (lets say A5:A10) that will only contain one of three values: POS CNP, POS CP or ATM (CP). I would like a formula placed in cell B5 that searches that range for POS, ATM, CNP and CP and if any oth those are present then strings them together into one result: POS/ATM CNP/CP - though the exact format isn't that important.

    So if POS CNP and POS CP were the only ones present then it would return: POS CNP/CP. Hope this is making sense!

    I have tried it using FIND (only got as far as two criteria), but that only appears to work in one cell at a time - despite detailing a range:

    =TRIM(IF(ISNUMBER(FIND("POS",A5:A10)),"POS","")&" "&IF(ISNUMBER(FIND("ATM",A5:A10)),"ATM",""))

    Any ideas? Or am I asking too much of a formula?

    Thanks, TC

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Upload example workbook

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    I would but everything's really restricted where I work. Sure that would be frowned upon - even with all remotely sensitive information removed.

    I will try and recreate something at home an upload - but may not be able to do it tonight.

    Thanks, TC

  4. #4
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Hi

    TransType.xls

    Has this worked? having no end of trouble attaching an example workbook!

  5. #5
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Okay - think that worked, finally.

    So, you can select the three different options in the yellow cells and I added the formula I detailed in the original post the adjacent cells in column B. This is only for two criteria at present, but would like to expand it to include CNP & CP as well - but seeing as I can't even get two to work probably best not to try that just yet.

    As I've specified a range I would have expected the formulas to all show the same result: "POS ATM" in each cell, but as you can see they show differing results depending on what is in the adjacent cell. I can't make sense of this... Anyone else?
    Last edited by TC1980; 07-02-2013 at 04:38 PM.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Can you write your expected result (manually)?

  7. #7
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    My expected result for all of these formulas would be POS ATM.

    Examples below:

    Range contains; POS CNP, POS CNP, result expected: POS CNP
    Range contains; POS CNP, POS CP, result expected: POS CNP / CP
    Range contains: POS CP, ATM (CP), result expected: POS / ATM CP
    Range contains: ATM (CP), POS CNP, result expected: POS / ATM CNP / CP

    Basically it the formula needs to search a range for four items (POS, ATM, CNP, CP) and if it finds them return them in the result.

    To clarify I will only use this summary formula once - but can't seem to get FIND to work with a range, hence my experimentation and finding the attached. Strange.

    I hope I'm making more sense now, not sure how else to explain...

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,694

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Hi,

    Adapted slightly and entered as an array formula:

    =TRIM(IF(SUM(--ISNUMBER(FIND("POS",$A$5:$A$10)))>1,"POS","")&" "&IF(SUM(--ISNUMBER(FIND("ATM",$A$5:$A$10))),"ATM",""))

    though you'll need to add in your other two conditions.

    Regards
    Click * below if this answer helped

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

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Here, try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Formula to find multiple part matches in a range of cells and return value if each exi

    Thank you both XOR LX and xbor, both work perfectly!

    I have gone with zbor solution as I like to avoid array formulas where possible, only because they take up a lot of memory and some of the guys where I work are using really underspec'd PC's that struggle to run lareger files.

    But thank you to both of you

+ 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