Results 1 to 4 of 4

Thread: INDEX/MATCH with nested IF/AND troubles.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question INDEX/MATCH with nested IF/AND troubles.

    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]
    

    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

  2. #2
    New Member
    Join Date
    May 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX/MATCH with nested IF/AND troubles.

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

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,161
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX/MATCH with nested IF/AND troubles.

    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".

  4. #4
    New Member
    Join Date
    May 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX/MATCH with nested IF/AND troubles.

    Thank You everyone... all sorted now

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •