Extract multiple matches without array formula or VBA

[Toadstool]

I often work with large datasets and need to extract multiple matches to a new sheet. I don't want to use VBA and I'd like to avoid the array formula overhead.

I'm happy with a helper column giving row numbers which match as I can then simply use INDEX to pull in all the columns I want for that row. I figured I could use MATCH for the first hit then just modify the MATCH to start searching from the next row, but I couldn't get that to work. I've found a way using INDIRECT and ADDRESS.

Example:
So in Sheet1 column A I have a set of codes to match against: SK4, SX5, XY4, NT5, XY4, XY4, AE7, LS7, etc.
I'm going to retrieve matches to XY4 so I want row numbers 3, 5 and 6.

In Sheet2 my helper column is off to the right, in column H. Cell H1 is empty and in H2 I put:
=MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1
which I then copy&paste down to the max hits I expect:
=MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H2+1,1)&":$A$99999"),0)+H2
etc.

This works and gives me 3, 5, 6 and then #N/A as there's no further matches.

So my question is: Can you figure out how to modify the MATCH function or a prettier way of the above without array formulae or VBA?

Thanks in anticipation!
Toadstool

 

[Dryver14]

This will return blank rather than N/A

=IFERROR(MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1,"")

 

[mikerickson]

The INDIRECT is also a volatile function. If the value in the H column is a row number
INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999")

Is the same range as INDEX(Sheet1!A:A,H1+1):Sheet1!$A$99999, which is not volatile

 

[Eric W]

Welcome to the forum!

You could also use a method like this:

	A	B	H	I	J	K
1	SK4					3
2	SX5		3	3		5
3	XY4		5	5	3	6
4	NT5		6	6
5	XY4		#N/A		5
6	XY4		#N/A		6
7	AE7		#N/A
8	LS7		#N/A
9

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1 I2 =IFERROR(MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1,"") J1 =IF(A1="XY4",ROW(),"") K1 =IFERROR(SMALL(J$1:J$99999,ROWS($A$1:$A1)),"") <tbody> </tbody>

<tbody>
</tbody>

H2 is your formula, I2 is Dryver14's.

The formulas in J and K are pretty simple, and they don't use the volatile INDIRECT function. You can remove the IFERROR from the K formula if you don't mind the #NUM ! errors.

 

[Toadstool]

Dryver14, Thanks but I actually removed error checking to simplify my example. It's simplification I'm after.

 

[Fazza]

Without formulas or VBA, consider these options advanced filter (ALT-D-F-A), and separately, query tables (ALT-D-D-N).

 

[Toadstool]

mikerickson, Sorry but I can't have explained properly. I'm OK with the INDEX to retrieve the data. I'm looking to get just just the matching row numbers into column H. My MATCH INDIRECT ADDRESS works OK but I'm wondering if there's a simpler way of getting column H populated with:
Column H
3
5
6
#N/A
#N/A
etc.

 

[Toadstool]

Welcome to the forum!

You could also use a method like this:

	A	B	H	I	J	K
1	SK4					3
2	SX5		3	3		5
3	XY4		5	5	3	6
4	NT5		6	6
5	XY4		#N/A		5
6	XY4		#N/A		6
7	AE7		#N/A
8	LS7		#N/A
9

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1 I2 =IFERROR(MATCH("XY4",INDIRECT("Sheet1!"&ADDRESS(H1+1,1)&":$A$99999"),0)+H1,"") J1 =IF(A1="XY4",ROW(),"") K1 =IFERROR(SMALL(J$1:J$99999,ROWS($A$1:$A1)),"") <tbody> </tbody>

<tbody>
</tbody>

H2 is your formula, I2 is Dryver14's.

The formulas in J and K are pretty simple, and they don't use the volatile INDIRECT function. You can remove the IFERROR from the K formula if you don't mind the #NUM ! errors.

Thanks for the welcome!

So H2 is the formula I'm using, I2 is adding the error check.
J1 means the Sheet2 I'm giving to my user will need to have as many rows of formulae in column J as there are rows of data (99,999) so that K1 through K20 will work in retrieving the 15-20 matches I expect.

I guess I could add the J1 as a helper column to the data in Sheet1, but that's going to cause challenges when the Sheet1 is rebuilt from the next data extract.

Thanks for the input!

 

[Toadstool]

Without formulas or VBA, consider these options advanced filter (ALT-D-F-A), and separately, query tables (ALT-D-D-N).

Fazza, Thanks for the suggestion but I do want to use a formula. It's an array formula I'm trying to avoid. I seem to have a curly-bracket phobia...

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​	SK4	​	3​
2​	SX5	​	idx​
3​	XY4	​	3​
4​	NT5	​	5​
5​	XY4	​	6​
6​	XY4	​	​
7​	AE7	​	​
8​	LS7	​	​

In C1 enter:

=COUNTIFS(A1:A8,"XY4")

In C3 enter and copy down:

=IF(ROWS($C$3:C3)>$C$1,"",INDEX(ROW($A$1:$A$8)-ROW($A$1)+1,
    AGGREGATE(15,6,(ROW($A$1:$A$8)-ROW($A$1)+1)/($A$1:$A$8="XY4"),ROWS($C$3:C3))))