nested IF

[jeff2000]

I have three columns and a single row
A1 can have values 1,2,3,4
B1 can have values A,B,C,D
C1 =IF(B1=A,"X1",IF(B1=B,"X2",IF(B1=C,"X3",IF(B1=D,"X4"))))


When A1 is a value of 1 or 2 then this formula applies.

What I want to include in the above formula - if B1 is equal to A or B and A1 is equal to 3 or 4 then I want a blank.

1 A X1

I tried nesting the AND formula without success
Thanks for your help

 

[Peter_SSs]

jeff2000

I am not sure that I have got what you want in my mind, but try this in C1:
=IF(A1<3,LOOKUP(B1,{"A","B","C","D"},{"X1","X2","X3","X4"}),"")

You may want a further condition to check that A1 and B1 have data in them. Anyway, try the above for starters.

 

[Scott Huish]

Perhaps:

=IF((A1<3)*(A1<>"")*(B1<>""),"X" & CODE(UPPER(B1))-64,"")

 

[lenze]

=IF(A1>2,"",IF(B1=A,"X1",IF(B1=B,"X2",IF(B1=C,"X3",IF(B1=D,"X4")))))

Peter's VLOOKUP approach, however, is probably more efficient

lenze

 

[Scott Huish]

MATCH would also work:

=IF(A1<3,"X" & MATCH(B1,{"A","B","C","D"},0),"")