Hàm Lookup trong Appsheet

Quan trọng: Các giá trị trong mảng phải được sắp theo thứ tự tăng dần: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; nếu không, hàm LOOKUP có thể trả về giá trị không chính xác. Văn bản chữ hoa và chữ thường tương đương nhau.

To limit the rows included in the COUNT() range (e.g., as with

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

0), 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

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

1), you may add the results of an additional FILTER() sub-expression for each additional column:

I am very new to AppSheet but I see great potential. I want to design a barcode scanner, very simple stuff in theory:)

The Datacolumns in my googlesheet are: Barcode, Location, date of last check, date of future check

I got three questions:
a) After a barcode was scanned i would like to see the information of my GoogleSheet (Location, date of last check & date of future check) In Excel i would use the vlookup based on the scanned barcode.

b) based on the GPS coordinates i would like to filter my location list, e.g. GPS in the states, show only American Cities.

c) I would like to have the date of future check calculated based on the date of last check. Should be today plus 2 years as default but also editable.

Returns the value from the column of the matching row of a table or slice. It returns only a single column value, even if several rows match.

Sample usage

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone") returns the value of the Phone column from a row in the Restaurants data set with a Name column value that matches Bob's Burgers. Equivalent to ANY(SELECT(Restaurants[Phone], ([Name] = "Bob's Burgers"))). See also: ANY(),

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

0

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

1 from a location row, gets the name of a manager whose assigned location is the one identified by the current row. Equivalent to

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

2.

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

3 from an order detail row, gets the order date from the parent order. Equivalent to

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

4 or

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

5.

Syntax

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

6

• LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

  7 - The value to match (as with the

  LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

  8 operator) in the given data set and column. The value must be of a type suitable for comparison with the data set column. If

  LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

  9 is an expression, it is evaluated from the perspective of the lookup data set (

  ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

  0). To reference columns of the current context, dereference

  ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

  1.  See also:

  LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

  0

• ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

  0 - The name of the table or slice (the "data set") in which to search as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.

• ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

  4 - The name of the column in which to search as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.

• ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

  5 - The name of the column whose value should be returned as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.

Notes

The data set and column name arguments (

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

0,

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

4,

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

5) must be simple text values; they may not be column references or more complex expressions. They should be enclosed in quotes to avoid confusion should any match internal names used by AppSheet itself. For instance, an unquoted column name of

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

9 will produce an error.

Troubleshoot

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")0 is effectively a wrapper to the

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

0 function. As with LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")2, any column references are interpreted from the perspective of the data set being searched, not that of the data set from which the expression is run.

For example, consider this attempt from an order detail row to get the parent order's order date:

LOOKUP([Order ID], "Orders", "Order ID", "Order Date")

While this would produce a result, the result would likely be incorrect. Consider the equivalent LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")2 expression:

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")4

The comparison matches the order's LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")5 against itself, which will always be LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")6. Therefore, the LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")2 matches all orders, not just the parent of the order detail row as intended.

To reference the column values of the row from which LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")0 is used, dereference

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

1. For example: