Hi,
A formula like this:
=IF(A1:A6="cat",ADDRESS(ROW(A1:A6),COLUMN(A1:A6)),0) will return: {"$A$1","$A$2",0,"$A$4",0,"$A$6"}
You can replace the red 0 with "" if you need this.
If you use the formula like this:
=IF(A1:A6="cat",A1:A6,0) it will return a range of those values: {"cat","cat",0,"cat",0,"cat"}
Or, you can return a corresponding set of values from the next column:
=IF(A1:A6="cat",B1:B6,0) returns: {10,20,0,40,0,60}
Note: use them as CSE formulas.
Cheers,
Catalin Bombea