If without gaps

[haybob]

What i want is a formula which will look down the team of the month column and place that month name into the column of the team which was team of the month. i have done a simple if formula but would like it if there wasnt spaces between the months like in column E.

******** ******************** ************************************************************************>

Microsoft Excel - Ex to Alan M.XLS ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F8F9F10F11F12F13F14F15F16 =
	B	C	D	E	F	G
2	Sheet*A		*	Sheet*B		*
3	*	*	*	*	*	*
4	*	*	*	*	*	*
5	*	Team*of*	*	*	*	*
6	Date	the*Month	*	Arsenal*Records		*
7	*	*	*	*	*	*
8	Jan	Leeds	*	Apr	0	*
9	Feb	Liverpool	*	July	0	*
10	Mar	Leeds	*	Aug	0	*
11	Apr	Arsenal	*	Sept	Apr	*
12	May	Chelsea	*	*	0	*
13	Jun	Chelsea	*	*	0	*
14	Jul	Arsenal	*	*	Jul	*
15	Aug	Arsenal	*	*	Aug	*
16	Sept	Arsenal	*	*	Sept	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url]

[fairwinds]

Hi,

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D7D8D9D10D11D12D13D14D15 =
	A	B	C	D
1	Sheet*A	*	*	Sheet*B
2	*	*	*	*
3	*	*	*	*
4	*	Team*of*	*	*
5	Date	the*Month	*	Arsenal
6	*	*	*	*
7	Jan	Leeds	*	Apr
8	Feb	Liverpool	*	Jul
9	Mar	Leeds	*	Aug
10	Apr	Arsenal	*	Sept
11	May	Chelsea	*	*
12	Jun	Chelsea	*	*
13	Jul	Arsenal	*	*
14	Aug	Arsenal	*	*
15	Sept	Arsenal	*	*
Sheet4 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

=IF(COUNTIF($B$7:$B$15,$D$5) > ROW()-ROW($A$7),INDEX($A$1:$A$15,SMALL(IF(($B$7:$B$15=$D$5),ROW($A$7:$A$15),""),ROW()-ROW($A$7)+1)),"")

Is entered with Ctrl + Shift + Enter in D7 and dragged down.

[Aladin Akyurek]

Also by means of a faster working set of ordinary formulas...

******** ******************** ************************************************************************>

Microsoft Excel - Book5 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D5D7E7D8E8D9E9D10E10D11E11D12E12D13E13D14E14D15E15 =
	A	B	C	D	E
2	Sheet*A	*	*	Sheet*B	*
3	*	*	*	*	*
4	*	*	*	*	*
5	*	Team*of*	*	4	*
6	Date	the*Month	*	Arsenal	0
7	Jan	Leeds	*	Apr	*
8	Feb	Liverpool	*	Jul	*
9	Mar	Leeds	*	Aug	*
10	Apr	Arsenal	*	Sept	1
11	May	Chelsea	*	*	*
12	Jun	Chelsea	*	*	*
13	Jul	Arsenal	*	*	2
14	Aug	Arsenal	*	*	3
15	Sept	Arsenal	*	*	4
16	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

D5:

=LOOKUP(9.99999999999999E+307,E:E)

D7, which is copied down:

=IF(ROW()-ROW($D$7)+1<=$D$5,INDEX(A:A,MATCH(ROW()-ROW($D$7)+1,E:E,0)),"")

E6 must house a 0.

E7, which is copied down:

=IF((A7<>"")*(B7<>"")*(B7=$D$6),LOOKUP(9.99999999999999E+307,$E$6:E6)+1,"")