# Numbers return from a group.

#### serge

##### Well-known Member
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
JKLMNOPQ
5
615810
722369
8
Sheet

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Where do you keep the groups? Are they named ranges?

Within the formula if possible.

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

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

I can use that way also if you have the formula for it.

Thank you for the help.

Regards,
Serge.

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

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.

Sure! You're welcome!

Cheers

Replies
3
Views
186
Replies
2
Views
123
Replies
7
Views
252
Replies
3
Views
264
Replies
5
Views
207

1,196,360
Messages
6,014,809
Members
441,847
Latest member
hw407

### 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.

### Which adblocker are you using?

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

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