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

Supposons que vous ayez une liste de mots-clés, pour récupérer le premier mot-clé qui apparaît dans une cellule spécifique, alors que la cellule contient plusieurs autres valeurs, vous devrez utiliser une formule INDEX et MATCH avec l'aide des fonctions AGGREGATE et SEARCH.

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

Pour obtenir le premier mot-clé correspondant dans une cellule par rapport à la liste de mots-clés comme indiqué dans le tableau ci-dessus, cela signifie que nous devons effectuer une correspondance de contenu plutôt qu'une correspondance exacte. Pour ce faire, vous pouvez utiliser la fonction SEARCH pour fournir les positions des mots-clés apparus dans la cellule en chiffres numériques à la fonction AGGREGATE. Ensuite, AGGREGATE obtiendra le plus petit nombre en définissant son argument function_num à15 et l'argument ref2 à1. Ensuite, utilisez MATCH pour localiser la première plus petite valeur et fournissez le numéro de position à INDEX pour récupérer la valeur à 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 entrée avec Ctrl + Shift + Enter.

Pour récupérer le premier mot-clé correspondant apparu dans la cellule B5 par rapport à la colonne des mots-clés, veuillez copier ou entrer la formule ci-dessous dans la cellule C5, et appuyer sur Ctrl + Shift + Enter pour obtenir le résultat :

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

√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que le keyword_rng dans la formule ne changera pas lorsque vous déplacez ou copiez la formule vers d'autres cellules. Cependant, il n'y a pas de signes dollar ajoutés à lookup_cell car vous souhaitez qu'il soit dynamique. Après avoir entré la formule, faites glisser la poignée de remplissage vers le bas pour appliquer la formule aux cellules ci-dessous.

Explication de la formule

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

• SEARCH($E$5:$E$7,B5): La fonction SEARCH renvoie la position de chaque mot-clé de la plage $E$5:$E$7 lorsqu'il est trouvé en chiffres numériques, et l'erreur #VALUE! si non trouvé. Le résultat serait dans un tableau comme ceci : {15;11;#VALUE!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): La fonction AGGREGATE avec un function_num de15 et une option de6 renverra la plus petite valeur dans le tableau selon l'argument ref21, tout en ignorant les valeurs d'erreur. Ainsi, le fragment renverrait11.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): Le match_type 0 force la fonction MATCH à effectuer une correspondance exacte et à renvoyer la position de 11 dans le tableau {15;11;#VALUE!}. Ainsi, la fonction renverra 2.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): La fonction INDEX renvoie ensuite la2ème valeur dans la plage $E$5:$E$7, qui est bbb.

Remarque

• Si aucune des mots-clés n'est présente dans une cellule, une erreur #NUM! sera renvoyée.
• La formule n'est pas sensible à la casse. Pour effectuer une correspondance sensible à la casse, vous pouvez simplement remplacer la fonction SEARCH par FIND.

Fonctions associées

Formules associées