CODEWITHSUNDEEP
excelexcel-formula
Henry
Henry

Reputation: 49

Return multiple matches when range is dynamic

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.

Upvotes: 2

Views: 583

Answers (3)

Scott Craner
Scott Craner

Reputation: 152660

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

Upvotes: 2

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

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))) )

Upvotes: 0

EDS
EDS

Reputation: 2195

Try something like =SORT(ROW($A$1:$A$5)/($A$1:$A$5=$C$1)). This will get you the matches of a value in C1 with a spill formula, leaving behind #DIV/0 errors

See example below: enter image description here

Other answers that you might be able to take ideas from are here: Match Exact Row Number of Nth Largest Value In Data With Duplicates

Upvotes: 0

Related Questions