Lookup and IF Statements

[Michael_Venner]

I need some help with what i believe should be a lookup expression but i may be incorrect.

Within excel/Google sheets it is pretty simple but I dont appear to be able to get it to work within appsheet. I use a combination of Lookup and If Statements.

I basically have a lookup table that has a number of rows with employee numbers on. then there are a number of columns with different values. The column to select will be based on another variable selected by the user. Basically there are two inputs from the user in excel to give the value.

here is the equation in google sheets that i use:

=VLOOKUP(‘Client Details’!B18,DayTable!$F$2:$P$24,IF(‘Client Details’!C10=“High”,2,IF(‘Client Details’!C10=“Medium”,3,IF(‘Client Details’!C10=“Low”,4)))))

I am trying to copy this into appsheet but I cant get it to work. i used a simple deref to begin with for employee numbers which was easy, when i then tried bringing in the high, medium, or low variable that didnt work.

The user will select a risk of either high, medium or low. they will then enter the employee numbers. The value returned will be one of 3 different columns (one for high, one for low, one for medium) for the row of employees they have selected.

Ive uploaded the lookup table in case that helps.

Copy of Contract Review-ERP - Day Table.pdf (27.3 KB)

Ive tried using lookup but i cant get it to work

I hope that makes sense, any help would be greatly appreciated

[Steve]

Please describe the needed logic in plain language. Please do not use AppSheet or Excel terms in your description.

[Michael_Venner]

Thanks for your response, I have actually got close to the answer but its only working when i select medium. This is what i have currently:

IF([ISO 9001 Risk]=“Medium”,LOOKUP([_THISROW].[Effective Employees],“Day Table”,“Effective Employees”,“9001 medium stage 1 & Stage 2”),"")

IF([ISO 9001 Risk]=“High”,LOOKUP([_THISROW].[Effective Employees],“Day Table”,“Effective Employees”,“9001 High stage 1 & Stage 2”),"")

IF([ISO 9001 Risk]=“Low”,LOOKUP([_THISROW].[Effective Employees],“Day Table”,“Effective Employees”,“9001 Low stage 1 & Stage 2”),"")

Its giving me the correct answer when medium is selected, but giving me blank when i select high or low.

I think it might require IFS instead?

[Steve]

Michael_Venner:

I think it might require IFS instead?

Yep!

[Michael_Venner]

Im trying to have the user select two options; one is to select the number of employees, the other is to select whether the risk is high, medium or low.

When the user selects both variables, the table will then be reviewed and the result presented. One of the three columns will be returned

for example, if they select 11-55 employees and high risk, the result will be 3, if they select the medium risk for the same employees the result will be 2.5, if they select low risk the result will be 2