search multiple text strings in a cell

[joecast]

hello all,
have this formula working:

=IF(ISERROR(SEARCH("PS",Table_Query_from_NAV[[#This Row],[No_]])),VLOOKUP(Table_Query_from_NAV[[#This Row],[No_]],Facility_Table,4,TRUE),"Linear Asset Type")

which is great, however, now i need to search for two other text strings. i have tried using OR, but may not be trying it in the right place or in the right way. so basicallyinstead of just searching "PS", i need to search "PS", "MH" or "SEG", and possibly others later on. any suggestions? thanks
joe

 

[Richard Schollar]

Hi Joe

Perhaps:

=IF(SUM(COUNTIF(Table_Query_from_NAV[[#This Row],{"*PS*","*MH*","*SEG*"})),Vlookup(Table_Query_from_NAV[[#This Row],[No_]],Facility_Table,4,TRUE),"Linear Asset Type")

 

[joecast]

thanks Richard,
the countif is only giving a result for the first value "*PS*", the others are not registering. tried switching the arguments, but only first one works. have seen
{"*PS*","*MH*","*SEG*"}
used but dont understand the logic so cant tell if it should work for countif. im guessing it is doing a rough OR kind of thing? thanks
joe

 

[Richard Schollar]

I did get the logic round the wrong way - think it should have been:

=IF(SUM(COUNTIF(Table_Query_from_NAV[[#This Row],{"*PS*","*MH*","*SEG*"}))=0,Vlookup(Table_Query_from_NAV[[#This Row],[No_]],Facility_Table,4,TRUE),"Linear Asset Type")

However, as far as I know that should work (although I don't have 2007 or 2010 to test on Table ranges I'm afraid).

 

[joecast]

YEEEES!!
thats got it! didnt reralise it was a CSE formula. Thanks
joe

=IF(SUM(COUNTIF(Table_Query_from_NAV[[#This Row],[No_]],{"*PS*","*MH*","*SEG*"}))=0,VLOOKUP(Table_Query_from_NAV[[#This Row],[No_]],Facility_Table,4,TRUE),"Linear Asset Type")

 

[Richard Schollar]

It's not a CSE formula