INDEX/MATCH with nested IF/AND troubles.

[Monsta4000]

Hi all,


I am very new to doing anything in excel that is not completely basic and I'm probably missing something obvious but this is driving me crazy.


I'll try keep this simple.


Using this table (1)

(A1)DATE     (B1)START      (C1)FINISH    (D1)DESCRIPTION
(A2)1/1/2015 (B2)8:00 am    (C2)9:00 am   (D2)Meeting 1
(A3)1/1/2015 (B3)11:00 am   (C3)4:00 pm   (D3)Meeting 2

I am trying to populate this table.

(A1)              (B1)DateForm1    (C1)DateForm2        Continues...
(A2)=TIME(7,30,0) (B2)FillForm1    (C2)FillForm2 
(A3)=TIME(8,0,0)  (B3)FillForm1    (C3)FillForm2
(A3)=TIME(8,30,0) (B3)FillForm1    (C3)FillForm2
(A3)=TIME(9,0,0)  (B3)FillForm1    (C3)FillForm2
Continues...


Where:-
DateForm1 =IFERROR(UPPER(TEXT(DATE(ReportYear,MonthNumber,ReportDay),"DDD, MMM D")),"Invalid Date")
DateForm2 =IFERROR(UPPER(TEXT(DATE(ReportYear,MonthNumber,ReportDay+1),"DDD, MMM D")),"Invalid Date")


FillForm1 {=IFERROR(INDEX(Input[[#All],[DESCRIPTION]],MATCH(DATE(ReportYear,MonthNumber,ReportDay),IF([@Time]=Input[[#All],[ START]],Input[[#All],[DATE]]),0),1),"")}
FillForm2 {=IFERROR(INDEX(Input[[#All],[DESCRIPTION]],MATCH(DATE(ReportYear,MonthNumber,ReportDay)+1,IF([@Time]=Input[[#All],[ START]],Input[[#All],[DATE]]),0),1),"")}

Using the above, the DESCRIPTION from table (1) is populating the correct "FillForm" cell... but ONLY where the START matches the TIME. Therefore for an entry like Meeting 1, it only shows in the TIME(8,0,0) cell.


I want to have the DESCRIPTIONS populate for the entire duration (from Start to Finish). Eg. for an entry like Meeting 1, I want it showing in both TIME(8,0,0) cell & TIME(8,30,0) cell.


To do this I have nested an AND condition into the IF section of the FillForm... like this.

FillForm {=IFERROR(INDEX(Input[[#All],[DESCRIPTION]],MATCH(DATE(ReportYear,MonthNumber,ReportDay),IF(AND([@Time]>=Input[[#All],[ START]],[@Time]<input[[#all],[finish]]),input[[#all],[date]]),0),1),"")}

When I add the AND part the code breaks and no cells populate at all :/


Can anyone see what I am doing wrong?
Is there a better way of doing this?


Thanks in advance for your time </input[[#all],[finish]]),input[[#all],[date]]),0),1),"")}

 

[Monsta4000]

Bumpily bump for visibility... i'm really stuck

 

[AhoyNC]

See if this link is of help.
Daily Dose of Excel » Blog Archive » Logical operations in array formulas

It may also help someone get you an answer if you could show the actual result you are looking for instead of "FillForm1" & "FillForm2".

 

[Monsta4000]

Thank You everyone... all sorted now