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

Upload example workbook

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

Hi

TransType.xls

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

Can you write your expected result (manually)?

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...

Here, try this:

Formula:

Please Login or Register  to view this content.

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