SOLVED

# Formula Help needed for Comparing Two Arrays in Excel 365

Copper Contributor

# Formula Help needed for Comparing Two Arrays in Excel 365

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 Doc1 Criteria1 Criteria3 Doc3 Doc2 Criteria2 Criteria7 Doc12 Doc3 Criteria3 Criteria1 Doc18 Doc4 Criteria4 Doc7 Doc5 Criteria5 Doc1 Doc6 Criteria6 Doc10 Doc7 Criteria7 Doc16 Doc8 Criteria8 Doc9 Criteria9 Doc10 Criteria1 Doc11 Criteria2 Doc12 Criteria3 Doc13 Criteria2 Doc14 Criteria3 Doc15 Criteria4 Doc16 Criteria1 Doc17 Criteria2 Doc18 Criteria3 Doc19 Criteria9 Doc20 Criteria8

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.

6 Replies

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

@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)``

best response confirmed by tmehringer01 (Copper Contributor)
Solution

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

@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!

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

One alternative with 365:

``````=TEXTSPLIT(
TEXTJOIN(";",,
MAP(CriteriaList,
LAMBDA(crit, TEXTJOIN(";",,FILTER(Table1[Document'#], Table1[Criteria]=crit, "")))
)
),,";"
)``````

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

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.

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

But TEXTJOIN() have a character limitation

Agree, 32767 Chars

1 best response

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

# Re: Formula Help needed for Comparing Two Arrays in Excel 365

@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!