Can it work both ways for vaules and texts?

Your posted example was in TEXT so I wrote the formula specifically for
TEXT.

To make it work for either text or numbers:

=IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MAT CH(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),"")

Change this portion:

MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0 )+1),0)

To:

MATCH(TRUE,B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16, 0)+1)<"",0)

This will make the formula an array. So, enter using the key combination of
CTRL,SHIFT,ENTER.

You still need an end of range flag in column B. Using this modified
formula, that flag can be either text or numeric.

Biff

"Fin Fang Foom" wrote in message
ups.com...
Ok I got it to work. Thank you very much!

The first try Iit did not work because I use it on another example
worksheet I have that instead of texts in column B they were numbers
but your formula you provided works well if column B are texts. Can it
work both ways for vaules and texts?


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),"")

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.