If Statement Issue - Excel

[kathleen0927]

Hello all!

I am having difficulty using multiple IF statements in 1 cell. There are 8 options the IF statement needs to check which are:
if J9 = BB, insert "1" in cell B9
if J9 = OS, insert "2" in cell B9
if J9 = OST, insert "3" in cell B9
if J9 = VP, "4"
if J9 = RR, "5"
if J9 = A3, "6"
if J9 = OD,"7"
if J9 = TN, "8"

This formula will be in all cells in column B of the Excel file for sorting. Any suggestions for how to write this? Thanks

 

[Joe4]

I would recommend creating a two columm lookup table instead, and using the VLOOKUP function.
https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Dave Patton]

Excel 2010
	B	C	D	E	F
1
2				BB	1
3				OS	2
4				OST	3
5				VP	4
6				RR	5
7				A3	6
8				OD	7
9	8			TN	8
10	8
11	8
2b

Cell Formulas
Range	Formula
B9	=VLOOKUP(J9,E2:F9,2,0)
B10	=VLOOKUP(J9,{"BB",1;"OS",2;"OST",3;"VP",4;"RR",5;"A3",6;"OD",7;"TN",8},2,0)
B11	=IF(J9="BB",1,IF(J9="OS",2,IF(J9="OST",3,IF(J9="VP",4,IF(J9="RR",5,IF(J9="A3",6,IF(J9="OD",7,IF(J9="TN",8,""))))))))

 

[Computerman]

Kathleen0927,
There is also a 9th option you need to consider and that is what if none of the eight options are inputted into J9?
Computerman

 

[kathleen0927]

There will always be one of these options in the cell. Thank you though for the assistance

 

[kathleen0927]

I am confused by cells B10 and B11. What do they do? For every row in column "B", the formula will need to insert a number 1 - 8

 

[kathleen0927]

Another user suggested the same thing. I am trying that. Thank you

 

[Joe4]

I am confused by cells B10 and B11. What do they do? For every row in column "B", the formula will need to insert a number 1 - 8

He is just showing you three different ways to do it. All three are different formulas, but will return the same thing.
The first one uses a lookup table (similar to my recommendation).
The other two are formulaic solutions that do not require a lookup table, as all posssible values are right there in the formulas.

One advantage to using a lookup table is that is you set it up correctly, you can easily make edits to the table values without having to update any formulas.

 

[kathleen0927]

oh duh! thanks for the clarification. I will try all.