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

Supposons que vous ayez une liste de mots-clés, pour récupérer le premier mot-clé qui est apparu dans une cellule spécifique, alors que la cellule contient plusieurs autres valeurs, vous devrez utiliser un INDEX et MATCH formule à l'aide de GRANULAT et Rechercher fonctions.

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

Pour obtenir le premier mot-clé correspondant dans une cellule par rapport à la liste des mots-clés comme le tableau ci-dessus, cela signifie que nous devons effectuer une correspondance contenant plutôt qu'une correspondance exacte. Pour ce faire, vous pouvez utiliser la fonction RECHERCHE pour alimenter les positions des mots-clés apparus dans la cellule en nombres numériques dans la fonction AGRÉGATION. Ensuite, AGGREGATE obtiendra le plus petit nombre en définissant son function_num à 15 et l'argument ref2 à 1. Utilisez ensuite MATCH pour localiser la première valeur la plus petite 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 vous oblige à entrer avec Ctrl + Shift + Entrer.

Pour récupérer le le premier mot-clé correspondant est apparu dans la cellule B5 contre la colonne Mots-clés, veuillez copier ou saisir la formule ci-dessous dans la cellule C5, et appuyez sur Ctrl + Shift + Entrer pour obtenir le résultat :

= INDEX ($ E $ 5: $ E $ 7,MATCH(AGRÉGATION(15,6,RECHERCHE($ E $ 5: $ E $ 7,B5),1),RECHERCHE($ E $ 5: $ E $ 7,B5),0))

√ Remarque : les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que le mot-clé_rng dans la formule ne change pas lorsque vous déplacez ou copiez la formule dans d'autres cellules. Cependant, aucun signe dollar n'est ajouté au recherche_cellule puisque vous voulez 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),1),SEARCH($E$5:$E$7,B5),0))

• RECHERCHE($E$5:$E$7,B5): La fonction RECHERCHE renvoie la position de chaque mot-clé de la plage $ E $ 5: $ E $ 7 lorsqu'il est trouvé dans des nombres numériques, et le #VALEUR! erreur si introuvable. Le résultat serait dans un tableau comme celui-ci : {15;11;#VALEUR !}.
• AGRÉGATE(15,6,RECHERCHE($E$5:$E$7,B5), 1) = AGRÉGATE(15,6,{15;11;#VALEUR !}, 1): La fonction AGGREGATE avec un num_fonction de 15 et possibilité de 6 renverra la plus petite valeur du tableau selon le réf2 argument 1, tout en ignorant les valeurs d'erreur. Ainsi, l'extrait reviendrait 11.
• RENCONTRE(AGRÉGATE(15,6,RECHERCHE($E$5:$E$7,B5), 1),RECHERCHE($E$5:$E$7,B5), 0) = RENCONTRE(11,{15;11;#VALEUR !}, 0): L' match_type 0 force la fonction MATCH à effectuer une correspondance exacte et à renvoyer la position de 11 dans le tableau {15;11;#VALEUR !}. Ainsi, la fonction retournera 2.
• INDICE($E$5:$E$7,RENCONTRE(AGRÉGATE(15,6,RECHERCHE($E$5:$E$7,B5), 1),RECHERCHE($E$5:$E$7,B5), 0)) = INDICE($E$5:$E$7,2): La fonction INDEX renvoie alors le 2ème valeur dans la plage $ E $ 5: $ E $ 7, lequel est bbb.

Remarque

• S'il n'y a pas de mots-clés dans une cellule, un #NUM! l'erreur sera renvoyée.
• La formule n'est pas sensible à la casse. Pour effectuer une correspondance sensible à la casse, vous pouvez simplement remplacer la fonction RECHERCHE par IDENTIFIER.

Fonctions connexes

Formules associées