Help With Word Based Excel Formula Using IF/OR Statement

Place this in V1 and copy it down as far as you need

=IF(OR(Q1="Alton",Q1="Alamo",Q1="Pharr"),McAllen”,"")

an OR function requires complete statements in each part, so
=OR(logical_test1,logical_test2,logical_test3)
So you need complete tests in each section, not just possible answers you want to compare against
=OR(A1=Criteria1,A1=Criteria2,A1=Criteria3) will work, but
=OR(A1=Criteria,Criteria2,Criteria3) will not work, because the logical_tests no longer return a logical TRUE or FALSE value

The Excel formula tooltip puts the [ ]'s around optional fields to identify that they are optional, placing the [ ]'s in the formula will cause errors though.

Also, I hit another snag. I'm wondering is there a way to use multiple if statements in one cell? So please let me try and explain what I am trying to accomplish and then I will show you my current formula.

Like before I am trying to accomplish something very similar but a bit more complex.

Basically what I need Excel to do is to render the word "McAllen" in column "V" based off of weather or not there is a match in column "Q" of "Alton", "Alamo" or "Pharr". If column "Q" does not match "Alton", "Alamo" or "Pharr" then I need column "V" to remain blank.
-or-
If the words "Alpine" or "Alamogordo" are in the "Q" column then the "V" column needs to render "El Paso"; if neither "Alpine" or "Alamogordo" are in column "Q" then again column "V" would need to remain blank.

Here is my current formula:

=IF(OR(Q:Q="Alton",Q:Q="Alamo",Q:Q="Pharr"),"McAllen","") =IF(OR(Q:Q="Alamogordo",Q:Q="Alpine"),"El Paso","")

I don't even know if this is possible but if it is it would save me a huge deal of time.

Thanks again!

Also, I hit another snag. I'm wondering is there a way to use multiple if statements in one cell? So please let me try and explain what I am trying to accomplish and then I will show you my current formula.

Like before I am trying to accomplish something very similar but a bit more complex.

Basically what I need Excel to do is to render the word "McAllen" in column "V" based off of weather or not there is a match in column "Q" of "Alton", "Alamo" or "Pharr". If column "Q" does not match "Alton", "Alamo" or "Pharr" then I need column "V" to remain blank.
-or-
If the words "Alpine" or "Alamogordo" are in the "Q" column then the "V" column needs to render "El Paso"; if neither "Alpine" or "Alamogordo" are in column "Q" then again column "V" would need to remain blank.

Here is my current formula:

=IF(OR(Q:Q="Alton",Q:Q="Alamo",Q:Q="Pharr"),"McAllen","") =IF(OR(Q:Q="Alamogordo",Q:Q="Alpine"),"El Paso","")

I don't even know if this is possible but if it is it would save me a huge deal of time.

Thanks again!

Yes. You can actually put 64 IF's in one cell.

Modify your formula to
=IF(OR(Q:Q="Alton",Q:Q="Alamo",Q:Q="Pharr"),"McAllen",IF(OR(Q:Q="Alamogordo",Q:Q="Alpine"),"El Paso",""))

Explanation of IF's:


=IF(logical_test,value_if_true,value_if_false)

logical_test: This is where you put the test. The rest can be any statement that results in a True or False result, for example A1>15. If A1 is in fact larger than 15, the result will be True. Common operators would be
.

• ">" Greater Than
• "<" Less Than
• "<>" NOT equal to
• ">=" Greater than or equal to
• "<=" Less than or equal to
• "=" Equal to

value_if_true: This is the result if logical_test returns a TRUE value.
value_if_false: As you probably guessed, this is the result if logical test returns a FALSE value.


A full example of the formula would be:

=IF(A1=10,A1+10,B1)

Now, if cell A1 is equal to 10, the result of the formula will be 20. Why? Because if A1=10, the value_if_true will occur, resulting in 10+10
If A1 is NOT equal to 10, the result of the formula will be whatever the value of B1's cell is

Remember, any one of the three inputs for the IF function can be another formula, for example, here are two IF's nested:
=IF(A1=10,A1+10,IF(A1>10,A1,B1-10))
The value_if_false is another IF function, that will only be executed by Excel if the previous IF statement (A1=10) is False

Originally Posted by Speshul

Modify your formula to

=IF(OR(Q:Q="Alton",Q:Q="Alamo",Q:Q="Pharr"),"McAllen",IF(OR(Q:Q="Alamogordo",Q:Q="Alpine"),"El Paso",""))

Are you sure that's what you want to do?

Since you're referencing the entire column the formula would have to be array entered.

Here's another way to write it when there are multiple criteria in an OR function:

=IF(OR(Q:Q={"Alton","Alamo","Pharr"}),"McAllen",IF(OR(Q:Q={"Alamogordo","Alpine"}),"El Paso",""))

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.