Aide recherche chaine de caractères dans matrice et renvoi No. ligne

[Chincha]

Bonjour,

J'essaie sans succès depuis hier à écrire une fonction à priori toute simple, mais je bute sur les limitations des fonctions Excel non-matricielles.

Il s'agit de rechercher une chaîne de caractères dans une matrice et renvoyer son numéro de ligne.
Pour simplifier, j'ai 2 feuilles de valeurs.
Une feuille 1 contenant 4 colonnes de valeurs :

• A: Code interne,
• B: Reférence Fournisseur 1,
• C: Reférence Fournisseur 2,
• D: Reférence Fournisseur 3.

Une feuille 2 contenant 1 colonnes de valeurs, et 2 colonnes de contrôle/recherche :

• A: Référence Fournisseur
• B: Match = vérifie si la Référence en colonne A est présente dans une des colonnes BCD Feuil1 (OUI/NON).
• C: Code = renvoie le Code interne correspondant de la colonne A feuille 1 si la Ref. est trouvée dans une des colonnes B, C ou D en feuille 1.

Je joins un exemple en PJ pour mieux visualiser.
Merci d'avance pour m'aider à avancer un peu !

 

[sylvanu]

Bonjour Chincha,
On peut faire une recherche multiple des Ref sur les trois colonnes.
Un essai pour voir en Feuil2 C2:

=SIERREUR(INDEX(Feuil1!A:A;SIERREUR(EQUIV(A2;Feuil1!B:B;0);SIERREUR(EQUIV(A2;Feuil1!C:C;0);SIERREUR(EQUIV(A2;Feuil1!D:D;0);""))));"Non")

La colonne Match ne sert à rien, si le code n'est trouvé nulle part le Sierreur renvoie Non.

 

[Pounet95]

Bonjour Sylvanu, Chincha
Je ne sais pas si tu ouvres les classeurs en PJ ( moi je le fais au risque de ....) mais , volontairement ou pas, il y a en feuil1 plusieurs références pour une même recherche de la feuil 2.
Chincha, quelle est la bonne ?

 

[sylvanu]

Bonjour le fil,
Pounet, j'ouvre les PJ. effectivement dans ce fichier il y a des erreurs :

Ensuite à chacun de prendre le risque ou non de continuer.

 

[Staple1600]

Bonjour le fil, Pounet95, Chincha, sylvanu

J'ai compris la même chose que sylvanu.
Mais ma formule étant plus alambiquée, je préfère la sienne
Et laisse donc la mienne dans sa cellule

 

[Pounet95]

Binjour Staple1600
Moi j'étais parti dans un "monde" que j'ai peu pratiqué du temps où je travaillais, à savoir les INDEX, EQUIV, INDIRECT, etc....
Sinon je parlais de ça, ci-dessous

 

[Staple1600]

Re

•>Pounet95
Oui, j'ai aussi ce dont ce je viens de m'apercevoir.
Diantre! Y a des doublons !

 

[Chincha]

Bonjour Chincha,
On peut faire une recherche multiple des Ref sur les trois colonnes.
Un essai pour voir en Feuil2 C2:

=SIERREUR(INDEX(Feuil1!A:A;SIERREUR(EQUIV(A2;Feuil1!B:B;0);SIERREUR(EQUIV(A2;Feuil1!C:C;0);SIERREUR(EQUIV(A2;Feuil1!D:D;0);""))));"Non")

La colonne Match ne sert à rien, si le code n'est trouvé nulle part le Sierreur renvoie Non.

