IF(OR Formula help.

[serge]

I'm using this formula ex: IF(OR(D736={1}),23,IF(OR(D736={2}),15 etc... but the problem I have is that, this formula give you up to 64 arguments and stop after that, but I need 6 more a total of 70.

Is there a possibility to extend it to 70 arguments some how or which other formula can I use that would produce the same results that would accept 70 arguments ?

Thank you
Serge.

 

[Fluff]

You're OR functions are not needed, it could be written like
IF(D736=1,23,IF(D736=2,15 etc...

If you have 70 values, I would suggest creating a table with the values & then use vlookup or index/match.

 

[serge]

Thank you for your answer. So there is no formulas that can handle 70 levels of nesting ?

 

[Fluff]

You could use choose, that will allow upto 254 values.
That's assuming you are looking up the numbers 1 to 70, without any breaks.

 

[serge]

Ok how do you start the formula please.

 

[Dave Patton]

See Excel's help for information on Choose.

The information below shows different ways that you could build a Lookup.

T202006a.xlsm
	A	B	C	D	E
1	Lookup	15		1	23
2	Lookup with array	15		2	15
3	Lookup	15		3	200
4	Lookup with array	15		4	200
5	lookup with named arrays	15		5	200
2a

Cell Formulas
Range	Formula
B1	B1	=LOOKUP(D736,D1:E70)
B2	B2	=LOOKUP(D736,{1,23;2,15;3,200;4,200;5,200;6,200;7,200;8,200;9,200;10,200;11,200;12,200;13,200;14,200;15,200;16,200;17,200;18,200;19,200;20,200;21,200;22,200;23,200;24,200;25,200;26,200;27,200;28,200;29,200;30,200;31,200;32,200;33,200;34,200;35,200;36,200;37,200;38,200;39,200;40,200;41,200;42,200;43,200;44,200;45,200;46,200;47,200;48,200;49,200;50,200;51,200;52,200;53,200;54,200;55,200;56,200;57,200;58,200;59,200;60,200;61,200;62,200;63,200;64,200;65,200;66,200;67,200;68,200;69,200;70,200})
B3	B3	=LOOKUP(D736,D1:D70,E1:E70)
B4	B4	=LOOKUP(D736,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70},{23;15;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200;200})
B5	B5	=LOOKUP(D736,array1,array2)

 

[serge]

Thank you so much guys for both your help the formulas works Great.
Serge.