IF Statement

[xcelnovice]

Hi I am trying to write an IF statement
So far I have IF(OR(A1=“A”,A1=“C”),”1”,”2”)
I’d like to add 2 more statements like this
So if A1=B or D return values 3 & 4 and If A1=E or F return 5 & 6

 

[Gerald Higgins]

Hi.

What exactly do you mean by this

So if A1=B or D return values 3 & 4

?

Do you mean, if A1=B, return 3, and if A1 = D, return 4 ?
Or do you mean, If A1 = B or D, return the text string "3 & 4" ?

 

[xcelnovice]

Sorry the first one. If A1=B return 3 & if A1=D return 4. And I’d like it to be all one formula so no matter if A1= A,B,C,D,E,F it returns one of the numbers. Hope this is clear. Sorry can’t post a file from my phone Thanks

 

[63falcondude]

Maybe something along the lines of this:

=LOOKUP(A1,{"A","B","C","D","E","F"},{1,2,3,4,5,6})

 

[Gerald Higgins]

OK, I think your best bet would probably be a look up table.

Set up a range somewhere like this, across 2 columns, let's assume you do it in the range D1:E6
A........1
B........3
C........2
D........4
E........5
F........6

Then, instead of your existing formula, use something like

=vlookup(A1,D1:E6,2,false)

It is possible to do this other ways - for example an IF statement, but this will rapidly get complicated, or you can also embed the lookup table within a formula, *** Edit to add - like 63falcondude did *** but my personal preference is not to do that.