Displaying Multiple Values without using Macros or ROWS function

Hello Gurus out there,

I am working with a software itself doesn't support Macros and ROWS function.

My Goal:
is to display a set of data based on filtered information.

My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri

Based on user selection of Company and Department, I want to be able to display the relavent names.

If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri


I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros, I am stuck. Can anyone provide any alternatives to this? Your help is highly appreciated.

Sample Formula
IF($B$2="Company",IF(ROWS(C$15:C15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF('Department!$B$3:$B$691=$B$11,ROW(Department!$F$3:$F$691)-ROW('WW DELMIA HC ROSTER 2009'!$F$3)+1),ROWS(C$15:C15))),""),IF(ROWS(C$15:C15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF('Department!$A$3:$A$691=$B$11,ROW('Department!$F$3:$F$691)-ROW('Department!$F$3)+1),ROWS(C$15:C15))),""))

Since it seems you can support ROW(), then replace ROWS(C$15:C15) with ROW(C15)-ROW($C$15)+1

e.g.

Please Login or Register  to view this content.

Well, without the rows function, you need a helper column in your data watching your filter criteria and a prefilled out set of numbers to match against.

This sheet will fill out names and stop when the Key values are maxxed.

Functions:
CONCATENATE
MAX
INDEX/MATCH

One way could be to create a helper column in the sheet say in column AA.

Enter the number 1 in AA1 and fill down to 691 in AA691

Then use formula:

Please Login or Register  to view this content.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]