CODEWITHSUNDEEP
arraysexcelif-statementsearchexcel-formula
kitchen800
kitchen800

Reputation: 227

Adding an OR Function to IF Statement

How do I make this formula search for 567 or 676 or 342 instead of just 567 which it is at the moment. 

=IF(ISNUMBER(SEARCH(""*567*"",B:B)),""Traction"","""")

Upvotes: 0

Views: 70

Answers (3)

JvdV
JvdV

Reputation: 75850

As a formula, you could simple try:

=IF(SUM(--ISNUMBER(SEARCH({567;676;342},B:B)))>0,"Traction","")

We have to use the semi-colon to evaluate each element in the array against all values in the B:B range.

Note1: This is a CSE formula and needs to be entered through CtrlShifterEnter

Note2: If this is indeed a follow-up on your previous question, you'll need to enter it like so:

<YourInputRange>.FormulaArray = "=IF(SUM(--ISNUMBER(SEARCH({""567"";""676"";""342""},B:B)))>0,""Traction"","""")"

Or, depending on your Locale:

<YourInputRange>.FormulaArray = "=IF(SUM(--ISNUMBER(SEARCH({""567"",""676"",""342""},B:B)))>0,""Traction"","""")"

Note3: As per my previous answer, you are comparing whole columns in an array formula. Be aware that this will impact your performance. I would advise your next step is to really get dynamic Range objects.

EDIT

A small explaination on why we either need to TRANSPOSE the values in column B:B OR those in our array. Let's imagine some sample data In B1:B3:

enter image description here

Let's focus on our formula; IF(SUM(--ISNUMBER(SEARCH({567,676,342},B1:B3)))>0,"Traction",""). Notice how we currently compare two vertical ranges. So Basically what we are asking the formula could look like:

enter image description here>enter image description here

The answer is triple-negative! So in this case the result will be "".

Now let's compare a vertical array against a horizontal one, our formula now looks like: =IF(SUM(--ISNUMBER(SEARCH({567;676;342},B1:B3)))>0,"Traction",""), to visualize this you could think of a matrix:

enter image description here

There is one positive match in our matrix! Now because this is comparing two arrays, we need to CSE the formula. And this is why we rather have a specified range, e.g. B1:B3, instead of a full column reference.

Upvotes: 2

SJR
SJR

Reputation: 23081

Try this. You don't need the wildcards with SEARCH.

=IF(COUNT(SEARCH({"567","676","342"},B:B)),"Traction","")

Upvotes: 1

Martin
Martin

Reputation: 16433

You can evaluate more than one clause using OR:

=IF(OR(ISNUMBER(SEARCH("567",B:B)),ISNUMBER(SEARCH("676",B:B)),ISNUMBER(SEARCH("342",B:B))),"Traction","")

Using OR allows you to specify two or more clauses which will return true if any of them are true:

=IF(OR(clause_1, clause_2, clause_3, etc.), true, false)

The OR function is documented here.

Upvotes: 2

Related Questions