INDEX/MATCH with nested IF/AND troubles.

Monsta4000

New Member
Joined
May 23, 2015
Messages
3
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)
Code:
(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.
Code:
(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.
Code:
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),"")}
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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