Forum Discussion
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.
Thanks in advance!
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!
6 Replies
One alternative with 365:
=TEXTSPLIT( TEXTJOIN(";",, MAP(CriteriaList, LAMBDA(crit, TEXTJOIN(";",,FILTER(Table1[Document'#], Table1[Criteria]=crit, ""))) ) ),,";" )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 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!
