# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 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
5
Views
76
Replies
5
Views
345
Replies
1
Views
155
Replies
3
Views
240
Replies
2
Views
191

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,564
Messages
5,765,129
Members
425,262
Latest member
sabry

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