Merci Sylvanu, en fait le problème est beaucoup plus complexe car il s'agit de nombreux fichiers à traiter avec des centaines/milliers de lignes. J'ai volontairement réduit un des fichiers au minimum en supprimant toutes les colonnes/lignes inutiles, d'ou peut-être des erreurs résiduelles du fichier source.
La recherche fonctionne effectivement dans cet exemple et je te remercie pour la piste, mais pas dans tous les cas rencontrés sur mes fichiers ou il faut adapter la fonction au cas par cas, c'est pourquoi j'ai besoin vraiment d'une recherche matricielle et non pas multiple colonne par colonne.
La colonne Match était juste pour avoir un contrôle séparé, non essentiel effectivement, car je ne voulais pas que des OUI/NON apparaissent dans cette colonne.

 

[Chincha]

Bonjour Sylvanu, Chincha
Je ne sais pas si tu ouvres les classeurs en PJ ( moi je le fais au risque de ....) mais , volontairement ou pas, il y a en feuil1 plusieurs références pour une même recherche de la feuil 2.
Chincha, quelle est la bonne ?

Oui c'est normal, comme expliqué, j'ai omis toutes les infos inutiles pour ne pas compliquer la demande d'aide. Un article peut avoir plusieurs fournisseurs, je veux juste remonter le Code correspondant de la feuille 1 Colonne A s'il y a présence de la référence cherchée.

 

[sylvanu]

Re,

je bute sur les limitations des fonctions Excel non-matricielles.

me faisait penser que vous recherchiez une formule non matricielle.
Ne serait il pas plus simple de passer par une fonction personnelle ?

 

[surchat]

Bonjour,
fonctionne pour 3 fournisseurs,
Bon dimanche,
Andy

 

[Chincha]

Re,
me faisait penser que vous recherchiez une formule non matricielle.
Ne serait il pas plus simple de passer par une fonction personnelle ?

Non justement, j'ai essayé de bricoler avec les EQUIV, RECHERCHEV etc., mais il faudrait idéalement une fonction VBA oui.

 

[sylvanu]

Re,
Une PJ avec une fonction perso :

Option Base 1
Function RechercheCode(NRef, ListeRef As Range, Matrice As Range)
On Error GoTo Fin
Dim Lig As Integer, Col As Integer, IndexLig As Byte
Lig = Cells(Matrice.Rows.Count, 1).End(xlUp).Row
Col = Matrice.Columns.Count
IndexLig = 0
For i = 1 To Col
    Set Plage = Range(Matrice.Cells(1, i), Matrice.Cells(Lig, i))
    If Not IsError(Application.Match(NRef, Plage, 0)) Then
        IndexLig = Application.Match(NRef, Plage, 0)
    End If
Next i
If IndexLig = 0 Then
    RechercheCode = "Non"
Else
    RechercheCode = ListeRef.Cells(IndexLig, 1)
End If
Fin:
End Function

Syntaxe :

=RechercheCode(Ref à checher;Colonne des Code;Matrice de recherche)

ex :
=RechercheCode(A2;Feuil1!A:A;Feuil1!B:D)

 

[Chincha]

Merci beaucoup Sylvanu, elle fonctionne en effet avec l'exemple mais pas avec le fichier "réel", j'ai que des "Non" renvoyés. J'essaye de débugger pour comprendre quel est le souci.

 

[Chincha]

Il semble que ce soit les titres des colonnes (la première ligne) qui posent problème ?

La fonction est:

• OK lorsque je sélectionne un Range de cellules pour ListRef (Colonne des Codes) et Matrice (matrice de recherche) : voir capture 1, le code "03.07.01.01" renvoyé est correct.

• Pas OK lorsque je sélectionne les colonnes entières pour ListRef (Colonne des Codes) et Matrice (matrice de recherche) : voir capture 2, la fonction renvoie "NON" alors que l'on voit bien la valeur 576147 est présente en 2e position dans le 3e argument Matrice.

J'ai éliminé tout ce qui pouvait parasiter en rangée 1 (filtrage, commentaires, cellules fusionnées, formats de cellules etc.), mais rien n'y fait.
Il faut préciser aussi que les donnés en colonne M:M sont le résultat d'une autre fonction matricielle, et non pas des chaines de caractères "bruts".