Use of lookup to find nearest possible match

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?

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"
)
1 Like

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

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

It worked. Thank you very much for your help. :slight_smile:

1 Like