Récupérer la première valeur correspondante dans une cellule par rapport à une liste

Supposons que vous disposiez d’une liste de mots-clés. Pour récupérer le premier mot-clé apparaissant dans une cellule spécifique — alors que celle-ci contient plusieurs autres valeurs —, vous devrez utiliser une formule combinant INDEX et EQUIV, en exploitant les fonctions AGREGAT et RECHERCHE.

Comment récupérer la première valeur correspondante dans une cellule à partir d’une liste ?

Pour obtenir le premier mot-clé correspondant dans une cellule par rapport à la liste de mots-clés, comme illustré dans le tableau ci-dessus, vous devez effectuer une correspondance partielle plutôt qu’une correspondance exacte. Pour ce faire, utilisez la fonction RECHERCHE afin de transmettre les positions numériques des mots-clés trouvés dans la cellule à la fonction AGREGAT. Celle-ci récupère alors la plus petite valeur en définissant son numéro de fonction sur 15 et l’argument ref2 sur 1. Enfin, employez EQUIV pour localiser cette première plus petite valeur, puis transmettez ce numéro de position à INDEX afin d’obtenir la valeur située à cette position.

Syntaxe générique

=INDEX()keyword_rng,MATCH(AGGREGATE(15,6,SEARCH()))keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Remarque : Il s’agit d’une formule matricielle qui nécessite d’être validée par Ctrl+Maj+Entrée.

Pour récupérer le premier mot-clé correspondant apparaissant dans la cellule B5 par rapport à la colonne Mots-clés, veuillez copier ou saisir la formule ci-dessous dans la cellule C5, puis appuyez sur Ctrl+Maj+Entréepour obtenir le résultat :

=INDEX()$E$5:$E$7,EQUIV(AGREGAT(15,6,RECHERCHE()))$E$5:$E$7,B5),1),RECHERCHE($E$5:$E$7,B5),0))

√ Remarque : Les symboles dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que la plage_mots_clésdans la formule ne changera pas lorsque vous déplacerez ou copierez la formule vers d’autres cellules. En revanche, aucun symbole dollar n’est ajouté à la cellule_recherchecar vous souhaitez qu’elle reste dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en dessous.

Explication de la formule

=INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):La fonction RECHERCHE renvoie la position de chaque mot-clé dans la plage $E$5:$E$7 sous forme de nombres lorsqu’il est trouvé, et l’erreur #VALUE! s’il ne l’est pas. Le résultat s’affiche sous forme de tableau comme suit : {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):La fonction AGREGAT avec un numéro de fonction égal à 15 et une option égale à 6 renvoie la plus petite valeur du tableau en fonction de l’argument ref2 égal à 1, tout en ignorant les valeurs d’erreur. Ainsi, cet extrait renvoie 11.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):La valeur match_type égale à 0 oblige la fonction EQUIV à effectuer une correspondance exacte et à renvoyer la position de 11 dans le tableau {15;11;#VALUE!}. La fonction renvoie donc 2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):La fonction INDEX renvoie alors la 2e valeur de la plage $E$5:$E$7, qui est bbb.

Remarque

• S’il n’y a aucun mot-clé dans une cellule, une erreur #NUM! est renvoyée.
• La formule ne tient pas compte de la casse. Pour effectuer une recherche sensible à la casse, remplacez simplement la fonction RECHERCHE par TROUVE.

Fonctions associées

Formules associées