I am trying to get a result that meets the following conditions..
- 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
- If the Craft is H.E. Operator then display the value of column G in the column H
- 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
3
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().
Try this;
=IF(OR(D7 = "Crane Operator",D7 = "Rigger I", D7 = "Rigger II", D7 ="Rigger III",D7),F7,IF(D7 = "H.E. Operator", G7, E7))