Using wildcards in IF statement

[halefamily104]

Hello

I am trying to use wildcards in an If statement but cant seem to get it to work. Here is what I have so far

=IF(OR(ISNUMBER(SEARCH({"*FF*","*HV*","*CTC*","*CLC*"},I12:V12))),"Full","Error")

If I only reference one cell (I12) it works beautifully but when I add in the additional cells (I12:V12) for the formula to look at, this

formula does not return the correct value.

Any ideas?

Thanks


​

 

[Eric W]

Are you looking to say "Full" if any of those codes are found in any of the cells of I12:V12? Then try:

=IF(OR(ISNUMBER(SEARCH({"FF";"HV";"CTC";"CLC"},I12:V12))),"Full","Error")
and confirm it with Control+Shift+Enter.

When you just had 1 cell to examine, you had a 1-d array formula. The array constant {} told Excel it was an array, so everything was fine. By changing I12 to I12:V12, it's now a 2-d array formula. This requires that you use CSE, but also it requires that one of the dimensions is horizontal (I12:V12), and the other dimension must be vertical. That's why I had to change the commas to semicolons in the array constant.

Other than that, you don't need the * when you use SEARCH.

Hope this helps!