Combining an Array Formula and Another Non-Array Formula

All,

I was looking for assistance or some determination if two formulas I wrote for work can be combined into a single formula. See attached spreadsheet cleansed of production data (it's work related) and the two formula I'd like help with (also in fields Y and Z in attached spreadsheet)

1) =IF(OR(H2="OperatorRejectedClose", H2="Order submit - Failed"), "Hard Touch", IF(OR(H2="AutoReject", H2="File download - Rejected"), "Upfront Rejection", IF(H2 = "Order submit - Completed","Yield"," ")))

2) ={IF(Y2="Yield",MATCH(A2&{"*fication*"},A:A&H:H,0), " ")}

Here's the overall idea - it's a spreadsheet with log data. If formula one finds "File download - Rejected", "Order submit - Failed", or "Order Submit - Completed" in field H, it returns "Upfront Rejection, "Hard Touch", or "Yield", respectively. Now, those line items that return "Yield' need to be broken down into "Soft Touch" or "Frictionless" (or alternatively, "Yield - Soft Touch" and "Yield - Frictionless which was the original goal writing these formulas) which is the point of the second array formula. The data is kinda wonky which is why such a complex set of formulas is needed (department didn't have reporting in mind when they developed the tool).

Please see attached spreadsheet - I know this is actually probably not the best way to do this (maybe VBA would help? I don't have any idea how to do this so that code would be SOOOOO helpful).

I've put the "Logic" or my intention in the merged cells below and highlighted an example of each case for the second formula (green My hope is combine these two into a single formula. It's a lot - I know, and I've been as detailed as possible both here and in my example spreadsheet in hopes someone will understand and tell me if there is a better way to accomplish this search. Currently, because of the way the second formula works and the actual number of records in a set of data, the second formula really takes time to go through several thousand lines of records to return my result. Any help would be greatly appreciated. Let me know if this isn't clear or if more clarification is needed. I appreciate you all!!!

By the way, all this data was made up for the example so nobody has to worry about sensitive data or anything like that. Thank you again.

Oh my it's really close! The only thing wrong is that all the "Yields" are being listed as "Yield - Frictionless" and it's not doing soft touches at all. Any idea on how to fix that? But wow! That's really cool. Any help on fixing that last part would be amazing. I'm taking a close look too but if you know what you did and how to fix that would be amazing.

WAIT - I see. It's based on the example, not on the fact my real version has several thousand rows to check. I'll play around with the absolute references a bit to fix it. Thanks for your help! You're the best.

First of all, you shouldn't be returning spaces within the quotes at the end of each formula - make it "" rather than " ".

Secondly, you shouldn't use full-column references in the second (array) formula. If you do, then Excel will use every cell in the range (over 1 million of them), and this is why the workbook will appear sluggish.

You can basically substitute the middle part of the array formula into formula 1 instead of the "Yield" parameter, although you could avoid having an array formula if you combine column A with column H. So, you could do this in Y2:

=A2&H2

and copy this down to the bottom of your data, and then the formula in Z2 would be:

=IF(OR(H2="OperatorRejectedClose", H2="Order submit - Failed"), "Hard Touch", IF(OR(H2="AutoReject", H2="File download - Rejected"), "Upfront Rejection", IF(H2 = "Order submit - Completed",MATCH(A2&"*fication*",Y:Y,0), "")))

This does not need to be array-entered (with CSE), but is equivalent to what you have at present.

To split the "Yield" as desired, you can make this amendment to the formula in Z2:

=IF(OR(H2="OperatorRejectedClose", H2="Order submit - Failed"), "Hard Touch", IF(OR(H2="AutoReject", H2="File download - Rejected"), "Upfront Rejection", IF(H2 = "Order submit - Completed",IF(ISNUMBER(MATCH(A2&"*fication*",Y:Y,0)),"Yield - Soft Touch","Yield - Frictionless"),"")))

and copy this down as required.

Hope this helps.

Pete