FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()

Core concepts & functions

Adding & updating rows

Excel alternatives

Last row of the spreadsheet

Rows of this thing only

Miscellaneous

54 Likes
LOOKUP if one of the columns filled in by the user matches
Select(), Filter() or Lookup()
Initial Values: SELECT, ANY, or LOOKUP?
How to Select oldest row [date] from a table
How can I select a column in the second row?
Hi guys I am trying to get data from another table using Select
Different view for add vs edit
How to add last data to virtual column?
Get last row
Error con la fórmula no me trae los datos correctos
AppSheet Training Resources & Useful Links
Trouble with expression to filter a slice
Last Update of last sync in my column
Update field in table when record is added to child table
How to select only a few rows of options in my reference table?
Weekly Report
How do I reference the value of the previous row?
Last record from another table in virtual column
Filter by the email address and obtain the barcode at the last row
How to display the last input data in the form
Expression for checking if a value exist in Column
User jobs issue
Detect similar phone numbers
How Can I show information the other table?
Occupation rate by day
Get initial value of a ref column based on the current user
I need to create an status row that is connected to another row on another table
Limit Data shown on Deck View
How to retrieve a value
Expression Assistance Required
Estado de cuenta bancario
How to calculate Time Difference between two rows
Don't clear data in inputs after clicking Submit
Crew Managment System
eNCONTRAR UN VALOR DE LA PENULTIMA FILA
Create daily water consumption dashboard
Inline View from Reference Rows that do not include key of parent table. Is it possible?

How do I get a column value from the last row of the spreadsheet?

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

See also: MAX()

11 Likes

How do I get the last row of the spreadsheet?

MAXROW(
  "My Table",
  "_ROWNUMBER"
)

Replace My Table with the name of the table whose row you want.

4 Likes

How do I get a column value from the last row of this thing only?

LOOKUP(
  MAX(
    SELECT(
      My Table[_ROWNUMBER],
      ([_THISROW].[Thing] = [Thing])
    )
  ),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want.

See also: MAX()

10 Likes

How do I get a list of rows of this thing only?

FILTER(
  "My Table",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

7 Likes

How do I get a list of column values from rows of this thing only?

To include duplicate values in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; Wanted Column with the name of the column whose value you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

To omit duplicates so that each value occurs only once in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing]),
  TRUE
)
8 Likes

How do I do SUMIF() or SUMIFS()?

SUM(
  SELECT(
    Orders[Total Amount],
    AND(
      ([Customer] = [_THISROW].[Customer]),
      ([Order Date] >= [_THISROW].[Order Date])
    )
  )
)

Use SELECT() to gather values from a single column only.

See also: AND(), SUM()

6 Likes

How do I do COUNTIF() or COUNTIFS()?

COUNTIF(col, expr), where col is a whole-column reference (e.g., A), is similar to:

COUNT(
  FILTER(
    "table",
    ([col] expr)
  )
)

COUNTIFS(col1, expr1, col2, expr2, ...), where col1 and col2 are each whole-column references, is similar to:

COUNT(
  FILTER(
    "table",
    AND(
      ([col1] expr1),
      ([col2] expr2),
      ...
    )
  )
)

To limit the rows included in the COUNT() range (e.g., as with COUNTIF(A3:A7, ...)), include the limit criteria in the FILTER() sub-expression, similar to this:

COUNT(
  FILTER(
    "table",
    AND(
      ([_ROWNUMBER] >= low-row-num),
      ([_ROWNUMBER] <= hi-row-num),
      ([col] expr)
    )
  )
)

To count across multiple columns (e.g., as with COUNTIF(A:C, ...)), you may add the results of an additional FILTER() sub-expression for each additional column:

COUNT(
  FILTER(...)
  + FILTER(...),
  ...
)

See also: AND(), COUNT()

4 Likes

Your a Beast :100:

2 Likes

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related?

FILTER(), LOOKUP(), MAXROW(), and MINROW() are all built upon SELECT(), so they all share similar capabilities and limitations.

  • FILTER("table", select-expression) is equivalent to:

    SELECT(
      table[key-column],
      (select-expression)
    )
    
  • LOOKUP(match-expression, "table", "match-column", "result-column") is equivalent to:

    ANY(
      SELECT(
        table[result-column],
        ([match-column] = (match-expression))
      )
    )
    

    Note that if multiple rows match, the result value from only one row, chosen at random, will be returned.

  • MAXROW("table", "column", select-expression) is equivalent to:

    ANY(
      SELECT(
        table[key-column],
        AND(
          (
            [column] = MAX(
              SELECT(
                table[column],
                (select-expression)
              )
            )
          ),
          (select-expression)
        )
      )
    )
    

    Note that if multiple rows contain the maximum value, only one row, chosen at random, will be returned.

  • MINROW("table", "column", select-expression) is equivalent to:

    ANY(
      SELECT(
        table[key-column],
        AND(
          (
            [column] = MIN(
              SELECT(
                table[column],
                (select-expression)
              )
            )
          ),
          (select-expression)
        )
      )
    )
    

    Note that if multiple rows contain the minimum value, only one row, chosen at random, will be returned.

  • REF_ROWS("table", "ref-column") is equivalent to:

    SELECT(
      table[key-column],
      ([ref-column] = [_THISROW])
    )
    
11 Likes

@Steve, This is excellent. Thanks for doing all this.

2 Likes

How do I get the last row of this thing only?

MAXROW(
  "My Table",
  "_ROWNUMBER",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

3 Likes

Bookmarked and appreciated

1 Like

All very useful references @Steve. Thank you.

1 Like

Is this a new row?/Does this row already exist?

Is this a new row?

NOT(IN([_ROWNUMBER], My Table[_ROWNUMBER]))

or:

ISBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

Does this row already exist?

IN([_ROWNUMBER], My Table[_ROWNUMBER])

or:

ISNOTBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

In the above, replace My Table with the name of the table about which you’re inquiring.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: FILTER(), IN(), ISBLANK(), ISNOTBLANK(), List from Table-Column Reference, NOT()

8 Likes

What is the previous value of this column?

The simplest way is to dereference [_THISROW_BEFORE]:

[_THISROW_BEFORE].[Wanted Column]

Replace Wanted Column with the name of the column whose value you want.

LOOKUP() could also be used:

LOOKUP(
  [_THISROW].[_ROWNUMBER],
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: List from Table-Column Reference, LOOKUP()

7 Likes

You can also use the THISROWBEFORE /AFTER functions… They’re supposed to be for reports and such, but they seem to work fine in other expressions.