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?
Solved! Go to Solution.
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"
)
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"
)
It is giving the following error:
Cannot compare Number with List in ([Incentive] = ORDERBY(SELECT(Base Data[Incentive],([Incentive] <= [Incentive Expected])),โIncentiveโ,โTrueโ))
Whoops! I missed something! Iโve updated the expression above.
It worked. Thank you very much for your help.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |