Using Excel 2010 with multiple "if" & need all results in one cell, can't use textjoin

Hi! I have Excel 2010 at work and am trying to create a report because the app they use can't do it. The original data was pulled from 5 different reports and have now have everything needed on two sheets. I have 3 criteria:
'MRP9202'!M:M>C2 'MRP9202'!B:B=G2 and 'MRP9202'!Q:Q<R2

and need to return all conforming values from 'MRP9202'!R:R (a 'helper column', which concatenates columns N, J and P) and display them in one cell, separated with commas or pipes.

I tried all sorts of IF/AND and INDEX/MATCH formulas, and the closest to success was =IF(AND('MRP9202'!M:M>C2,'MRP9202'!B:B=G2,'MRP9202'!Q:Q<R2), CONCATENATE('MRP9202'!N:N," Needs QTY:",'MRP9202'!J:J," Ships:",'MRP9202'!P:P),"") but it only returns the first valid option and I need all of them.

I created the helper column 'MRP9202'!R:R in the interest of simplicity for the INDEX/MATCH attempts, but gladly abandon it if another method works. Everything, formula or array, I've tried only returns the first valid option.

Thanks, in advance, for any help you can provide!

***added a sanitized version of the spreadsheet

If you need ONE of these formulas for use in ONE lookup, it's going to be simple with a helper column added to the data sheet.

If what you need is the same thing you've described over and over again starting at row2 and going downward ad nauseum... that's not going to be easy with a simple formula.

If you're allowed to use VBA we could create a custom function for you to install into your workbook that would then function like a formula, just a special one we created with coding.

Hi arcarc and welcome to the forum,

I'd use the Advanced Filter tool that is in the Data tab. See the attached example that has 100 rows of data, a criteria area and the answer to the right. Instead of exact matches you would use your criteria above and this should then give all possible answers.

Advanced Filter using 3 Column AND.xlsx

Marv

Here's the custom function I offered, comments added to explain as we go:

Please Login or Register  to view this content.

I've installed it into your workbook. The formula is used in a cell as:

=SPECIALIFS(TableData,WorkOrder,Component,DateCell)
=SPECIALIFS(MRP!$A$1:$J$7,A2,C2,J2)

Have a look.