Formule recherchev perfectionnée

Résolu
Bonjour,

J'ai besoin de votre aide pour élaborer une formule recherchev assez complexe.

Je vous glisse un fichier avec une liste A et une liste B.

https://www.cjoint.com/?0KBprozUUwq

L'idée est de compléter la liste A avec l'élément de colonne H de la liste B. Ceci peut se faire facilement avec une recherchev sur le N° de dossier.

La complexité vient du fait qu'il faut que la Date Exécution et Heure de la liste A soit comprise entre les Date Entrée/Heure Entrée et Date Sortie/Heure Sortie de la liste B.

Pour donner un exemple :

Liste A (ligne 12) - N° Dossier 23931219 le 02/01/2014 à 00:00 doit me rapporter la valeur "3321" de la colonne H de la liste B (ligne 6).

Merci des solutions que vous pourrez m'apporter.

2 réponses

  • Votes
  • Date
  • Date décroissante
  1. Malgré une validation usuelle Sommeprod est une matricielle.
    Pour chaque item-condition la formule balaye le champ et renvoie Vrai ou Faux (assimilable à 1 ou 0)
    ('Liste B'!$A$2:$A$205='Liste A'!C2) renvoie Vrai si le N° 24061065 est trouvé sur la ligne balayée.

    ('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<('Liste A'!A2+'Liste A'!B2) compare date +heure de consigne (B2) à Date + heure de début

    ('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>('Liste A'!A2+'Liste A'!B2) compare date +heure de consigne (B2) à Date + heure de fin

    Ligne par ligne la formule effectue le produit des Vrai ou Faux.

    Le dernier item ('Liste B'!$H$2:$H$205) définit le champ à " sommer":
    La formule additionne les valeurs de ce champ dont les lignes remplissent les 3 conditions.
    C'est pourquoi en cas de doublon on trouvera 6642 au lieu de 3321.
    1. Bonjour,
      Un sommeprod permet l'addition jour + heure mais il y a des risques en cas de doublons.
      En D2 de Liste A!
      =SOMMEPROD(('Liste B'!$A$2:$A$205='Liste A'!C2)*(('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<('Liste A'!A2+'Liste A'!B2))*(('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>('Liste A'!A2+'Liste A'!B2))*('Liste B'!$H$2:$H$205))
      À recopier.
      À vérifier avec soin!
      1. Merci tontong! Une fois de plus, tu résolves mon problème.
        J'ai fait une vérif des 20 premiers N° de dossier et c'est bon!

        J'ai juste modifié un truc car pour 3 résultats (au global), je n'obtenais pas de résultat :
        =SOMMEPROD(('Liste B'!$A$2:$A$205='Liste A'!C2)*(('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<=('Liste A'!A2+'Liste A'!B2))*(('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>=('Liste A'!A2+'Liste A'!B2))*('Liste B'!$H$2:$H$205))
        Peux-tu me mettre l'explication de la formule que je puisse comprendre et la ré-utiliser dans d'autres cas similaires ?