Short version for countif

becklog

New Member
Joined
Dec 26, 2016
Messages
34
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,017
Perhaps:

ABCDE
1asdfLOCALABC
2xxxJKLyyyIMPORTDEF
3GHI
4JKL
5MNO

<tbody>
</tbody>
Sheet3

Wor
ksheet Formulas
CellFormula
B1=IF(ISNUMBER(LOOKUP(2^15,SEARCH($D$1:$D$5,A1))),"IMPORT","LOCAL")

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,473
Or, if you want to stick with COUNTIF,

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

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top