Fonction décaler avec une recherche

Question

Tout d'abord je tiens à vous remercier par avance pour le temps que vous consacrerez à ma question ! Ci-joint vous trouverez le fichier concerné. Problème : Je cherche à effectuer une fonction décaler en lien avec un terme. Je souhaiterais que la fonction cherche la valeur de la cellule B1 de la feuille 1 dans la feuille 2, et qu'en fonction de la position dans la feuille 2 il me renvoi la valeur d'une des cellules du dessous. Exemple pour le site ALFORTVILLE la formule devra chercher le terme dans la feuille 2 et me renvoyer le résultat 4449. Je réitèrerai le processus mais pour le résultat 4866 puis 28155. Concrètement voici le résultat que je cherche à obtenir : ALFORTVILLE : 4449 4866 28155 BAGNEUX : 3457 4944 24502 Merci d'avance pour votre aide! En espérant avoir été suffisamment clair...

njhub · Answer

Voyez si le fichier joint vous convient (colonnes L à N de la "Feuil1")...

MFerrand · Answer

=SI(SOMMEPROD(--(Feuil2!$A$1:$H$17=$B5))=1;INDEX(Feuil2!$A$1:$H$17;SOMMEPROD((Feuil2!$A$1:$H$17=$B5)*LIGNE(Feuil2!$A$1:$H$17))+3;SOMMEPROD((Feuil2!$A$1:$H$17=$B5)*COLONNE(Feuil2!$A$1:$H$17))+COLONNE(A:A)-1);"") Dans le modèle, cette formule va en L5. A étendre sur les 3 colonnes, et sur toutes les lignes suivantes. La plage de recherche sera à actualiser selon son extension. Cordialement...

MFerrand · Answer

SOMMEPROD(--(Feuil2!$A$1:$H$17=$B5))=1 Dans cette expression mise en condition d'une fonction SI, SOMMEPROD teste chaque cellule de la plage indiquée pour y trouver le nom de la ville (B5) et établit une matrice de même taille dans laquelle on aura VRAI si le nom de ville est dans la cellule, FAUX s'il n'y est pas. Et il fait la somme des éléments de la matrice, mais pour qu'il puisse la faire, on a placé un opérateur unaire -- qui a pour effet de transformer VRAI en 1 et FAUX en 0 (on peut à la place opérer une multiplication par 1...). La condition est que cette somme soit égale à 1 : si elle est 0, la ville n'y est pas, si elle est &gt;0, elle y est plusieurs fois, et on ne peut alors opérer... Si la condition est réunie on applique alors la fonction INDEX à ladite plage : INDEX(Plage;IndexLigne;IndexColonne) Syntaxe habituelle, mais il faut donc calculer alors les index de ligne et de colonne de la valeurà renvoyer. Pour la ligne, on sait qu'elle se trouve 3 lignes en dessous du nom de la ville : SOMMEPROD((Feuil2!$A$1:$H$17=$B5)*LIGNE(Feuil2!$A$1:$H$17))+3 Dans cette expression, SOMMEPROD multiplie la matrice vue précédemment qui renvoie VRAI (=1) pour la cellule portant le nom par une matrice égale des numéros de ligne des cellules de la plage, ce qui produit une matrice dont toutes les valeurs seront à 0 sauf une qui sera le numéro de ligne de la cellule Ville. A ce résultat on ajoute donc 3 pour avoir l'indexLigne. On fait la même chose pour l'indexColonne : SOMMEPROD((Feuil2!$A$1:$H$17=$B5)*COLONNE(Feuil2!$A$1:$H$17)) La valeur cherchée se trouve dans la même colonne que la ville, on a donc ainsi son index sans avoir rien à rajouter, mais on doit trouver aussi 2 autres valeurs sur la même ligne dans les colonnes qui suivent. On va donc ajouter un élément pouvant varier à la recopie de la formule sur les colonnes suivantes : +COLONNE(A:A)-1 COLONNE(A:A) renvoie 1, numéro de colonne de A, et recopié sur la ligne deviendra COLONNE(B:B) qui renvoie 2, puis COLONNE(C:C) qui renvoie 3. En enlevant 1, on ne décale pas le numéro initial trouvé et on incrémente les suivants. Cordialement...