Return multiple matches when range is dynamic

user_12905324 0
Viewed 121

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.

excel-formula excel
3 Answers

Use FILTER:

 =FILTER(ROW(A1:A5),A1:A5=C1)

This will return the Row number everywhere they match.

enter image description here

From there it is pretty easy to include that in INDEX:

=INDEX(B:B,FILTER(ROW(A1:A5),A1:A5=C1))

enter image description here

If you do the full column in the index, you do not need the ROW($A$2:$A$8)-ROW($A$2)+1 as returning the actual row instead of relative works just fine.

enter image description here

user_48515900
user_109627890

You could be thinking of:

=SMALL(IFERROR(ROW(A1:A5)*MATCH(A1:A5,C1,0),FALSE),ROW(A1:INDEX(A:A,COUNTIF(A1:A5,C1),)))

Where your data start in A1 and your compare value is in C1.

result

If you have Excel 365, this is easier:

=INDEX(SORT(ROW(A1:A5)*MATCH(A1:A5,C1,0)),SEQUENCE(COUNTIF(A1:A5,C1)))

or better yet:

=LET( dataRange, A1:A5,
       compareTo, C1,
       INDEX(SORT(ROW(dataRange)*MATCH(dataRange,compareTo,0)),SEQUENCE(COUNTIF(dataRange,compareTo))) )
user_46171210
Related