array needed

[tbablue]

2 columns are populated in Sheet 1 - column E contains a list of numbers & column M a list of names.

Sheet 2 $A$1 is a validated drop-down list of names drawn from column M Sheet 1.

I need an array that populates column C of Sheet 2 with all numbers that correspond to the value in $A$1

Can anyone help me with this please? It's driving me crazy.

 

[Domenic]

On Sheet2, try...

B2:

=COUNTIF(Sheet1!M2:M10,A1)

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX(Sheet1!$E$2:$E$10,SMALL(IF(Sheet1!$M$2:$M$10=$A$1,ROW(Sheet1!$M$2:$M$10)-ROW(Sheet1!$M$2)+1),ROWS(C$2:C2))),"")

If you want the numbers sorted and returned in ascending order, try the following instead...

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,SMALL(IF(Sheet1!$M$2:$M$10=$A$1,Sheet1!$E$2:$E$10),ROWS(C$2:C2)),"")

Adjust the ranges, accordingly.

Hope this helps!

xl-central.com

 

[tbablue]

Thank you Domenic. Seems to do just what's required.

Appreciated.

 

[Domenic]

Thank you Domenic. Seems to do just what's required.

Appreciated.

You're very welcome!

xl-central.com[