Numbers return from a group.

[serge]

I have 3 different groups that each one contain numbers.
Group 1 = 5,8,10.
Group 2 = 2,3,6,9.
Group 3 = 1,4,7.

If I put 1 in K6 what formula need to be in M6,N6,O6 to return the group number which are 5,8,10 ?
or if I put 2 in K7 and return 2,3,6,9 in M7,N7,O7,P7

Thank you.

Excel Workbook
	J	K	L	M	N	O	P	Q
5
6		1		5	8	10
7		2		2	3	6	9
8
Sheet

 

[konew1]

Where do you keep the groups? Are they named ranges?

 

[serge]

Within the formula if possible.

 

[XOR LX]

A very strange request!

=INDEX({5,8,10,"";2,3,6,9;1,4,7,""},$K6,COLUMNS($A:A))

in M2, and copied across (and down, if desired), would be one way, though it's static and not readily adaptable to other group values.

However, since you requested that the values be placed as such within the actual formula, I can only comply with your wishes!

Regards

 

[serge]

ADVERTISEMENT

Hi XOR LX,

Thank you for the formula I really appreciate it.

I wanted the group numbers in the formula because it would be easier, I think ?

You, you wanted to have them in cells for example :

Group 1 : 5,8,10 in M1,N1,O1
Group 2 : 2,3,6,9 in P1,Q1,R1,S1
Group 3 : 1,4,7 in T1,U1,V1

Was that your idea ?
I can use that way also if you have the formula for it.

Thank you for the help.

Regards,
Serge.

 

[XOR LX]

Actually I'd arrange them a bit more helpfully, i.e.

5, 8, 10 in M1:O1
2, 3, 6, 9 in M2:P2
1, 4, 7 in M3:O3

which makes the formula in M6:

=INDEX($M$1:$P$3,$K6,COLUMNS($A:A))

though this will return a 0 in cells to the right beyond the expected number of returns. These could be accounted for by either:

=IF(COLUMNS($A:A)>COUNT(INDEX($M$1:$P$3,$K6,)),"",INDEX($M$1:$P$3,$K6,COLUMNS($A:A)))

or:

=IFERROR(1/(1/INDEX($M$1:$P$3,$K6,COLUMNS($A:A))),"")

though the latter can sometimes suffer from rounding errors due to the divisions, depending on the values in question.

Regards

 

[serge]

XOR LX,

Thank you so much for the formulas they work like a charm.

Actually I like it better this way , Thanks again.

Regards,
Serge.

 

[XOR LX]

Sure! You're welcome!

Cheers