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

1. ## 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  Register To Reply

2. ## Re: Formula to find multiple part matches in a range of cells and return value if each exi  Register To Reply

3. ## 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  Register To Reply

4. ## 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!  Register To Reply

5. ## 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?  Register To Reply

6. ## 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)?  Register To Reply

7. ## 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...  Register To Reply

8. ## 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",""))

Regards  Register To Reply

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

Here, try this:
Formula:  `Please Login or Register  to view this content.`  Register To Reply

10. ## 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   Register To Reply