# How to Use More Than 7 IF Functions?

#### jerryj

##### New Member
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
try using vlookup.
this allows you to have as many options as you require

Within a cell?

Breakup your formula to span more than one cell. At the top of your IF statement tree you have =if(A,y,z). Put Y-tree in one cell. Put Z-tree in another cell.

g-

On 2002-08-30 00:41, jerryj wrote:
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

Welcome to the board Jerry,

Can you post a copy of your formula using IF's.
I think, as mentioned, you may be able to use VLOOKUP or LOOKUP.
If you post the formula we'll have a look.

Also, as mentioned, you can run across a number of cells. This would only be a workaround, look at, "limits in Microsoft Excel" in the help files.

Here it is!!
=IF(OR(V1<=V40,V1<V41),"The Eagle Has Landed",IF(AND(V1>V40,V1<=V41),"Philly Pride II",IF(AND(V1>V41,V1<=V42),"Egoholics",IF(AND(V1>V42,V1<=V43),"Hail Marys",IF(AND(V1>V43,V1<=V44),"Rice A Roni",IF(AND(V1>V44,V1<=V45),"Dallas Devestators",IF(AND(V1>V45,V1<=V46),"Packers2003",IF(AND(V1>V46,V1<=V47),"Clutch Ballers",IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes",IF(AND(V1>V48,V1<=V49),"Womping Wombats",IF(AND(V1>V49,V1<=V50),"Under Dogs",)))))))))))

OK,

As i can tell you have various values in column V as as a target between numbers.

VLOOKUP should do the trick:
Book1
ABCDE
1TargetNameValueReturn
20Ian042Ian1
340Ian1169Ian13
450Ian223Ian0
560Ian3157Ian12
670Ian478Ian4
780Ian5103Ian7
890Ian672Ian4
9100Ian7116Ian8
10110Ian8181Ian15
11120Ian9231Ian20
12130Ian1094Ian6
13140Ian11136Ian10
14150Ian12150Ian12
15160Ian13107Ian7
16170Ian14157Ian12
17180Ian15210Ian18
18190Ian16218Ian18
19200Ian1795Ian6
20210Ian18
21220Ian19
22230Ian20
23240Ian21
Sheet1

using the 1 at the end of the formula will give an approximate match (round down) to the one your looking for.

Is this what you need?

Just adding a "work-around" that i've used that i believe is simpler than the above.

Split your IF statements between 2 cells, in your example you have 9 IF statements. In the first column use the first 7, but have the ultimate FALSE print an empty cell (ie- " ").

Then have a second column check the criteria on the other 2 IF statements, with the ultimate FALSE printing an empty cell.

Then in a 3rd column do an IF statement that displays the value of the cell that isn't empty (ie- =If(a1=" ", b1, a1) ).
This message was edited by thereuare on 2002-08-31 06:42

On 2002-08-30 09:14, jerryj wrote:
Here it is!!
=IF(OR(V1<=V40,V1<V41),"The Eagle Has Landed",IF(AND(V1>V40,V1<=V41),"Philly Pride II",IF(AND(V1>V41,V1<=V42),"Egoholics",IF(AND(V1>V42,V1<=V43),"Hail Marys",IF(AND(V1>V43,V1<=V44),"Rice A Roni",IF(AND(V1>V44,V1<=V45),"Dallas Devestators",IF(AND(V1>V45,V1<=V46),"Packers2003",IF(AND(V1>V46,V1<=V47),"Clutch Ballers",IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes",IF(AND(V1>V48,V1<=V49),"Womping Wombats",IF(AND(V1>V49,V1<=V50),"Under Dogs",)))))))))))

What are the values that you have in V40:V50?

On 2002-08-31 06:41, thereuare wrote:
Just adding a "work-around" that i've used that i believe is simpler than the above.

Split your IF statements between 2 cells, in your example you have 9 IF statements. In the first column use the first 7, but have the ultimate FALSE print an empty cell (ie- " ").

Then have a second column check the criteria on the other 2 IF statements, with the ultimate FALSE printing an empty cell.

Then in a 3rd column do an IF statement that displays the value of the cell that isn't empty (ie- =If(a1=" ", b1, a1) ).
This message was edited by thereuare on 2002-08-31 06:42

A work-around? Would be a lot more costly on system resource when it calculates.

My reply, which may not answer the question (until we have further information),
Let the user keep a track of all the different values between.
Also, the way the problem is described, it sound like such a table already exsists, why would run a formula over 2, 3 or 4 cells when VLOOKUP is design for just such a table?

Regards

Replies
1
Views
348
Replies
1
Views
246
Replies
0
Views
145
Replies
3
Views
83
Replies
5
Views
231

1,218,826
Messages
6,144,693
Members
450,565
Latest member
ChasVenie12

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