Can you use a variable in the LOOKUP function...

(Matt White) #1

Can you use a variable in the LOOKUP function for returnColumn?

I am having some trouble trying to use a variable as the returncolumn in a LOOKUP.

LOOKUP(lookupValue, tableName, lookupColumn, returnColumn)

I have a virtual column “DefaultTeam” that contains the name of a column in a table. Trying to use it like this:

LOOKUP([match], “Schedule”, “matchnum”, [DefaultTeam])

When I try this, Appshet errors stating that I need exactly 4 arguments. If I substitute [DefaultTeam] for a literal such as “Red1” it works fine.

Do you see any way to do this?

(Aleksi Alkio) #2

How about… LOOKUP([_THISROW].[match],Schedule,matchnum,DefaultTeam)


It looks like the last 3 inputs must be “constant value” expressions, so I don’t think you can dynamically set the return column like that. I think Aleksi’s version will just look for a column called “DefaultTeam” in the lookup table.

(Matt White) #4

@Aleksi_Alkio @Adam_Stone_AppSheet Thanks - as Adam mentioned it seems like you cannot use a variable in that position. I guess this is now a feature request! Here are the errors I get:

using: * [DefaultTeam] - it states I need exactly 4 arguments.

  • DefaultTeam - it states "Column Name ‘team’ in Schema ‘QMatch_Schema’ of Column Type ‘Number’ has an invalid app formula ‘LOOKUP([_THISROW].match],Schedule,matchnum,DefaultTeam)’. Unable to find column ‘DefaultTeam’, did you mean ‘DefaultTeam’?
(Aleksi Alkio) #5

One square bracket is missing. Try LOOKUP([_THISROW].[match],Schedule,matchnum,DefaultTeam)