Maximum IF statements

splesac

New Member
Joined
Feb 10, 2004
Messages
10
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
Joined
Feb 18, 2002
Messages
3,339
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
Joined
Dec 2, 2003
Messages
3,196
The limit is 7 nested if statements, but like mudface said there's usually a better way then the if statements.
 

splesac

New Member
Joined
Feb 10, 2004
Messages
10
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
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

splesac

New Member
Joined
Feb 10, 2004
Messages
10
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

splesac

New Member
Joined
Feb 10, 2004
Messages
10
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
Joined
Feb 10, 2004
Messages
10
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.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top