Thanks:  0
Likes:  0

# Thread: More than 7 \

1. 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 ]

2. 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.

3. 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

4. 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 ]

5. 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.

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•