Discussion :

[zaza45]

Bonjour

J'ai un problème sans doute de synthaxe avec une formule

J'ai une table A1:E20
Je recherche une valeur dans la colonne E de ma table A1:E20
si dans la colonne D je trouve la valeur D2
et si dans la colonne B, je trouve la valeur B2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Feuil1!$A$1:$E$20;EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0);5)

la premiére condition fonctionne bien, mais je ne trouve pas comment associer la 2ème condition dans la fonction equiv

EXCEL me signale que j'ai une erreur avec la synthaxe suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Feuil1!$A$1:$E$20;EQUIV(ET(Feuil2!D2;Feuil1!$D$1:$D$20;0);(Feuil2!B2;Feuil1!$B$1:$B$20;0);5)

Merci de votre aide

[bab22]

Bonjour,

Ton erreur de syntaxe vient du fait que la fonction ET renvoie VRAI ou FAUX et donc la sortie ne peut pas etre utilisée dans ta fonction EQUIV.

A mon avis, tu dois d'abord vérifier que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0) = EQUIV(Feuil2!B2;Feuil1!$B$1:$B$20;0)

car tu ne pourras pas trouver une valeur dans la colonne E si la formule renvoie deux lignes différentes (une pour Feuil2!B2 et une pour Feuil2!D2)
et après utiliser l'index sur la valeur unique trouvée.

Sur une meme ligne de code, ca donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=INDEX(Feuil1!$A$1:$E$20;SI(SI(EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0)=EQUIV(Feuil2!B2;Feuil1!$B$1:$B$20;0);"O";"N")="O";EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0);"N");5)

Le seul problème est que cela renvoie #VALEUR! s'il n'a pas trouvé B2 et D2 dans les colonnes B et D.

J'espère que ca pourra t'aider.

[cafeine]

Hello,

si tu concatènes tes 2 colonnes, tu peux revenir au cas d'une seule condition, non ?

[Michel_M]

bonjour à tous,
je suppose que la formule est en feuil1 et qu'il faut que les valeurs B2 et D2 soient sur la m^ligne dans le colonnes B et D

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=si(equiv(feuil2!D2;D1:D20;0)=equiv(feuil2!B2:B1;0);index(E1:E20;equiv(feuil2!B2;B1:B20;0));"")

n'est pas traité le cas où D2 ou B2 ne sont pas dans les colonnes B et D

formule non testée, syntaxe à vérifier...

[zaza45]

Bonjour à tous

merci de me donner un gros coup de pouce, car je suis complètement perdue.
La première formule me donne effectuivement un #valeur
et la seconde prend la condition à vide
je vous envoie le fichier, ce sera peut-être plus simple

En fonction du

[Michel_M]

re,

en regardant ton fichier, tu as plusieurs problèmes: les numéros d'ordre et le modèle apparraissent plusieurs fois: par exemple 4 et AW (de mémoire); pire tu as plusieurs lignes identiques dans table! AW et 7 par exemple. (et m^ avec les dates)
IL faudrait donc concaténer comme le proposait Cafeine et qu'il n'y ait pas de doublons dans la concaténation.
Ce n'est qu'une fois résolu ces problèmes qu'on pourra trouver une solution.

[wilfried_42]

re:

un autre solution mais toujours avec la meme condition que michel, pas de doublon

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=index(matrice;sommeprod((plage1=critere1)*(plage2=critere2)*ligne(plage1))-1;colonne)

en rouge -1 si ta matrice commence en ligne 2, à adapter

[Michel_M]

Bonsoir à tous,

Mine de rien, c'est loin d'être évident ton truc! ne serait ce pas sponsorisé par doliprane?

Ci joint proposition à partir d'une concaténation comme le préconisait Caféine et en utilisant "decaler..."

J'ai pas tout vérifié, donc.. et à toi de réaménager si cette usine à gaz te convient.

Amicalement

[zaza45]

Bonjour à tous

et merci à wilfried

Après plusieurs essais wielfried à résolu le pb avec la fonction suivante si cela intéresse quelqu'un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function gw_recherche(val1 As Range, val2 As Range, rest As Range) As Variant
    Dim gwcel As Range, i As Long
    a = val1.Value: b = val2.Value
    i = 0
    For Each gwcel In Range("gw_code")
        If gwcel = val1.Value Then
            If Range("gw_codif")(i) = val2.Value Then
               gw_recherche = rest(i)
               Exit For
            End If
        End If
        i = i + 1
    Next
End Function

C'est super et très rapide en temps de réponse
Merci à tous

[Bigalo]

Bonjour,

Nouvel arrivé sur le forum, j’ai parcouru les questions récentes. Celle-ci m’a paru intéressante, et j'ai voulu trouver une solution sans faire appel à VBA.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"");0))}

à valider en matricielle, fonctionne parfaitement sur ton fichier Index Equiv.xls, que je remets en pièce jointe.

L'astuce, assez classique, consiste à remplacer dans la formule le contenu testé en colonne B, par une chaine vide, si on ne trouve pas la valeur cherchée en colonne D la valeur de référence.

Ainsi EQUIV() renvoie un indice où les 2 conditions sont remplies, ce qui permet d'employer ensuite INDEX().

Attention, s’il y a plusieurs lignes ayant les valeurs cherchées à la fois dans les colonnes B et D, le résultat obtenu correspondra à la première ligne remplissant les 2 conditions.

