Months in numbers iso text

M

mucah!t

Well-known Member
Joined
Jun 27, 2009
Messages
593
Hi all,

The formulas below get the month from column C [JAN, FEB etc]
However, as this gives problems when opened with other time-settings, I'd like it rather to reference to column B where moths are displayed in numbers [1,2 etc] any ideas

Excel Workbook
BCDEFGHIJ
1820111234567
191JANVTTTT
202FEBLDLALALA
213MARC2ADDDA
224APRVVLDLDLD
235MAYLALALA4
246JUNLALALAVV
257JULLALDLDLDLD
CALENDAR
Excel 2003
Cell Formulas
RangeFormula
F18=E18+1
F19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
F25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),F$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G18=F18+1
G19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
G25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),G$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H18=G18+1
H19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
H25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),H$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I18=H18+1
I19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
I25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),I$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J18=I18+1
J19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
J25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),J$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
D25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E19=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C19)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E20=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C20)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E21=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C21)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E22=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C22)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E23=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C23)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E24=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C24)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
E25=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,MONTH(DATEVALUE("1-"&$C25)),E$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi

If you have the Analysis Toolpak installed in 2003.
Then for column C, starting in C19, enter as a date 01/01/2011 and format as "mmm".

Then your MATCH for the dates in Cell D19 could be simplified to something like (not tested) -

Code: 
MATCH(EOMONTH($C$19,-1)+COLUMNS($D$19:$D19)[COLOR=#800080],WERKBLAD!$B$6:$B$5001,0[/COLOR][COLOR=#008000])[/COLOR]

hth
 
Last edited:
Upvote 0
Hello ukmikeb
I don't have the toolpak installed and rather don't like to.
The sheet I am working on is used by a lot of people on different computers.

Are there other solutions?
 
Upvote 0
Try the following:

for cell D19

=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,$B19,D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,$B19,D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))

Basically instead of MONTH(DATEVALUE("1-"&$C19)) just refer to $B19
 
Upvote 0
Hi

Without the Analysis Toolpak, then with the dates in Column C as specified in my earlier post, try -

Code: 
MATCH($C19+COLUMNS($D$19:$D19)-1,WERKBLAD!$B$6:$B$5001,0)

or to make it more transparent as you copy down and across -
Code: 
MATCH($C19+COLUMN($1:1)-1,WERKBLAD!$B$6:$B$5001,0)

hth
 
Upvote 0
pushkardey said:
Try the following:

for cell D19

=IF(ISERROR(INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,$B19,D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0))),"",INDEX(WERKBLAD!$D$6:$IV$5001,MATCH(DATE($C$18,$B19,D$18),WERKBLAD!$B$6:$B$5001,0),MATCH($C$2,WERKBLAD!$D$5:$IV$5,0)))

Basically instead of MONTH(DATEVALUE("1-"&$C19)) just refer to $B19
Click to expand...

Perfect!
Thanks Pushkardey!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Index with Multiple Conditions
Replies
2
Views
109
kivikatz
K
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
415
Alex Blakenburg
A
D
  • Solved
List of items within a specific month
Replies
4
Views
233
Fluff
Fluff
Sean15
  • Solved
Rewrite formula so 2nd, 3rd…occurrence of lookup value return blank cell
Replies
6
Views
253
Peter_SSs
Peter_SSs
C
  • Solved
index match excel formula to vba code
Replies
9
Views
357
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,594
Messages
6,179,792
Members
452,942
Latest member
VijayNewtoExcel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top