IFS with multiple conditions check in Google Sheet

Question

I am trying to get a result that meets the following conditions..

1. If the Craft is Crane Operator or all Riggers including Rigger I, Rigger II, Rigger III then display the value of column F in the column H
2. If the Craft is H.E. Operator then display the value of column G in the column H
3. All other Crafts excluding Crane Operators, H.E. Operators and all Rigger including Rigger I, Rigger II, Rigger III then display the value of column E in the column H

I tried the below formula but does not work..

=IFS(C7="Crane Operator", QUERY(F7,"SELECT *"),C7="Rigger III", QUERY(F7,"SELECT *"),C7="Rigger II", QUERY(F7,"SELECT *"),C7="Rigger I", QUERY(F7,"SELECT *"),C7="H.E. Operator", QUERY(G7,"SELECT *"))

You can access Goggle Sheet to check the data:

https://docs.google.com/spreadsheets/d/1_jOn0vyv6osaoppeU9TXX1boOlZbnoCFy3eWsddP7uc/edit?gid=14738129#gid=14738129

Answer 1

Crafts are in column D rather than column C. Use regexmatch(), like this:

=ifs( 
  regexmatch(D7, "Crane Operator|Rigger I{1,3}"), F7, 
  D7 = "H.E. Operator", G7, 
  true, E7 
)

See regexmatch().