Select function with multiple conditions

[Juamgv]

Hello Gurus,

I am trying to use Select function with multiple conditions and it seems not to work.

This is the scenario:

On the columns I have 6 different service types (1 to 6) with prices for each one and on the rows I have the vehicles type (1 to 10). 

If I input service type #1 and vehicle type #5 it should show the price for that combination and so on with each different combination. How can I do it since the data to pull it is not in only one column? (I will call it dynamic depending on the combination requested) 

Will appreciate your support. 

[TeeSee1]

INDEX(
 SELECT(
  table[price],
  AND(
   [service type] = [_THISROW].[service type],
   [vehicle type] = [_THISROW].[vehicle type]
  )
 ),
1)

[Suvrutt_Gurjar]

If you share how the the data schema is set up , the community could help you better. It is not exactly clear how service type and price type columns are set up.  Please do hide or obscure the actual data while sharing any screenshot.

[Juamgv]

Hello, thank you for the response. I don't have a column named price, it will depend on two factors as shown in the screenshot: 

If someone fills the form with: example; sedan compact and carpet shampoo as his 2 options it should show the price for that combination. Thank you in advance 

[Suvrutt_Gurjar]

Thank you. I think your question needs a more detailed analysis before being answered an d could go more towards app design. You may want to evaluate if this form user will fill- is it just to give the total estimate on the app screen itself or you want a separate quote record in the name of customer ?

In that case ( if you want quote record) you may need to use the concept of referencing to get the prices based on model and service types selected in the quote record. So you have one table for Quote records that references "Service Prices" table.

References between tables - AppSheet Help

App design 101 - AppSheet Help

[TeeSee1]

SWITCH(
 [service type],
 "service type 1", ANY(SELECT(table[service type 1], [vehicle type] = [_THISROW].[vehicle type])),
 "service type 2", ANY(SELECT(table[service type 2], [vehicle type] = [_THISROW].[vehicle type))],
 ...
)
 

You can get what you want with the expression above (replace [service type n] with your specific types) , but as @Suvrutt_Gurjar suggested, you may also want to re-visit your app design. 

Or at lease you may want to re-design this "pricing" table because it is not very scalable/flexible.

Of course I do not know the entire structure of your app and the current design may very well suffice.