Re: The LOOKUP function behaviour

racatapum · 08-12-2020 11:39 PM

The VLOOKUP function behaviour…
HI! I am detecting an extrange behaviour with the VLOOKUP function. To place some context, I am applying a format rule to alert the user that a certain value (weight) he introduced in a form is higher or smaller than expected. To do this, the system looks for the product id (an ugly concatenation of product,units and length) in a reference table to compare the value with a max and min value.
Well, the question is that when you use this alert system in google spreadsheets, there is no problem. If the product id is not found in the reference table, it just gives “N/A”. But inside appsheet, I see that VLOOK funtion aparently gives a numeric value equal to “0”. So the “alert” is always displayed if the product id is new, for example for a particular package with less units than usual.
Well, the question is easily solved with a and([value]<>“0”;“theVLOOKformula”), but… Am I right? is that the normal VLOOK behaviour?

Aleksi

That’s not a normal behavior, it should give you a blank value if the record is not found. May I ask what kind of LOOKUP() are you using?

Steve

AppSheet doesn’t have a VLOOKUP() function.

racatapum

I am sorry, VLOOKUP is a mistake, I meant lookup.
The function was:
[Peso]>LOOKUP([Resumen];REF2;Grupo;maximo)
“Peso” (weight) is the input value. “resumen” is the product id. “Ref2”, the reference table, “Grupo” is the product id in the reference table, and “maximo” is the max admissible value for “peso”. If The Lookup function is unable to find the “resumen” value in the column “grupo” (for example, it happens when we have a new product), instead of not throwing any result for the exression, it returns the value “TRUE”. Thats why I supposed that the lookup expression gives “0”. “Peso” is always bigger than zero.
In fact, I placed the expression:
and([Peso]>LOOKUP([Resumen];REF2;Grupo;maximo);LOOKUP([Resumen];REF2;Grupo;maximo)<>0)
This way, I “force” a “false” value if the lookup expression gives a “0”, and then the function works as I expect it to.

Steve

See Troubleshooting here:

Tin_Nguyen

I have two tables. One for a quote and one for the data. For the quote, I want to look up a specific value in the data table. I have an enum field to choose between 2 options. If the first option is selected then the dependent field will automatically change to the column in the data field. However, if the second option is highlighted, then I want to use that same value but divide it by 2. I tried using an IFS() and data[column] expression but that didn’t work.

racatapum

Let me see if I understand you. You have a table “quote” (with a column you want to lookup, called [product], for example, and an [enum] column with"option1" and “option2”), and a “reference” table (with column [data] and column [value]).
Well to do what you ask for (select a product, lookup for its “value”, and if I choose the “option1” I get the “value”, but if I choose “Option2”, I got the same value but halved) I think I would use, in a column “result”:
=ifs([enum]=“option1”;lookup([product],reference;data;value;[enum]=“option2”;(0.5lookup([product],reference;data;value)))
(note that you cannot type "0.5value" inside the lookup expresion).

Tin_Nguyen

Thank you! I did something similar and it worked!