Formula Length

M

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
 
B

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
 
B

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
 
R

Rowan Drummond

Try:

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

hope this helps
Rowan
 
M

Mukesh Garg

Thank u all

bpeltzer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top