Cordialement,

Michel Gaboly

Bonjour à tous

et merci à wilfried

Après plusieurs essais wielfried à résolu le pb.

C'est super et très rapide en temps de réponse
Merci à tous

[zaza45]

Bonjour Bigalo et Bonne Année

L'année commence bien...

Je viens de rentrer de congès et j'ai testé ta formule. Super cela fonctionne parfaitement sur mon fichier réel.

Mais je ne comprends pas l'astuce de la chaine vide si on ne trouve pas la valeur cherchée en colonne D.

Effectivement
=SI(D120 = 'nouveau modèle'!D$2;B1:B20;"")
me donne toujours une chaine vide mais ma valeur en colonne D existe bien.

Je n'ai pas assez de notion sous excel pour comprendre la subtilité. Peux-tu m'expliquer plus en détail

Merci beaucoup pour ton idée géniale

[Pierre Fauconnier]

Bonjour

C'est marrant. Je viens de répondre ici à une question similaire...

Zaza45... As-tu bien validé la formule comme une matricielle? (ctrl+maj+enter)

[zaza45]

Bonjour Pierre

Le problème n'est pas tout à fait le même dans mon fichier car je dois gérer des doublons. La solution de Bigalo fonctionne bien mais je ne comprends pas comment cela fonctionne.

=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D120 = 'nouveau modèle'!D$2;B1:B20;"");0))

Ma culture sur excel est assez succinte!

En tout cas merci pour l'info

[Bigalo]

Bonjour,

Merci pour le compliment dithyrambique

• Sélectionne la cellule G4 de la feuille "Table" du fichier que je t’ai renvoyé.
• Puis dans la barre de formules, sélectionne "SI(D1: D20 = 'nouveau modèle'!D$2;B1:B20;"")". (NB l’espace entre ":" et "D20" évite que ":" suivi de "D" soit transformé en smiley)
• Appuie sur la touche "F9", tu obtiens le résultat de la fraction de formule sélectionnée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{"";"";"";"";"";"";"";"";"";"";"";"";"";"";"AW";"AK";"";"";"";""}

Tu constates alors que la fonction SI() a pour effet de remplacer pour le calcul, les valeurs de la plage B1:B20 par elles-mêmes si on a sur la même ligne, en colonne D, la valeur de la cellule D$2 de la feuille "nouveau modèle", ou par une chaîne vide dans le cas contraire.

La formule en G4, à valider en matricielle,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"");0))}

équivaut donc à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;{"";"";"";"";"";"";"";"";"";"";"";"";"";"";"AW";"AK";"";"";"";""};0))}

Le but du jeu est de trouver en colonne D la première ligne où 2 critères sont respectés : en colonne B, la valeur en B2 de la feuille "nouveau modèle", et en colonne D, la valeur en D2 de cette même feuille.

La substitution opérée par la fonction SI() revient à éliminer pour la recherche les valeurs de la colonne B, pour lesquelles la condition portant sur la colonne D n’est pas remplie. Cela revient donc à chercher en colonne B les lignes remplissant l’égalité avec le contenu de la cellule B2 de la feuille "nouveau modèle" parmi les seules lignes où la condition portant sur la colonne D est remplie.

Ce faisant, la première cellule trouvée avec EQUIV() remplit nécessairement les 2 conditions !

Cordialement, et bonne année à toi aussi.

Bonjour Bigalo et Bonne Année

L'année commence bien...

Je viens de rentrer de congès et j'ai testé ta formule. Super cela fonctionne parfaitement sur mon fichier réel.

Mais je ne comprends pas l'astuce de la chaine vide si on ne trouve pas la valeur cherchée en colonne D.

Effectivement
=SI(D120 = 'nouveau modèle'!D$2;B1:B20;"")
me donne toujours une chaine vide mais ma valeur en colonne D existe bien.

Je n'ai pas assez de notion sous excel pour comprendre la subtilité. Peux-tu m'expliquer plus en détail

Merci beaucoup pour ton idée géniale

[Bigalo]

Re,

Pierre, tu sais bien que les grands esprits se rencontrent

A bientôt.

Bonjour

C'est marrant. Je viens de répondre ici à une question similaire...

[zaza45]

Ca y est, j'ai comprisl'astuce.
Je pense que je vais réutiliser assez souvent cette fonction.
Moi qui ne connaissait même pas la fonction INDEX/EQUIV!
Merci beaucoup pour tes explications très simples.

[Pierre Fauconnier]

Re,

Pierre, tu sais bien que les grands esprits se rencontrent

A bientôt.

Je sens que cette année commence en beauté, Michel... Content de te retrouver ici, en tout cas...!!

[Bigalo]

C’est un couple effectivement très utile.

Il y a également une fonction très puissante et sous-employée, c’est SOMMEPROD(). Tu la connais ?

Par exemple dans ton fichier,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDIRECT("E" & SOMMEPROD((B2:B19 = 'nouveau modèle'!B$2) * (D2:D19 = 'nouveau modèle'!D$2) * LIGNE(B2:B19)))

fournit également ce que tu cherches à condition, qu’il n’y ait qu’une ligne répondant aux 2 critères.

Cordialement,

Ca y est, j'ai comprisl'astuce.
Je pense que je vais réutiliser assez souvent cette fonction.
Moi qui ne connaissait même pas la fonction INDEX/EQUIV!
Merci beaucoup pour tes explications très simples.