I would immediately stop using the IF statement and rephrase your question so potentially a VBA or alternative formula can be devised
=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),h pe!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!e 5),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!e1 0,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,b 37=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(a nd(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!f 15,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=h pe!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(an d(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,-------------," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
I would immediately stop using the IF statement and rephrase your question so potentially a VBA or alternative formula can be devised
Financial Modelling Consultant
Excel 2016 32bit and 2016 64bit + Windows 10
I've always listening so i'm always learning
Before posting your question, reread it to make sure you understand it.
If possible post example data to make it easier to help you
Going out on a limb and never tried it like this but try using name ranges.
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.
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)
Hi
Perhaps :-
Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.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))
hth
Last edited by ukmikeb; Dec 31st, 2013 at 07:53 AM.
Mike
-----------------------------------------------
Some solutions don't require an IF!
When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .
Paste your Excel data...
MrExcel HTML Maker or Excel Jeanie
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
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
Just make a table with all of the descriptions in one column and the relevant measure in the next, and use a lookup formula.
http://www.contextures.com/xlFunctions02.html
Like this thread? Share it with others