Biff wrote:
OK, I see what you want.

Try this: (based on your sample data in the range A3:B16):

=IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"")


I'm getting a #N/A error. I'm putting the exact formula you gave
me.



Biff

"Fin Fang Foom" wrote in message
ups.com...
Hi,
My Data is Set-Up in this format.
A B D E
Hours Dept.Names Criteria Expected Results
8.34 A B 8.34
8.34 7.21
7.64 3.21
7.21
7.21
3.21
6.64
3.21
6.62
8.34 B
7.21
3.21
9.21 C
4.15
8.34
10.11
Column A are the hours.
Column B are the Dept. names
Cell D2 has the criteria
I would like a formula that returns multiple results for Dept. B in
cell E2 and filler down.
I could use this formula:
=INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2)))

But if you notice that in column B has gaps between them anyway we
could go around that without any helper columns or helper cells? A all
in one formula.