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

 

[Fazza]

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

OK.

Depending on what is required, and particularly for large datasets, database type approaches (without any formulas) can be efficient.
Queries, especially parameterised queries and also queries that join two tables (the full source table and the small table defining which records to return) can do a lot of work without any formulas (and can be without VBA).
For some things formulas will be fine and for others they can take tens of minutes to do what a query can do in less than a second.

regards