1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX & MATCH I think

Discussion in 'Ask an Excel Question' started by DannyV, May 16, 2018 at 1:42 PM.

  1. DannyV

    DannyV New Member

    Messages:
    25
    Hello,

    I am using Chandoo's vacation tracker, which I am modifying to suit the needs of the organization I work in. So far, I've been able to do what I need however I've run into a problem that I'm not sure how to solve.

    I am in the Calculations sheet of the vacation tracker and have modified the formulas in the main calendar calculation portion of the page to :

    =IF(AND(SUMIFS(TABLEAbsence[[Catégorie d''absence]:[Catégorie d''absence]],
    TABLEAbsence[[Nom]:[Nom]],Calcul!$E88,
    TABLEAbsence[[Date de début]:[Date de début]],"<="&AT$3,
    TABLEAbsence[[Date de fin]:[Date de fin]],">="&AT$3)>0,
    TABLEAbsence[[Approuvée]:[Approuvée]]="BLOOP"),99,
    SUMIFS(TABLEAbsence[[Catégorie d''absence]:[Catégorie d''absence]],
    TABLEAbsence[[Nom]:[Nom]],Calcul!$E88,
    TABLEAbsence[[Date de début]:[Date de début]],"<="&AT$3,
    TABLEAbsence[[Date de fin]:[Date de fin]],">="&AT$3))

    My problem is with the section in red. I am trying to have that portion of the formula return the value from the cell, but it is returning a general value for the whole column from my table (I think). I think I will have to use INDEX and MATCH but am not sure how to go about it. Any help or tips would be appreciated.

    Thanks
  2. DannyV

    DannyV New Member

    Messages:
    25
    I found my solution... Here is the formula I am using which returns the desired value:

    =IFERROR(IF(AND(SUMIFS(TABLEAbsence[[Catégorie d''absence]:[Catégorie d''absence]],TABLEAbsence[[Nom]:[Nom]],Calcul!$E40,TABLEAbsence[[Date de début]:[Date de début]],"<="&AX$3,TABLEAbsence[[Date de fin]:[Date de fin]],">="&AX$3)>0, INDEX(TABLEAbsence[[Approuvée]:[Approuvée]],MATCH(Calcul!$E40,TABLEAbsence[[Nom]:[Nom]],0))="BLOOP"),99,SUMIFS(TABLEAbsence[[Catégorie d''absence]:[Catégorie d''absence]],TABLEAbsence[[Nom]:[Nom]],Calcul!$E40,TABLEAbsence[[Date de début]:[Date de début]],"<="&AX$3,TABLEAbsence[[Date de fin]:[Date de fin]],">="&AX$3)),0)

Share This Page