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.

 

[just_jon]

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?

If you're worried about losing the "shape" of the data, do consecutive Copy/Paste specials with Values and Format.

 

[Todd Bardoni]

You can hardcode your VLOOKUP.

Edit:-
Here's a sample. Add as many colors as you like:

=VLOOKUP($F2,{"Black",1;"Green",3;"Orange",5;"Blue",7;"Grey",8;"Red",2;"Yellow",4;"Magenta",15},2,0)

and, to get rid of the #N/A (and there are better ways than what I'm about to show you):-
=IF(ISNA(VLOOKUP($F2,{"Black",1;"Green",3;"Orange",5;"Blue",7;"Grey",8;"Red",2;"Yellow",4;"Magenta",15},2,0)),"EDIT",VLOOKUP($F2,{"Black",1;"Green",3;"Orange",5;"Blue",7;"Grey",8;"Red",2;"Yellow",4;"Magenta",15},2,0))

 

[splesac]

Tbardoni

thats interesting what does the ,2,0) mean at the end of that formula?

 

[Todd Bardoni]

The 2 tells it to return the value and/or text in the second column which is represented by a comma in the array (rows are represented by semicolons in the array).

The 0 tells it to look for an exact match.

 

[Zack Barresse]

splesac,

fyi, can replace 0/1 with TRUE/FALSE also.

 

[Todd Bardoni]

splesac,

fyi, can replace 0/1 with TRUE/FALSE also.

Yes, but that requires too many key strokes.

Edit: You forgot:- Or, you can leave it blank.

So, basically, if you were to visualize the above formula it would look something like:-

Book1
	D	E	F	G	H
1	Color	ReturnValue	LookupValue	Formula
2	Black	1	Grey	8
3	Green	3
4	Orange	5
5	Blue	7
6	Grey	8
7	Red	2
8	Yellow	4
9	Magenta	15
10
Sheet1

But, instead of creating a table, you can just hard code the table in to the formula itself as ssen in the above post.

 

[Damon Ostrander]

Hi splesac,

Here's a simple and compact alternative to nested if: a user-defined function that provides a Case capability. For example, with this Case function you can use the formula

=Cases($F2,"Edit","Black",1,"Green",3,"Orange",5,"Blue",7,"Grey",8,"Red",2,"Yellow",4,"Magenta",15 )

in place of the first formula you showed in your example, and it can be used up to the 255-character limit of the cell text.

Here's the code:

Function Cases(TestValue, ElseValue, ParamArray Selections() As Variant) As Variant

' This is a worksheet function utility that can serve as a Select Case on a worksheet.
' Example of usage: =Cases(A3,"Out of Order",3,"Red",2,"Amber",1,"Green")
' returns a string "Green" if cell A3 = 1, "Amber" if A3 = 2, "Red" if A3 = 3, and
' "Out of Order" for all other values of cell A3.

Dim nCases As Integer
Dim iCase As Integer

nCases = UBound(Selections) / 2
For iCase = 1 To UBound(Selections) Step 2
If TestValue = Selections(iCase - 1) Then
Cases = Selections(iCase)
Exit Function
End If
Next iCase
Cases = ElseValue

End Function



To install this code, simply go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane. It is then immediately available for use in your workbook.

Its too bad they didn't build a Case capability into Excel.

 

[Todd Bardoni]

Wow, Damon! That's awsome!

 

[Aladin Akyurek]

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?

Hard to follow. What do you mean by

"Is there a way to use VLookup on a cell by cell basis"?

 

[splesac]

Aladin Akyurek I guess I should give more background.

We have a logging program in the office that is based on Access 97. The program is tedious to use at times, so I found it easier to manipulate the program in Access. So what I've done is gone into Access 97 copied the tables that the program produces, pasted them into excel and basically created a template. I then created formulas in excel to make data entry easier. Once I'm done inputting the data in excel I then cut the excel data (the entire page) and paste it into access, then voila my logs are created.

Here is what I'm getting at, I can't add a column or rename one what so ever in excel ( I can only input data) otherwise when I paste back into Access it screws everything up. I also do not want to add more worksheets if possible. I was under the impression at the time I posted the message that Vlookup required a person to add worksheets or extra columns to get the desired result, meanwhile an If statement can be created in a cell and copied all the way down the column, without having to add to or disrupt my excel template.

I now realize that what I thought was not the case. Thanks for all your suggestions I'm very impresed with this place. I should have time thursday to tackle this one once more. If I'm still having problems I'll come back and update my post.