Short version for countif

[becklog]

Hi,

I have a formula like this

=IF(OR(COUNTIF(A1,"*"&"ABC"&"*"),COUNTIF(A1,"*"&"DEF"&"*"),COUNTIF(A1,"*"&"GHI"&"*"),COUNTIF(A1,"*"&"JKL"&"*"),COUNTIF(A1,"*"&"MNO"&"*"),"IMPORT","LOCAL")

Is there a way to just put all wildcards condition in a column to make to formula shorter? I have a total of 47 wildcards.

 

[Eric W]

Perhaps:

	A	B	C	D	E
1	asdf	LOCAL		ABC
2	xxxJKLyyy	IMPORT		DEF
3				GHI
4				JKL
5				MNO

<tbody>
</tbody>

Sheet3​

Wor ksheet Formulas Cell Formula B1 =IF(ISNUMBER(LOOKUP(2^15,SEARCH($D$1:$D$5,A1))),"IMPORT","LOCAL") <tbody> </tbody>

<tbody>
</tbody>

 

[Tetra201]

Or, if you want to stick with COUNTIF,

=IF(SUMPRODUCT(COUNTIF(A1,"*"&$D$1:$D$5&"*")),"IMPORT","LOCAL")