multiple "if"

[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

 

[Jarek Kujawa]

=IF(A1=1.2,"A",IF(A1=3.8,"B",IF(A1=15.31,"c")))

HIH

 

[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)

 

[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

 

[Nick C]

Hi Bob,

That is 3 or 8. Appologies for any confusion caused.

Regards,

Nick

 

[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

 

[Nick C]

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

Regards,

Nick

 

[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)

 

[Nick C]

Many thanks Bob. Works a treat.

Regards,

Nick

=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)

 

[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

 

[Lorne]

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

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.