Using a Lookup Function on a Variable Column

[Rodney_Reid]

Hi Guys and Gals,

I've done a bit of searching and can't find a solution to something that I would have thought would have had a standard function. 

I have a data table pre-populated with data and a blank data table for form functions.

In my form, I have two fields. 1 - Enum to choose which type of fruit and 2. Enum showing Pricing category(Price 1, Price 2, Price 3)

The user has the option in the form to choose the Fruit and then the pricing category(Column Name) and then the relevant data field needs to be displayed- i.e Fruit: BANANA  Pricing Category: Price 2 shows results $0.20

It seems that what is an easy-to-do lookup function in sheets/excell does not apply to Appsheet as Lookup() does not accept expressions and only constants.

Is there any other way to be able to reference data using a variable column name combined with a row?

I'm sure there is an easy enough solution to this but just cant think or find anything.

**PS - ideally would like to stay away from IF() functions**

[TeeSee1]

If you want to use a filtering condition with multiple variables, use SELECT() instead. LOOKUP is a wrapper of SELECT for a special case.

[Rodney_Reid]

Unless I'm understanding you incorrectly  - I'm finding that Select() has the same limited function whereby the Column needs to be set data and cannot be a Variable. 

[TeeSee1]

I see what you mean. Your right, You cannot specify a column represented by a variable. (actually AppSheet does not really have the concept of variables as in most programming languages)

Unless you want to use SWITCH/IF/IFS then you need to change your pricing table structure to

(ID), Product, Price Category, Price 

Then you can use ANY(SELECT(data[Price], AND([Product]=[_THISROW].[Product], [Price Category] = [_THISROW].[Price Category]))