Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH$36)

[MACx]

Bonjour,

Après maintes recherches je suis tombé sur plusieurs formules qui ont fait mon bonheur, mais lorsque je commence à les combiner, je n'obtiens rien comme résultat, voici l'explication et le but de ma formule:

Feuil1: J'ai un tableau de données $A$1:$BH$36 --> "Tablo" (Il s'agit d'un tableau de pointage qui s'incrémente au fil des jours)
Feuil2: Recherche et extraction de données à partir de Tablo


Dans la Feuil2, colonne A j'ai des dates et colonne B je veux voir que s'est-il passé à cette date là (d'après Tablo)

Dans Feuil1: Les dates se trouvent un peu n'importe où mais le résultat à renvoyer est TOUJOURS 6 ou 7 lignes (selon les formules utilisées) en dessous (ex: en B2= 02/06/2014/2014 => B8="Valeur_cherchée") = "Présence"

Je réalise alors dans un premier temps, un test :

Test1:
Feuil2: en D2=INDEX(Feuil1!B2:B8;7) --> "Valeur_cherchée" OK

Dans ce test j'indique "manuellement" dans ma formule où commence mon tableau de recherche (càd, première cellule en haut à gauche de la plage de recherche.
NB: ne pas confodre avec A1 qui est la première cellule en haut à gauche de Tablo)

Or, ce tableau/plage de recherche est toujours aléatoire (selon la date recherchée)

Pour cela je vais alors employer d'autres formules (encore deux test différents qui vont me donner le même résultat)

Test2:
Feuil2: en E2=ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36));SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4) --> Résultat affiché B2 (car en Feuil2!A2 j'ai la valeur 02/06/2014

Test3: (Là, je veux obtenir (de manière un peu archaïque, je le reconnais (d'où ma demande à l'aide!) la référence de la formule INDEX) = Test2 augmenté lol
Feuil2: en F2="Feuil1!"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36));SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4)&":"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36))+6;SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4) --> Résultat affiché Feuil1!B2:B8

Et là ça se complique, je veux intégrer mon Test3 dans ma formule INDEX (Test1), Soit,
en B2=INDEX("Feuil1!"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36));SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4)&":"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36))+6;SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4);7) --> Résultat affiché #VALEUR

Là, je panique... Désespération !!!! Alors je me tourne vers vous, en espérant que quelqu'un y comprenne quelque chose et m'apporte le miracle dont j'ai besoin...

Pour étayer tout ça, ci-joint un fichier excel.

 

[vgendron]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

Hello Macx,

tu y étais presque ;-)

voir la PJ.
pour simplifier les formules, j'ai nomé ton tableau A1:BH36 en tant que Tablo..

PS, pour les #valeurs que j'ai laissé tel quel. c'est parce que la date cherchée n'apparait pas dans le tableau..
il suffirait donc de modifier les formules pour qu'en cas d'erreur, la formule n'affiche rien..

 

[JBOBO]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

Bonjour,

Si toutefois j'ai bien compris (c'est pas souvent !) tu peux essayer en F2 :

=INDEX(INDIRECT(E2);7)

ou encore en remplaçant E2 par sa formule :

=INDEX(INDIRECT("Feuil1!"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36));SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4)&":"&ADRESSE(SOMMEPROD((Feuil1!$A$2:$BH$36=Feuil2!A2)*LIGNE(Feuil1!$A$2:$BH$36))+6;SOMMEPROD(((Feuil1!$A$2:$BH$36=Feuil2!A2)*COLONNE(Feuil1!$A$2:$BH$36))*1);4));7)

 

[vgendron]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

la formule modifiée
=SIERREUR(DECALER(INDIRECT("Feuil1!" &D2);6;0);"")

et une seule et unique formule en Colonne B (sans avoir besoin de toutes les autres colonnes de test)
=SIERREUR(DECALER(INDIRECT("Feuil1!" &ADRESSE(SOMMEPROD((Tablo=A2)*LIGNE(Tablo));SOMMEPROD(((Tablo=A2)*COLONNE(Tablo)));4));6;0);"")

 

[MACx]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

Hey! vous etes géniaux les mecs !
Tout d'abord merci pour le temps que vous avez consacré à lire et comprendre mon "roman" lol
Je ferais les modifs sur mon tableau d'origine demain ou plus tard ... Aujourd'hui je suis sur d'autres formules.
Bien entendu je vous tiens au courant de votre contribution.
Merci encore une fois

 

[MACx]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

Excuse moi vgendron, mais je n'y arrive pas avec ta deuxième proposition... Je n'obtiens que des cellules vides
En revanche ta première solution fonctionne à merveille... mais en une seule colonne
Merci de ton aide

 

[vgendron]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

hello

Je viens de comprendre pourquoi la deuxième ne te donne que des cellules vides.
en fait. j'imagine que tu as un fait un copier coller de la formule à partir du post vers excel.


quand tu fais ca. dans le post, la formule est sur deux lignes, du coup. le copier coller rajoutte un espace entre SOMM et PROD..
et forcément. excel. y connait pas la fonction SOMM PROD ;-)
faut donc juste enlever cet espace

=SIERREUR(DECALER(INDIRECT("Feuil1!" &ADRESSE(SOMMEPROD((Tablo=A2)*LIGNE(Tablo));SOMMEP ROD(((Tablo=A2)*COLONNE(Tablo)));4));6;0);"")

 

[MACx]

Re : Trouver une valeur à partir d'une plage aléatoire dans un tableau figé ($A$1:$BH

Je suis trop bête, j'avais pas vu le "piège" J ene me ferai plus avoir
Merci beaucoup ! C'est vraiment sympa de ta part d'avoir pris du temps pour m'aider.

Merci également à JBOBO