# nested IF

#### jeff2000

##### New Member
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
Perhaps:

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

#### lenze

##### Legend
=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

##### MrExcel MVP
MATCH would also work:

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

Replies
6
Views
166
Replies
6
Views
122
Replies
6
Views
133
Replies
4
Views
184
Replies
2
Views
105

1,195,848
Messages
6,011,948
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back