Is there an excel formula of chain of formulas that could return me either an array with values (or string with CSVs) or a range address of cells corresponding to a given type/category value?
Example
2 Answers
If you have office365 you can use =FILTER(A:A,B:B="cat2")
Else you need to use =IFERROR(INDEX($A$5:$A$10,SMALL(IF($B$5:$B$10="cat2",ROW($B$5:$B$10)),ROWS($1:1))),"") enter with ctrl + shift + enter and copy down.
For the cell address, assuming you max have 2 times the same value:
=CELL("address",INDEX(A:A,MATCH("cat2",B:B,0),1))& ":" & CELL("address",INDEX(A:A,MATCH("cat2",INDIRECT("B"&MATCH("cat2",B:B,0)+1 &":B100"),0),1))
Related