Formula Length

[Mukesh Garg]

Hi all,

I tried the following formula:
=IF(L6=1,"ABC",IF(L6=2,"DEF",IF(L6=3,"GHI",IF(L6=4,"JKL",IF(L6=5,"MNO",IF(L6=6,"PQR",IF(L6=7,"STU",IF(L6=8,"VWX","XYZ"))))))))

As in this formula, i can test 9 situations only. but i want to set more
conditions on that cell only.

but excel is not accepting more values than 9. how can i do it?

please suggest.

Regards
Mukesh

 

[Biff]

Hi!

Create a table:

1..........abc
2..........def
3..........ghi

Then use a lookup formula:

=VLOOKUP(L6,table_range,2,0)

Or: (no table needed)

=CHOOSE(L6,"abc","def","ghi")

With the above formula you can have up to 29 values returned.

Biff

 

[bpeltzer]

Create a two-column table that has the 'input' value in the first column (ex
1, 2, 3) and the corresponding 'output' in the second (ex ABC, DEF, GHI).
Then use vlookup; something like =vlookup(L6,table_range,2,false).
--Bruce

 

[Rowan Drummond]

Try:

=CHOOSE(L6,"ABC","DEF","HIJ","KLM","NOP","QRS","UVW","XYZ","CHOICE9","etc")

hope this helps
Rowan

 

[Mukesh Garg]

Thank u all

Create a two-column table that has the 'input' value in the first column (ex
1, 2, 3) and the corresponding 'output' in the second (ex ABC, DEF, GHI).
Then use vlookup; something like =vlookup(L6,table_range,2,false).
--Bruce