Vous cherchez toutes les commandes d’un client. Vous écrivez un RECHERCHEV, il renvoie une ligne, et il s’arrête là. Pourtant le client en a passé sept.
C’est le comportement normal de la fonction, et c’est aussi sa limite la plus gênante au quotidien. RECHERCHEV répond à la question « quelle est la valeur associée à cette clé », pas à « quelles sont toutes les valeurs associées à cette clé ».
Voici les méthodes qui répondent à la seconde question, avec ce qu’elles coûtent.
Pourquoi RECHERCHEV s’arrête à la première ligne
RECHERCHEV parcourt la colonne de recherche de haut en bas, trouve la première correspondance, renvoie la valeur et termine. Il n’existe aucun paramètre pour lui demander de continuer.
RECHERCHEX ne change rien sur ce point. Son argument mode_recherche accepte la valeur -1 pour balayer du bas vers le haut, ce qui donne la dernière correspondance au lieu de la première. Une seule valeur, dans les deux cas.
La raison est structurelle : ces deux fonctions renvoient une valeur unique. Récupérer plusieurs résultats demande soit une formule qui renvoie un tableau, soit une formule qui concatène.
Méthode 1 : INDEX et PETITE.VALEUR
La solution historique, celle qui circule sur les forums depuis Excel 2003. Elle se recopie vers le bas, et chaque ligne récupère la correspondance suivante.
=SIERREUR(INDEX($B$2:$B$100;PETITE.VALEUR(SI($A$2:$A$100=$D$1;
LIGNE($A$2:$A$100)-1);LIGNE()-1));"")Sur Excel 2016 et 2019, elle demande une validation par Ctrl+Maj+Entrée.
Le mécanisme tient en trois temps. SI teste chaque ligne et renvoie son numéro quand la clé correspond. PETITE.VALEUR récupère le premier de ces numéros, puis le deuxième, puis le troisième, selon la ligne où la formule est recopiée. INDEX va chercher la valeur à ce numéro de ligne.
Le principe est clair. Sa mise en oeuvre l’est beaucoup moins.
Les décalages
Les -1 qui traînent dans la formule compensent le fait que les données commencent en ligne 2 et que la formule est écrite en ligne 2. Déplacez le tableau d’une ligne et tout se décale silencieusement. C’est la source d’erreur numéro un de cette méthode.
Le nombre de lignes à prévoir
Vous devez recopier la formule sur assez de lignes pour couvrir le maximum de correspondances possibles. Trop peu, vous perdez des résultats. Trop, vous remplissez votre feuille de cellules vides.
Méthode 2 : TEXTEJOINDRE, pour concaténer
Quand vous voulez les résultats dans une seule cellule plutôt que sur plusieurs lignes :
=TEXTEJOINDRE(", ";VRAI;SI($A$2:$A$100=$D$1;$B$2:$B$100;""))Plus lisible que la précédente, et elle tient en une cellule. Deux réserves : elle réclame Ctrl+Maj+Entrée sur Excel 2019, et elle n’existe pas avant Excel 2019.
Méthode 3 : FILTRE, sur Microsoft 365
La réponse native moderne, et il faut lui reconnaître sa qualité :
=FILTRE($B$2:$B$100;$A$2:$A$100=$D$1;"Aucun")Une formule courte, lisible, qui renvoie toutes les correspondances sous forme de tableau débordé et se met à jour quand les données changent. Sur un parc entièrement à jour, elle règle le problème.
Ses deux limites. Elle est réservée à Microsoft 365 et renvoie #NOM? sur Excel 2016, 2019 et 2021. Et elle raisonne en condition matricielle, pas en clé : pour un test simple d’égalité cela reste léger, mais la syntaxe demande de manipuler des plages entières là où l’on pense en « toutes les valeurs de ce client ».
Méthode 4 : la fonction dédiée PF.RECHERCHE_TOUTES
HexFormula ajoute une fonction qui pose la question dans les termes où vous la pensez : une clé, une plage où la chercher, une plage de valeurs à ramener.
=PF.RECHERCHE_TOUTES(valeur; plage_recherche; plage_resultat; [si_non_trouve]; [delimiteur])| Paramètre | Description | Obligatoire |
|---|---|---|
valeur | La clé à chercher | Oui |
plage_recherche | La colonne où chercher la clé | Oui |
plage_resultat | La colonne des valeurs à ramener | Oui |
si_non_trouve | Texte affiché si aucune correspondance | Non |
delimiteur | Si renseigné, renvoie un texte concaténé au lieu d’un tableau | Non |
En tableau, par défaut
Tous les produits achetés par Alice :
=PF.RECHERCHE_TOUTES("Alice"; A2:A100; B2:B100)
→ T-shirt
Chaussures
ChapeauLe résultat déborde sur les cellules du dessous, comme FILTRE, et s’ajuste au nombre de correspondances.
En texte, avec un délimiteur
Le cinquième paramètre change la nature du résultat. Renseignez un délimiteur et la fonction renvoie une chaîne unique :
=PF.RECHERCHE_TOUTES("CMD-001"; A2:A100; B2:B100; "Aucun statut"; " → ")
→ "Reçue → Traitée → Expédiée"Ce mode texte fonctionne sur les versions d’Excel dépourvues du moteur de tableaux dynamiques, là où FILTRE échoue.
Voir la documentation complète de PF.RECHERCHE_TOUTES
Cas pratiques
L’historique d’un client dans une fiche
Une fiche client avec la liste de ses achats, sur une seule ligne :
="Achats : " & PF.RECHERCHE_TOUTES(A2; Commandes!$A:$A; Commandes!$C:$C)Les membres d’une équipe projet
=PF.RECHERCHE_TOUTES("Alpha"; A2:A100; B2:B100)
→ Alice, Carol, EveLes tags d’une catégorie produit
Avec un délimiteur choisi pour produire directement le format voulu :
="#" & PF.RECHERCHE_TOUTES("Vêtements"; A2:A100; B2:B100; ""; " #")
→ "#Coton #Bio #Été"Comparatif des méthodes
| Critère | INDEX + PETITE.VALEUR | TEXTEJOINDRE | FILTRE | PF.RECHERCHE_TOUTES |
|---|---|---|---|---|
| Lisibilité | Faible | Moyenne | Bonne | Bonne |
| Résultat en tableau | Oui, par recopie | Non | Oui | Oui |
| Résultat en texte | Non | Oui | Non | Oui |
| Nombre de lignes à prévoir | À la main | Sans objet | Automatique | Automatique |
| Excel 2016 et 2019 | Oui, en CSE | 2019 en CSE | Non | Oui |
| Sans add-in | Oui | Oui | Oui | Requiert HexFormula |
Sur Microsoft 365, pour un besoin ponctuel, FILTRE fait le travail sans rien installer et reste le bon réflexe. L’intérêt d’une fonction dédiée apparaît sur trois points : les versions antérieures à 365, le mode texte concaténé, et une syntaxe qui parle de clé plutôt que de condition matricielle.
Questions fréquentes
RECHERCHEX peut-il renvoyer plusieurs résultats ?
Non. RECHERCHEX renvoie une seule valeur. Son paramètre mode_recherche réglé sur -1 inverse le sens de balayage et donne la dernière correspondance au lieu de la première, mais il n’en ramène jamais plusieurs.
Quelle est la différence entre FILTRE et une recherche multiple ?
FILTRE prend une condition matricielle et renvoie les lignes qui la vérifient. Une fonction de recherche multiple prend une clé et ramène les valeurs associées. Le résultat se ressemble sur un test d’égalité simple, mais la première raisonne en filtre de tableau et la seconde en correspondance de clé.
Comment éviter les cellules vides sous la formule INDEX ?
Vous recopiez la formule sur un nombre de lignes fixe, donc les lignes excédentaires affichent le résultat du SIERREUR, en général une chaîne vide. Réduire ce nombre fait perdre des correspondances. C’est le compromis inhérent à cette méthode, et l’une des raisons de préférer une formule qui dimensionne son résultat elle-même.
Peut-on récupérer plusieurs colonnes à la fois ?
FILTRE accepte une plage de plusieurs colonnes et renvoie le bloc correspondant. Pour ramener une colonne de valeurs associée à une clé, PF.RECHERCHE_TOUTES traite une colonne de résultat à la fois, ce qui couvre la majorité des usages de fiche ou de synthèse.
Que se passe-t-il si la clé n’existe pas ?
Le quatrième paramètre de PF.RECHERCHE_TOUTES définit le texte à afficher, par défaut une chaîne vide. Avec FILTRE, c’est le troisième argument qui joue ce rôle. Avec INDEX et PETITE.VALEUR, il faut envelopper la formule dans un SIERREUR, sans quoi elle renvoie #NOMBRE!.
Conclusion
Récupérer toutes les correspondances d’une clé est une demande banale à laquelle RECHERCHEV et RECHERCHEX ne répondent pas, par construction. Les contournements existent depuis longtemps, mais INDEX avec PETITE.VALEUR accumule les décalages de lignes et les références absolues à surveiller.
Sur Microsoft 365, FILTRE a réglé la question pour les cas simples. Pour les versions antérieures, pour un résultat concaténé en une cellule, ou pour écrire la formule dans les termes du besoin, =PF.RECHERCHE_TOUTES() couvre les deux formats de sortie avec la même syntaxe.
Essayez HexFormula gratuitement sur hexformula.com et récupérez toutes vos correspondances en une formule.
À lire aussi : RECHERCHEV vs RECHERCHEX vs INDEX/EQUIV, lequel choisir