multiple "if"

N

Nick C

Hello,

I have a various list of numbers. I want to do one of those fancy if
formulas to say if 1, 2 then "A", if 3, 8 then "B", if 15, 31 then "c"

Thanks in advance.

Nick
 
B

Bob Phillips

Is that 3 or 8, or 3 through to 8?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
N

Nick C

Jarek,

Not sure I explained myself properly. Trying to get it to put an "A" if 1 or
2 appears, "B" if 3 or 8 appears etc.

Regards,

Nick
 
P

Pete_UK

Set up a table of your numbers and letters somewhere (eg from X1
onwards), like this:

1 A
2 A
3 B
8 B
15 C
31 C

Then you can use:

=VLOOKUP(A1,X:Y,2,0)

Hope this helps.

Pete
 
N

Nick C

Thanks Pete. Would still like to know how to do it using an IF formula, if it
is possible.

Regards,

Nick
 
B

Bob Phillips

=IF(OR(A1=1,A1=2),"A",IF(OR(A1=3,A1=8),"B",IF(OR(A1=15,A1=31),"C","")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
N

Nick C

Many thanks Bob. Works a treat.

Regards,

Nick

Bob Phillips said:
=IF(OR(A1=1,A1=2),"A",IF(OR(A1=3,A1=8),"B",IF(OR(A1=15,A1=31),"C","")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
P

Pete_UK

If you are using XL2003 or earlier, you are limited to 7 nested
functions in a formula. In the example you gave you will be within
this limit, but if you have more conditions you will quickly exceed
it, whereas the VLOOKUP approach does not suffer from this limitation.

Pete
 
L

Lorne

Nick C said:
Hello,

I have a various list of numbers. I want to do one of those fancy if
formulas to say if 1, 2 then "A", if 3, 8 then "B", if 15, 31 then "c"

Thanks in advance.

Nick
Click to expand...

Have a look at the VLookup formula - it is easier than nested if statements
if you have more than a couple of results.

In part of the spreadsheet not used create you list:
1 A
2 A
3 B
8 B
15 c
31 c
etc.

then use vlookup to find a number in column 1 and it will display the text
in column 2.
 
Top