If/Or statement only returning false value

Question

Hello,

I am trying to use an if/or statement to return a value of 4 if true or 1 if false. Below is my equation. I have tried with and without quotes around the true and false values. Basically if the cell has E1, E2, LT1 or R4 in it then the true should be 4. If F3 or F7 is in the cell then the answer should be 1. The answer always comes out to 1 for me. I am not sure where I am going wrong.

Thank you.

=IF(OR(C22="E1",C22="E2",C22="LT1",C22="R4",C22="L5",C22="E8",C22="E6",C22="E7",C22="E9"),"4","1")

E1
E2
F3
F7
LT1
R4

Answer 1

Hi Jodi

An alternative and very effective solution when dealing with long nested IF statements formulas are using the VLOOKUP function.

1. Anywhere in your workbook create a table with all the lookup values and their respective results/values

(using named ranges is also advantageous)

2. Then use the formula =VLOOKUP(A1,$G$1:$H$12,2,FALSE)

**** Adapt the formula range to your real scenario

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi Jodi,

The formula looks good to me, and it works on my side:

In the list you provide, you have hidden space behind the value:

In this case, the formula won't work, because "E1" does not equal to "E1 ".

Meanwhile, if you are not sure which part of your formula is wrong, using the Evaluate Formula function will be helpful.

Evaluate a nested formula one step at a time

Regards,

Alex Chen

Answer 3

Thank you,

The spaces were my problem and now it works. Also thank you for letting me know about the Evaluate Formula function, that will come in handy.

I appreciate it,

Jodi

Answer 4

Thank you,

I did use VLOOKUP but the person I am doing the equations for doesn't understand it so I was trying to make the equation he does understand work.

I appreciate it,

Jodi

Answer 5

Just another idea:

=IF(OR(C22={"E1","E2","LT1","R4"}), 4, IF(OR(C22={"F3","F7"}), 1, "n/a"))

= = =

> ... to return a value of 4

Not sure what you want, but note that a value would be 4, and a string would be "4"