Lookup() and Today()

[AhmedEsmail]

Hello everyone, 

I'm a bit new to Appsheet, was trying to create an equation for a ref column, I have two tables (head count & schedule), the column I'm creating in head count table should search for employee names in both tables then return the correct today's shift for each employee.

I have named the shift headers in Schedule table from 1 to 30 as of day of the month. 

I'm able to get the correct results only when identifies the column name (let's say today's 12th, I have to write it as "12")

LOOKUP([Full Name], Schedule, "Agent Name", "12")

I tried to use today(), text(today()) but nothing worked, I even placed text(today(), "dd") as a helper column in the Google sheet (database) but yet the app won't recognize this column the next day.

Lookup(HC.Full Name, "SCH", "Agent Name", TEXT(TODAY(), "dd"))

The error I get: Function 'LOOKUP' must be passed constant values for its tableName, lookupColumn, and returnColumn parameters: LOOKUP(lookupValue, tableName, lookupColumn, returnColumn). Consider wrapping the constant parameters in double quotes to ensure correct parsing 

Is there any work around for this limitation? 

[Marc_Dillon · Accepted solution]

SWITCH( TEXT(TODAY() , "dd" ) , 
"1" , LOOKUP( ...... "1" ) ,
"2" , LOOKUP( ...... "2" ) ,
...

[AhmedEsmail]

Thanks a lot mark and sorry for the late reply! This really helped bypassing the LOOKUP limitations.