I have a column which is a dynamic named range:
A
A
B
C
A
How can I apply a MATCH
or other formula which returns the row or cell address of all A
values in the dynamic named range?
A dynamic named range is used so all row or address of A
values can automatically appear as a spill rather than having to drag down the formula.
I have seen numerous formulas in this structure:
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1)))
However, most of these seem to return the first instance (row or cell address of the first match) and requires the formula to be dragged down manually rather than a spill, which is more preferable.
Any help will be greatly appreciated.