Maximum IF statements

[splesac]

I'm sorry if this question sounds very novice but I am new to excel. I'm creating if statements which are working correctly, only problem is that excel appears to only allow me to place 8 if statements maximum per cell. Can I increase this somehow or is 8 the limit per cell. Thanks in advance.

 

[Mudface]

That's the limit, but there's often a way round it (eg using VLOOKUP etc). Could you post some data, preferably using Colo's HTML maker and more details of what you want to do.

 

[Cbrine]

The limit is 7 nested if statements, but like mudface said there's usually a better way then the if statements.

 

[splesac]

Thank You for the prompt responses. Unfortunately Mudface I am not familiar with Colo's HTML. But I will provide this data:

What I'm basically doing is imputting data in excel then pasting it into a different loging program. So in my spreadsheet if I type in Black in cell F2, it knows that the cell N2 should have the number 1 (signifying Library colour and symbol 1)

Cell N2:
=IF($F2="Black",1,IF($F2="Green",3,IF($F2="Orange",5,IF($F2="Blue",7,IF($F2="Grey",8,IF($F2="Red",2,IF($F2="Yellow",4,IF($F2="Magenta",15,"EDIT"))))))))

Then in cell O2 it knows black can be found in Log Library.

Cell O2:
=IF($F2="Black","Log Library",IF($F2="Green","Log Library",IF($F2="Orange","Log Library",IF($F2="Blue","Log Library",IF($F2="Grey","Log Library",IF($F2="Red","Log Library",IF($F2="Yellow","Log Library",IF($F2="Magenta","Log Library","""EDIT"))))))))

What I'd like to do is add several more colours and/or symbols to the statements but as you have told me there is a limit. Maybe what I should do is read up on VLOOKUP I wasn't aware of this function.

 

[just_jon]

Yup; after reading up and trying, post back if you need help, because a VLOOKUP table's exactly what you need.

 

[splesac]

I've had success using the CONCATENATION method:

=IF($F2="Black",1,"")&IF($F2="Green",3,"")

This way my IF statements are unlimited, which is great but now I get up to 27 or so IF statements then excel tells me the formula is too long. Is there any way to increase the number of characters in a cell?

I would have used something like VLOOKUP but the problem is that I'd like to keep the formula in each cell because I'm cutting and pasting this info into another program and the info (cell titles, headings, and data) has to be exact. I have many worksheets in this file and other co-workers in the field will use this file so adding another worksheet may cause confusion. Is there a way to use VLookup on a cell by cell basis?

 

[Zack Barresse]

when you paste, can you paste special -> values, or do you need to keep the formulas?

 

[splesac]

Firefytr

When I paste into the program (access) no I do not need the excel formula. The formulas are there to create less typing in excel.

 

[Iridium]

FYI: J-Walk: Alternatives to Nested IF statements to get around the limit of 7 statements

HTH

 

[splesac]

Iridium

Thanks for the link I actually found that website from a past post on this board when I did a search, thats where I got the idea to use the CONCATENATION method.