multiple search criteria formula - needs to be simplified.

=if(not(iserror(search("A",D2))),"Arts",if(not(iserror(search("W",D2))),"World",if(not(iserror(search("E",D2))),"Enterprise",if(not(iserror(search("D",D2))),"Discovery",if(not(iserror(search("i",D2))),"i-College",if(not(iserror(search("S",D2))),"Sixth Form",""))))))


It looks in a text for either an "A", "D", "W", "i", "S", "E". And then returns the values as above.

Is there a way to combine the SEARCH function with an index function?

The text is not always in a consistent place within the text hence the use of SEARCH.


Must be usable in Google Sheets.