I'm hoping to create a TOCOL in excel of an array of values based on variable criteria. Eg. When it meets one criteria, I wish for it to be able to return an array of values associated with that criteria (eg stores in that region), and when it meets another criteria return values associated to that criteria.
| Sales | Region | Number of stores | Criteria |
|---|---|---|---|
| www | WA | 3 | FALSE |
| xxx | WA | 3 | TRUE |
| yyy | NSW | 2 | TRUE |
| zzz | VIC | 4 | TRUE |
There would be another table to lookup data which I wish to return (eg replenishment stock)
Tried playing around with SEQUENCE and TOCOL and I am reliably able to get a static value
=IFERROR(TOCOL(CHOOSE(SEQUENCE(1,COUNTIF(\_sloc_repl\[Repl type\],XLOOKUP(TEXTAFTER(CELL("filename",$A$1),"\]"),\_tool\[Material type\],\_tool\[Repl type\])),1,0),FILTER(\_tool\[Criteria\],\_tool\[Criteria\]=TEXTAFTER(CELL("filename",$A$1),"\]"))),,0),"")
In this case I can get a result of
xxx
xxx
xxx
yyy
yyy
yyy
zzz
zzz
zzz
(which is a sequence based on the count of the first criteria found [3])
What I am hoping to achieve is
xxx
xxx
xxx
yyy
yyy
zzz
zzz
zzz
zzz
Which is a limited repeat of the value associated to the matching criteria
