Forum Discussion
Using Filter to return rows with certain conditions
I want to return the rows where "maturity date" column is higher than "Asofdate" or is lower than "IssueDate". I tried this formula
=FILTER(INDIRECT(C12&"[#All]"),(INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+(INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]")))
but it gives me #Value error. What would be the correct formula? C12 definitely has the correct table name so can't pin point
=FILTER(INDIRECT(C12&"[#All]"), VSTACK(TRUE, (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+ (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]")) ) )=FILTER(array,include,[if_empty])
In your formula the number of rows of "array" and "include" are different. The "array" - INDIRECT(C12&"[#All]" - refers to all 13 rows of the table. The "include" refers to 12 rows. And therefore the formula returns a VALUE error. The above formula adds a TRUE critieria to the "include" argument to return the header row.
=VSTACK(INDIRECT(C12&"[#Headers]"), FILTER(INDIRECT(C12), (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+ (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]"))))I'd suggest to filter the bottom of the table (without the headers) and then vertically stack the headers and the result of the filter as shown with the second formula and screenshot.
4 Replies
This may not be sensible but it's fun to play with and one way to avoid INDIRECT. It may be an option if there's a reasonable number of tables and you're using an input cell to pick one of them. This example has 3.
=LET( StackedTables, VSTACK(LAMBDA(Alpha), LAMBDA(Bravo), LAMBDA(Charlie)), tbl, XLOOKUP(input, TableList, StackedTables, "")(), keep, (TAKE(tbl, , -1) > CHOOSECOLS(tbl, 2)) + (TAKE(tbl, , -1) < CHOOSECOLS(tbl, 3)), FILTER(tbl, keep, "none") )As variant
=LET( tbl, INDIRECT($C$12), headers, INDIRECT($C$12 & "[#Headers]"), maturity, XLOOKUP("MaturityDate", headers, tbl), AsOfDate, XLOOKUP("AsOfDate", headers, tbl), IssueDate, XLOOKUP("IssueDate", headers, tbl), VSTACK( headers, FILTER( tbl, (maturity < AsOfDate) + (maturity < IssueDate) ) ) )=FILTER(INDIRECT(C12&"[#All]"), VSTACK(TRUE, (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+ (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]")) ) )=FILTER(array,include,[if_empty])
In your formula the number of rows of "array" and "include" are different. The "array" - INDIRECT(C12&"[#All]" - refers to all 13 rows of the table. The "include" refers to 12 rows. And therefore the formula returns a VALUE error. The above formula adds a TRUE critieria to the "include" argument to return the header row.
=VSTACK(INDIRECT(C12&"[#Headers]"), FILTER(INDIRECT(C12), (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+ (INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]"))))I'd suggest to filter the bottom of the table (without the headers) and then vertically stack the headers and the result of the filter as shown with the second formula and screenshot.
The problem is with [#ALL] because that will return the entire table including the header and the other references only include the data portion which means the Filter array has more rows than the Filter include.
If you want the table header then use VSTACK:
=VSTACK(INDIRECT(C12&"[#HEADERS]"),
FILTER(INDIRECT(C12),(INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[IssueDate]"))+(INDIRECT(C12&"[MaturityDate]")<INDIRECT(C12&"[AsOfDate]")),"none"))
