If then ???

[ugowego]

Is there a way to shorten this statement or is there a better way to perform this?

=IF(AI2="321","EMS",IF(AI2="111","Fire",IF(AI2="411","Hazmat",IF(AI2="412","Hazmat",IF(AI2="424","Hazmat",IF(AI2="350","Rescue",IF(AI2="351","Rescue",IF(AI2="352","Rescue",IF(AI2="353","Rescue",IF(AI2="357","Rescue",IF(AI2="360","Rescue",IF(AI2="462C","ARFF",IF(AI2="462D","ARFF",IF(AI2="462E","ARFF",IF(AI2="462F","ARFF",IF(AI2="462G","ARFF",IF(AI2="462H","ARFF",IF(AI2="462I","ARFF",IF(AI2="462J","ARFF",IF(AI2="462K","ARFF",IF(AI2="462L","ARFF",IF(AI2="462M","ARFF",IF(AI2="135A","ARFF",IF(AI2="135B","ARFF",IF(AI2="135C","ARFF",IF(AI2="135D","ARFF",IF(AI2="135E","ARFF",IF(AI2="135F","ARFF",IF(AI2="135G","ARFF",IF(AI2="135H","ARFF",IF(AI2="135I","ARFF",0)))))))))))))))))))))))))))))))

 

[ps1]

uhm vlookup? Too many IFS. Sort the data into two columns, name that as a range then use the formula.

=vlookup(AI2,"range to lookup",2,0)

 

[FormR]

I agree that a lookup table makes the most sense; but alternative function is a little shorter than what you currently have:

=IF(AI2="321","EMS",IF(AI2="111","Fire",IF(OR(AI2={"412","424"}),"Hazmat",IF(OR(AI2={"351","352","353","357","360"}),"Rescue",IF(OR(AI2={"462C","462D","462E","462F","462G","462H","462I","46J","462K","462L","462M","135A","135B","135C","135D","135E","135F","135G","135H","135I"}),"ARFF",0)))))

 

[tinguyen]

Another solution :

=IF(AI2=321,"EMS",IF(AI2=111,"Fire",IF(OR(AI2={411,412,424}),"Hazmat",IF(AND(AI2>=350,AI2<=360),"Rescue",IF(OR(LEFT(AI2,3)={"462","135"}),"ARFF",0)))))