Ashish,

I received your work-around file and it really helps. Thanks so much for
your time!

CW

"Ashish Mathur" wrote:

I,

Can you mail me the file at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
Thanks,

Unfortunately that is what I did and the result was a column of blank
cells.
For a simple example, assume:

Range B4:B14 = department #s
Range C4:C14 = employee names

In columns F - H in row 2, I want to be able to input the 3 different dept
#s. In rows 3 and higher, I want to enter the array to list out the
employees within each dept. When I enter the following formula, I get
blank
cells and I'm not sure why:

=IF(ISERROR(INDEX($B$4:$C$14,SMALL(IF($B$4:$B$14=F $2,ROW($B$4:$B$14)),ROW(1:1)),2)),"",INDEX($B$4:$C $14,SMALL(IF($B$4:$B$14=F$2,ROW($B$4:$B$14)),ROW(1 :1)),2))

Any idea?

Thanks again for your time.



"Ashish Mathur" wrote:

Hi,

To make it work, you have to array enter the formula (Ctrl+Shift+Enter)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com