Required a correct the single cell formula for join a values

[mmr1]

Hi,

required a formula help to return a correct results, the below stated formula returns a N/A error could you please rectify it.

=MAP(B1944:B2180,AD1943:BB1943,C1944:C2180,LAMBDA(Criteria1,Criteria2,Criteria3,LET(uniqueFilter,UNIQUE(FILTER($O$12:$O$1669,($L$12:$L$1669=Criteria1)*($R$12:$R$1669=Criteria2)*($W$12:$W$1669=Criteria3),0)),TEXTJOIN("|",,IFERROR(uniqueFilter,"")))))


Many thanks,

 

[KRice]

The three arrays being passed to the LAMBDA function are B1944:B2180, AD1943:BB1943, and C1944:C2180...and those consist of either 237 rows or 25 columns. When used in the FILTER function, those arrays are used to make logical comparisons against arrays $L$12:$L$1669, $R$12:$R$1669, and $W$12:$W$1669, respectively...which all consist of 1658 rows. The 1st and 3rd of those logical comparisions in the FILTER function (where only rows are being examined), will produce resultant arrays that are 1658 rows by 1 column in size, but only the first 237 rows are meaningful...everything beyond that will be an #N/A error because arrays of dissimilar size are being compared. And the 2nd of those logical comparisons in the FILTER function (where a 1658 row array is compared to a 25 column array will generate a resultant array that is 1658 rows by 25 columns...is that what you want? When all three resultant arrays are multiplied together, the result will be a 1658 x 25 array, and FILTER cannot use that to operate on $O$12:$O$1669, which is a single column array. If you can offer more detail about what you want to accomplish, further assistance is likely.

 

[KRice]

Assuming you want to determine which rows in the three vertical ranges contain values that can be found anywhere in their respective criteria lists, and then you want to eliminate duplicates from the results and combine the results with a delimiter using TEXTJOIN, give this a try:

=LET(uniqueFilter,UNIQUE(FILTER($O$12:$O$1669,ISNUMBER(MATCH($L$12:$L$1669,B1944:B2180,0))*ISNUMBER(MATCH($R$12:$R$1669,AD1943:BB1943,0))*ISNUMBER(MATCH($W$12:$W$1669,C1944:C2180,0)),0)),TEXTJOIN("|",,IFERROR(uniqueFilter,"")))