Nest formula.

[serge]

I'm looking for a formula that can nest multiple numbers within.
For example if I type 200 in A1 it will return in B1:E1 ( 3,15,62,73 ) in separated cells, if I type 101 in A1 it would return in B1:C1 ( 1,5 ) etc…

Reference :
200 : 3,15,62,73
110 : 7,8,36,42,56
101 : 1,5

 

[ISY]

Hi

Excel 365

=IF(A1=200,{3\15\62\73},IF(A1=110,{7\8\36\42\56},IF(A1=41,{1\5},"")))

 

[serge]

Hi ISY, Thank you for the formula but it return a error message !!!

 

[ISY]

Hi


The formulas of the previous versions do not have this possibility

 

[serge]

You mean it can not be achieve ?

 

[serge]

Does someone have an idea how to make it work for 2007 version ?

 

[serge]

Would this formula work with excel 2010 ??

=IF(A1=200,{3\15\62\73},IF(A1=110,{7\8\36\42\56},IF(A1=41,{1\5},"")))

 

[serge]

Is there another formula that would give me the same result ?
Thank you.

 

[serge]

Hi everyone, I really need this formula, can someone help me with this, for Excel 2007, Please, Thank you.

 

[CA_Punit]

Book1
	M	N	O	P	Q	R	S	T	U	V	W	X	Y
20
21	200	3,15,62,73
22	110	7,8,36,42,56
23	101	1,5			101	1	5
24
25
26
27
Sheet1

Cell Formulas
Range	Formula
R23:V23	R23	=IFERROR(TRANSPOSE(MID(SUBSTITUTE(INDEX(N21:N23,MATCH(Q23,M21:M23,0)),",",REPT(" ",LEN(INDEX(N21:N23,MATCH(Q23,M21:M23,0))))),(ROW(A1:A5)-ROW(A1))*LEN(INDEX(N21:N23,MATCH(Q23,M21:M23,0)))+1,LEN(INDEX(N21:N23,MATCH(Q23,M21:M23,0)))))+0,"")
Dynamic array formulas.

Please use fill option in after pressing Ctrl+Shift+Enter