More than 7 \

[Phil J]

Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.

The formula containing the 7 "IFs" is listed below:
=IF(N200="lam/cont",VLOOKUP(N201,pricelist,2,TRUE),IF(N200="vinyl 1",VLOOKUP(N201,pricelist,3,TRUE),IF(N200="vinyl 2",VLOOKUP(N201,pricelist,4,TRUE),IF(N201="vinyl 3",VLOOKUP(N201,pricelist,5,TRUE),IF(N200="vinyl 4",VLOOKUP(N201,pricelist,6,TRUE),IF(N200="tas oak",VLOOKUP(N201,pricelist,7,TRUE),IF(N200="blackwood",VLOOKUP(N201,pricelist,8,TRUE),"")))))))

_________________
Phil

Phil.Jackel@dhs.vic.gov.au

[ This Message was edited by: phil j on 2002-04-22 19:53 ]

[John McGraw]

On 2002-04-22 18:53, Phil J wrote:
Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.

Create your own function in VBA.

[Jay Petrulis]

Hi Phil,

Many ways around this limit. Post your request in more detail and the appropriate solution will be found.

If you'd like to take a crack at it before posting again, the main options are:

1. IFs separated within the formula
2. Boolean algebra
3. Lookup tables (VLOOKUP,HLOOKUP,LOOKUP,INDEX/MATCH)
4. Rewriting the formula

Bye,
Jay

[Colo]

Pls Try this.

=VLOOKUP(N201,pricelist,MATCH(N200,{"lam/cont","vinyl 1","vinyl 2","vinyl 3","vinyl 4","tas oak","blackwood"},0)+1,TRUE)

[ This Message was edited by: Colo on 2002-04-22 21:17 ]

[Aladin Akyurek]

On 2002-04-22 18:53, Phil J wrote:
Excel limits the number of "IFs" that can be group in one formula to seven. Is there any way around this limitation. I would like to be able to have 8 "IFs" in one formula.

The formula containing the 7 "IFs" is listed below:
=IF(N200="lam/cont",VLOOKUP(N201,pricelist,2,TRUE),IF(N200="vinyl 1",VLOOKUP(N201,pricelist,3,TRUE),IF(N200="vinyl 2",VLOOKUP(N201,pricelist,4,TRUE),IF(N201="vinyl 3",VLOOKUP(N201,pricelist,5,TRUE),IF(N200="vinyl 4",VLOOKUP(N201,pricelist,6,TRUE),IF(N200="tas oak",VLOOKUP(N201,pricelist,7,TRUE),IF(N200="blackwood",VLOOKUP(N201,pricelist,8,TRUE),"")))))))

_________________
Phil

Phil.Jackel@dhs.vic.gov.au

[ This Message was edited by: phil j on 2002-04-22 19:53 ]

Make a 2-column table, consisting of

{"lam/cont",2;
"vinyl 1",3;
"vinyl 2",4;
"vinyl 3",5;
"vinyl 4",6;
"tas oak",7;
"blackwood",8}

and name it ColList via the name Box.

Change your VLOOKUP formula to:

=VLOOKUP(N201,pricelist,VLOOKUP(N200,ColList,2,0))

Alternatively, make a 1-column list:

{"lam/cont";
"vinyl 1";
"vinyl 2";
"vinyl 3";
"vinyl 4";
"tas oak";
"blackwood"}

and name it Categories.

Change the VLOOKUP formula to:

=VLOOKUP(N201,pricelist,MATCH(N200,Categories,0)+1)

You can expand ColList or Categories to cover more as needed.

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-04-22 21:39 ]