I'm attempting to return a string of values that match multiple sets of criteria. All of the criteria except for the one that looks to see if the value matches a key seem to be working. I believe it has something to do with combining a array formula with a OR statement.
I was able to create one formula that worked using the TRANSPOSE function however, once I dragged it down my excel prompted me with the below error message. (Assuming it is too much data to compute)
Here is the formula that worked initially:
=TEXTJOIN(", ",TRUE,IF(('Sample Check Data'!$A:$A=Solution!B3)*('Sample Check Data'!$T:$T=Solution!D3)*('Sample Check Data'!$J:$J>Solution!G3)*('Sample Check Data'!$M:$M=TRANSPOSE(Criteria!$A$2:$A$8)),'Sample Check Data'!$H:$H,""))
Image
As an alternative, I built this formula which continues to fail to return a #VALUE Error (The bold highlight is what's causing it to fail. I, unfortunately, can't figure out the issue. Does anyone know how to resolve?:
=TEXTJOIN(", ",TRUE,IF(**'Sample Check Data'!$M:$M=OR(Criteria!$A$2:$A$8)***('Sample Check Data'!$A:$A=Solution!B3)*('Sample Check Data'!$T:$T=Solution!D3)*('Sample Check Data'!$J:$J>Solution!G3),'Sample Check Data'!$H:$H,"NoDataFound"))
I found the transpose solution on this thread but it did not mention any memory issue resulting from its use. TEXTJOIN based on multiple columns and multiple IF conditions
Any help or alternative solution would be greatly appreciated.
Update:
After some more trial and error I found a solution, however I'm still running into the Excel Ran Out of Resources Error.
=TEXTJOIN(", ",TRUE,IF(('Sample Check Data'!$A:$A=Solution!B3)*('Sample Check Data'!$T:$T=Solution!D3)*('Sample Check Data'!$J:$J>Solution!G3)* (('Sample Check Data'!$M:$M=Criteria!$A$2) +('Sample Check Data'!$M:$M=Criteria!$A$3)+('Sample Check Data'!$M:$M=Criteria!$A$4) +('Sample Check Data'!$M:$M=Criteria!$A$5)+('Sample Check Data'!$M:$M=Criteria!$A$6)+('Sample Check Data'!$M:$M=Criteria!$A$7)+('Sample Check Data'!$M:$M=Criteria!$A$8)),'Sample Check Data'!$H:$H,""))
I attempted to modify the column references from $A:$A to $A$1:$A$10000 that way less computing power is required. After making this change I now receive a N/A error. Are there any suggestions?
