Lookup multiple results with gaps
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.
|