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
asdf	LOCAL	ABC
xxxJKLyyy	IMPORT	DEF
GHI
JKL
MNO

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Wor
ksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=IF(ISNUMBER(LOOKUP(2^15,SEARCH($D$1:$D$5,A1))),"IMPORT","LOCAL")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Or, if you want to stick with COUNTIF,

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