work around for array formula in numbers

I'm curious about the ROW()-2 part. Why are you subtracting 2? In my sheet I have to subtract 1 to make it work and I'm trying to understand the inner workings. I get how the index is made using =B2&COUNTIF(B$1:B2,B2), but I don't get how it's extracted using =INDEX(Table 1::A,MATCH($A$1&ROW()−2,Table 1::$D,0))

Any help understanding this concept would be greatly appreciated!

The ROW()-2 takes the row number the formula is on (row 3 for the first body row in my example) and subtracts 2. The idea is to adjust for the number of Header Rows. Thus the formula in A3 of my example will construct the string "membersip1" and use MATCH to look up the row number for that in the Index column and feed that row number to INDEX to retrieve the corresponding value in another column. If you have only one Header Row in your table then you would subtract 1 instead of 2.

It's a little tricky the first time (because you have to remember to think about the Header Rows) but the underlying approach is simpler than it looks.

SG

Given this:

How about a filter?

Giving this:

You can then copy the body cells of the filtered table into another existing table, which you can sort, etc.

Or, if you want have the second table automatically refresh (but don't care about sorting it) you could do something like this.

Add an index column (that you can later hide if you want):

The formula in D2, copied down, is: =B2&COUNTIF(B$1:B2,B2)

And then use a formula like this to extract the values into the second table:

The formula in A3, copied right and down, is:

=INDEX(Table 1::A,MATCH($A$1&ROW()−2,Table 1::$D,0))

If you want, you can wrap it in IFERROR to suppress the red warning triangles in the lower rows:

=IFERROR(INDEX(Table 1::A,MATCH($A$1&ROW()−2,Table 1::$D,0)),"")

SG

Thank you so much for the explanation. I feel that I'm learning more from these online forums than I ever would have in a class. 🙂

Glad the explanations helped. Thanks for the nice feedback!

SG