Selecting or Filtering Entire Rows from a Table

[Jon_Ferry]

I am trying to figure out how to select or filter out entire rows from a table. I have tried the FILTER function, and the MAP function paired with INDEX, MATCH, but nothing seems to work. It seems as if these functions only work with 1D arrays.

For example, here is what I tried most recently, which did not work:

Let(
Rw, MATCH(Props, PIN_TAG, 0),
MAP(Rw, LAMBDA(X, INDEX(TAGtable, X, )))
)

In the above, the vars Props, PIN_TAG and TAGtable are all defined names. The Rw variable works just fine and results in an array of numbers (e.g., 1, 5, 18, 23, 40). My expectation was that by using the Map function, I could feed these values into an Index function, and return just the 1st, 5th, 18th, etc. rows from the table, but instead, I got a #CALC error.

Again, I have tried this same concept with the FILTER function, I've combined the MAP and the FILTER functions. I have tried FILTER and BYROW together, but nothing seems to work. Does anyone out there know how to solve this?

One more note, the values in the TAGtable are text values, so doing a SUM or SUMIF would result in an error.

[#2]

@Jon_Ferry 

=LET(
Rws,ROW(B2:B19)-1,
Rw, MATCH(A2:A19,B2:B19,0),
isnum,ISNUMBER(MATCH(Rws,Rw,0)),
FILTER(D2:F19,isnum)
)

Does this return the intended result in the sample data?

 

[#3]

@OliverScheurich 
It did not quite work. Here is how I tried to apply it. I am providing more of my original formula so you can get more context for my defined variables.

N, SEQUENCE(Nall),   M, MATCH(N, N),

PIN, INDEX(PIN_PrIn, M,1),
TIF_YN, INDEX(TIF_Info, M, 1),
RPA, INDEX(TIF_Info, M, 2),

Props, FILTER(PIN, TIF_YN = "Yes"),

Rws, ROW(Props) - 1,
Rw, MATCH(Props, PIN_TAG, 0),
isnum, ISNUMBER(MATCH(Rws, Rw,0)),
FILTER(TAGtable, isnum)
)

As you can see, my Props variable is the result of a filter formula, rather than a direct cell or table reference. When I tried to do Row(Props) -1, the result was a 6 row by 1 column array in which all of the cells contained a #VALUE! error. Because of that, the way I tried to apply your solution did not work, and also resulted in a #VALUE! error.

[#4]

@OliverScheurich,

I did find one solution that works, but it required the creation of a helper table. Here is the solution that worked, and below, I will post the formula for the helper table.

=LET(N, SEQUENCE(Nall),   M, MATCH(N, N),
Cta, COUNTA(INDEX(Taxing_Authorities_List,,2)),
Nta, SEQUENCE(1, Cta),    Mta, MATCH(Nta, Nta),

TIF_YN, INDEX(TIF_Info, M, 1),
Include, BYCOL(IF(TIF_YN = "Yes", 1, 0) * INDEX(TALT_Table, M, Mta), LAMBDA(X, IF(SUM(X) > 0, 1, 0))),

TANames, TRANSPOSE(MAKEARRAY(Cta, 1, LAMBDA(r,c, INDEX(Taxing_Authorities_List,r,2)))),

TRANSPOSE(FILTER(TANames, Include = 1, "None"))
)

And here is the formula for the helper table. For this table, the formula is by row. Each row represents one Property ID Number (PIN). I then selected the entire resulting table for all of the PINs and named it as a named range in the Formulas menu. This is what I called the TALT_Table, as used in the above formula.

=LET(
Cta, COUNTA(INDEX(Taxing_Authorities_List,,2)),
TANames, TRANSPOSE(MAKEARRAY(Cta, 1, LAMBDA(r,c, INDEX(Taxing_Authorities_List,r,2)))),

Ntag, SEQUENCE(1,COLUMNS(TAGtable)), Mtag, MATCH(Ntag, Ntag),

X, LAMBDA(X, ISNUMBER(MATCH(TANames,INDEX(TAGtable,X,Mtag),0))*1)(MATCH($A2,PIN_PrIn,0)),

IF($A2 <>"", X, "")
)