Use of lookup to find nearest possible match

AppSheet AppSheet Q&A
1

I want to use lookup in AppSheet in a similar way to vlookup with the last argument True in excel. Is there any way to do so? Your article on LOOKUP explains how the exact match can be found. But in this case, I want to find the nearest match possible. If it is not possible via LOOKUP, is there any other way to do so?

2

Unfortunately, AppSheet has no built-in equivalent to the approximate-match feature of VLOOKUP().

In AppSheet, this:

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

is effectively a macro for this:

ANY(
  SELECT(
    table[result-column],
    ([search-column] = search-value)
  )
)

From my reading (because I’m not as proficient with Excel), the approximate-match feature of the Excel’s VLOOKUP() function produces the matching or closest lesser value. This behavior can be obtained the following in AppSheet:

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

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks ?

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 s…

4

It is giving the following error:
Cannot compare Number with List in ([Incentive] = ORDERBY(SELECT(Base Data[Incentive],([Incentive] <= [Incentive Expected])),“Incentive”,“True”))

1 Like
5

Whoops! I missed something! I’ve updated the expression above.

6

It worked. Thank you very much for your help.

1 Like