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.

1. DannyVNew 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. DannyVNew 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)