# Maximum IF statements

#### splesac

##### New Member
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.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Mudface

##### MrExcel MVP
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

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

#### splesac

##### New Member
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

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

#### splesac

##### New Member
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

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

#### splesac

##### New Member
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.

#### splesac

##### New Member
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.

Replies
2
Views
54
Replies
5
Views
103
Replies
5
Views
133
Replies
1
Views
258
Replies
3
Views
112

1,186,168
Messages
5,956,337
Members
438,247
Latest member
UZev

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back