how i can use more than 64 levels of nesting

nskatoch

Board Regular
Joined
Dec 30, 2013
Messages
53
Office Version
  1. 365
Platform
  1. Windows
=if(and(p1=1,b37=hpe!c5),hpe!c6,if(and(p1=1,b37=hpe!d5),hpe!d6,if(and(p1=1,b37=hpe!e5),cosa!e6,if(and(p1=1,b37=hpe!f5),hpe!f6,if(and(p1=1,b37=hpe!g5),hpe!g6,if(and(p1=2,b37=hpe!c5),hpe!c7,if(and(p1=2,b37=hpe!d5),hpe!d7,if(and(p1=2,b37=hpe!e5),cosa!e7,if(and(p1=2,b37=hpe!f5),hpe!f7,if(and(p1=2,b37=hpe!g5),hpe!g7,if(and(p1=3,b37=hpe!c5),hpe!c8,if(and(p1=3,b37=hpe!d5),hpe!d8,if(and(p1=3,b37=hpe!e5),cosa!e8,if(and(p1=3,b37=hpe!f5),hpe!f8,if(and(p1=3,b37=hpe!g5),hpe!g8,if(and(p1=4,b37=hpe!c5),hpe!c9,if(and(p1=4,b37=hpe!d5),hpe!d9,if(and(p1=4,b37=hpe!e5),cosa!e9,if(and(p1=4,b37=hpe!f5),hpe!f9,if(and(p1=4,b37=hpe!g5),hpe!g9,if(and(p1=5,b37=hpe!c5),hpe!c10,if(and(p1=5,b37=hpe!d5),hpe!d10,if(and(p1=5,b37=hpe!e5),cosa!e10,if(and(p1=5,b37=hpe!f5),hpe!f10,if(and(p1=5,b37=hpe!g5),hpe!g10,if(and(p1=6,b37=hpe!c5),hpe!c11,if(and(p1=6,b37=hpe!d5),hpe!d11,if(and(p1=6,b37=hpe!e5),cosa!e11,if(and(p1=6,b37=hpe!f5),hpe!f11,if(and(p1=6,b37=hpe!g5),hpe!g11,if(and(p1=7,b37=hpe!c5),hpe!c12,if(and(p1=7,b37=hpe!d5),hpe!d12,if(and(p1=7,b37=hpe!e5),cosa!e12,if(and(p1=7,b37=hpe!f5),hpe!f12,if(and(p1=7,b37=hpe!g5),hpe!g12,if(and(p1=8,b37=hpe!c5),hpe!c13,if(and(p1=8,b37=hpe!d5),hpe!d13,if(and(p1=8,b37=hpe!e5),cosa!e13,if(and(p1=8,b37=hpe!f5),hpe!f13,if(and(p1=8,b37=hpe!g5),hpe!g13,if(and(p1=9,b37=hpe!c5),hpe!c14,if(and(p1=9,b37=hpe!d5),hpe!d14,if(and(p1=9,b37=hpe!e5),cosa!e14,if(and(p1=9,b37=hpe!f5),hpe!f14,if(and(p1=9,b37=hpe!g5),hpe!g14,if(and(p1=10,b37=hpe!c5),hpe!c15,if(and(p1=10,b37=hpe!d5),hpe!d15,if(and(p1=10,b37=hpe!e5),cosa!e15,if(and(p1=10,b37=hpe!f5),hpe!f15,if(and(p1=10,b37=hpe!g5),hpe!g15,if(and(p1=11,b37=hpe!c5),hpe!c16,if(and(p1=11,b37=hpe!d5),hpe!d16,if(and(p1=11,b37=hpe!e5),cosa!e16,if(and(p1=11,b37=hpe!f5),hpe!f16,if(and(p1=11,b37=hpe!g5),hpe!g16,if(and(p1=12,b37=hpe!c5),hpe!c17,if(and(p1=12,b37=hpe!d5),hpe!d17,if(and(p1=12,b37=hpe!e5),cosa!e17,if(and(p1=12,b37=hpe!f5),hpe!f17,if(and(p1=12,b37=hpe!g5),hpe!g17,if(and(p1=13,b37=hpe!c5),hpe!c18,if(and(p1=13,b37=hpe!d5),hpe!d18,if(and(p1=13,b37=hpe!e5),cosa!e18,if(and(p1=13,b37=hpe!f5),hpe!f18,-------------," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I would immediately stop using the IF statement and rephrase your question so potentially a VBA or alternative formula can be devised
 
Upvote 0
Going out on a limb and never tried it like this but try using name ranges.
 
Upvote 0
This looks extremely inefficient. Looking at the formula you have entered so far:

you are looking to see if B37 appears in HPE!C5:G5
When P1 = 1 you want to return 1 row below, P2 = 2 rows below, P3=3 rows below etc

In which case you can use:

=INDEX(HPE!C:G,P1+1,MATCH(B37,HPE!C$5:G$5))


If that doesn't work a you want, as the above poster says, please rephrase your question: tell us the layout of your data and what you want to achieve.


 
Upvote 0
Revised formula:


=INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))

Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)
 
Upvote 0
Hi

Perhaps :-
Code:
=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth
 
Last edited:
Upvote 0
Revised formula:=INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)
Thank you very much sir. Your revised formula runs successfully
 
Upvote 0
Hi

Perhaps :-
Code:
=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth

Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
Sorry in advance for my inexperience
 
Upvote 0
Hi

Perhaps :-
Code:
=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth

Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
Sorry in advance for my inexperience
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top