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

Thanks a lot for your time.

I need to do like column only because I have few criterias and each column have 3 options that is selected from the table like you mentioned above. But for my requirement I need almost 11 column and each column Is different criteria like priority, price, item type etc… but everything has a table that is listed and selected using select function.

For you reference. This is the table I’m using.

This is a normalized table. Why can’t you operate on this table as is? Where is it that you are using the “like” columns as suggested in this post above?

Here.

This table also looks normalized. I’m confused. Which columns relate to your original question shown above?

2 Likes

How do I do VLOOKUP()?

In Excel, VLOOKUP() searches the first column in a range for a given search value and returns the value of another column in the same range from the row containing the matching value. The match can be exact or “approximate”. An approximate match is the closest value less than or equal to the search value.

VLOOKUP() with exact match

The AppSheet equivalent to VLOOKUP() using exact match is LOOKUP():

LOOKUP(search-value, "table", "search-column", "result-column")

where search-value is an expression that produces the value to find; table is the name of the table to search; search-column is the name of the column in which to look for the search value; and result-column is the name of the column whose value should be returned if the search value is found in the search column of that same row.

LOOKUP() returns at most only one value, from the first row in which it finds a match.

See also: LOOKUP()

VLOOKUP() with approximate match

AppSheet has no direct equivalent for VLOOKUP() with approximate match, but this expression provides similar behavior:

LOOKUP(
  ANY(
    ORDERBY(
      FILTER(
        "table",
        ([search-column] <= search-value)
      ),
      [search-column],
        TRUE
    )
  ),
  "table",
  "key-column",
  "result-column"
)

where table is the name of the table to search; search-column is the name of the column in which to look for the search value; search-value is an expression that produces the value to find; key-column is the name of the key column of the table; and result-column is the name of the column whose value should be returned if the value of the search column of that same row is less than or equal to the search term.

The expression above returns at most only one value, the value of the result column from the row with the highest search column value less than or equal to the search value.

Note that this expression is expensive and may hurt app performance.

See also: ANY(), SELECT(), SORT()

3 Likes

Puedes por favor explicar la sintaxis de esta formula con un ejemplo ?

1 Like
  1. SUM(...) sums the list of values (...) produced by (2).

  2. SELECT(Orders[Total Amount], ...) gathers a list of values from the Total Amount column of select rows (...) in the Orders table.

  3. AND(...) selects only those rows in the Orders table that match all of the given criteria (...; see (3) & (4)).

  4. ([Customer] = [_THISROW].[Customer]) selects only rows with a Customer column value that matches the Customer column value of the row receiving the SUM() result in (1). Rows that have the same customer.

  5. ([Order Date] >= [_THISROW].[Order Date]) selects only those rows in the Orders table with an Order Date column value greater than or equal to the Order Date column value of the row receiving the SUM() result in (1). Orders on or after the given date.

3 Likes

Thanks!!!

1 Like

Hope you can help!

1 Like
2 Likes

Hi can I use this to:
get the value in row 1 col 1 and use that as the initial value of row 2 col 1?

Use case: I have 1 form where sometimes only 1 column is updated and the other columns of the form need to be filled with the previous row’s values for those columns.

Yep.

2 Likes

Thank you!

Hello,

I have tried to organize this list in alphabetical order and I have not succeeded, so I go to you to guide me to achieve it.

substitute(
			SELECT(
					registro alumnos[Nombre],
			
      		or(
          	([Categoria]=[IdPartidoLista].[_THISROW].[Categoria]),
            ([Categoria]=number([IdPartidoLista].[_THISROW].[Categoria])+1),
            ([Categoria]=number([IdPartidoLista].[_THISROW].[Categoria])-1)
          )
      
			),
	",",
	"
	"&
)

For your support, thank you.

1 Like

what I have not been able to find is the correct syntax, can you guide me with this?

Thank you

SORT() accepts a List as an argument. SELECT() returns a List. Therefore:

SORT( SELECT(...) )
2 Likes

Thanks a lot for that @Steve
Was wondering how to set the location for a stocktaking app dependable from the user, worked like a charm!
Thanks again

1 Like

@Marc_Dillon,
Thank you very much, it worked well for me

Thank you

2 Likes