SOLVED

Formula Help needed for Comparing Two Arrays in Excel 365

Copper Contributor

I'm having trouble finding the right formula in Excel 365 to compare a column in an dynamic Table array with a separate dynamic list and having the formula output a variable list of items from a different column in the array.

 

Document#Criteria Criteria List Expected Output
Doc1Criteria1 Criteria3 Doc3
Doc2Criteria2 Criteria7 Doc12
Doc3Criteria3 Criteria1 Doc18
Doc4Criteria4   Doc7
Doc5Criteria5   Doc1
Doc6Criteria6   Doc10
Doc7Criteria7   Doc16
Doc8Criteria8    
Doc9Criteria9    
Doc10Criteria1    
Doc11Criteria2    
Doc12Criteria3    
Doc13Criteria2    
Doc14Criteria3    
Doc15Criteria4    
Doc16Criteria1    
Doc17Criteria2    
Doc18Criteria3    
Doc19Criteria9    
Doc20Criteria8    

 

The Document# list is unique, but there can be multiple documents with the same criteria.  The Expected Output does not need to be in any specific ordering.

 

The Expected Output is a list of all the documents from the Document# column that match the criteria in the Criteria List.

 

I've tried a variety of INDEX, SMALL, ROW functions but can't seem to get close.  Any help would be greatly appreciated.

 

Thanks in advance!

6 Replies

@tmehringer01 REDUCE() is right function. It has capability to iterate based on give list and store each calculated result to accumulator. Try-

=DROP(REDUCE("",D2:D4,LAMBDA(a,x,VSTACK(a,FILTER(A2:A21,B2:B21=x)))),1)

Harun24HR_0-1705633568359.png

 

 

best response confirmed by tmehringer01 (Copper Contributor)
Solution

@Harun24HR Works like a charm!  There are some advanced functions in there that I'm going to need to dig into to understand.  Looks like the formula even caught one output that I missed.

 

Looks like they need to pay you more!  Thanks again for your help!

@tmehringer01 

 

One alternative with 365:

Sample.png

=TEXTSPLIT(
  TEXTJOIN(";",,
    MAP(CriteriaList,
      LAMBDA(crit, TEXTJOIN(";",,FILTER(Table1[Document'#], Table1[Criteria]=crit, "")))
    )
  ),,";"
)
Well. But TEXTJOIN() have a character limitation and we must be aware of that for large data set. For small data set, this is a good alternative.

@Harun24HR 

 

But TEXTJOIN() have a character limitation

Agree, 32767 Chars

1 best response

Accepted Solutions
best response confirmed by tmehringer01 (Copper Contributor)
Solution

@Harun24HR Works like a charm!  There are some advanced functions in there that I'm going to need to dig into to understand.  Looks like the formula even caught one output that I missed.

 

Looks like they need to pay you more!  Thanks again for your help!

View solution in original post