Excel Match Fonction avec des exemples de formule

Ce tutoriel explique comment utiliser la fonction de correspondance dans Excel avec des exemples de formule. Il montre également comment améliorer vos formules de recherche par une formule dynamique avec VLookup et Match.

Dans Microsoft Excel, il existe de nombreuses fonctions de recherche / référence différentes qui peuvent vous aider à trouver une certaine valeur dans une gamme de cellules, et la correspondance en fait partie. Fondamentalement, il identifie une position relative d'un élément dans une gamme de cellules. Cependant, la fonction de correspondance peut faire bien plus que son essence pure.

Fonction de correspondance Excel - Syntaxe et utilisations

La fonction de correspondance dans Excel recherche une valeur spécifiée dans une plage de cellules et renvoie la position relative de cette valeur.

La syntaxe de la fonction de correspondance est la suivante:

Lookup_value (requis) - la valeur que vous souhaitez trouver. Il peut s'agir d'une valeur numérique, texte ou logique ainsi qu'une référence cellulaire.

Lookup_Array (requis) - La plage de cellules à rechercher.

Match_type (facultatif) - définit le type de correspondance. Cela peut être l'une de ces valeurs: 1, 0, -1. L'argument Match_Type défini sur 0 ne renvoie que la correspondance exacte, tandis que les deux autres types permettent une correspondance approximative.

• 1 ou omis (par défaut) - correspondance approximative (plus proche plus petit). Trouve la plus grande valeur dans le tableau de recherche inférieur ou égal à la valeur de recherche. Nécessite de trier le tableau de recherche en ordre ascendant, du plus petit au plus grand ou de A à Z.
• 0 - correspondance exacte. Trouve la première valeur du tableau qui est exactement égale à la valeur de recherche. Aucun tri n'est requis.
• -1 - correspondance approximative (plus proche plus proche). Trouve la plus petite valeur supérieure ou égale à la valeur de recherche. Le tableau de recherche doit être trié par ordre décroissant, du plus grand au plus petit ou de Z à A.

Pour mieux comprendre la fonction de correspondance, faisons une formule simple basée sur ces données: les noms des étudiants dans la colonne A et leurs scores d'examen de la colonne B, triés du plus grand au plus petit. Pour savoir où se trouve un étudiant spécifique (disons, Laura ), utilisez cette formule simple:

=MATCH("Laura", A2:A8, 0)

Facultativement, vous pouvez mettre la valeur de recherche dans une cellule (E1 dans cet exemple) et référence à cette cellule dans votre formule Excel Match:

=MATCH(E1, A2:A8, 0)

Comme vous le voyez dans la capture d'écran ci-dessus, les noms d'étudiants sont entrés dans un ordre arbitraire, et nous définissons donc l'argument Match_type sur 0 (correspondance exacte), car seul ce type de match ne nécessite pas de valeurs de tri dans le tableau de recherche. Techniquement, la formule de correspondance renvoie la position relative de Laura dans la gamme. Mais parce que les scores sont triés du plus grand au plus petit, il nous dit également que Laura a le 5 ^e meilleur score parmi tous les étudiants.

Conseil. Dans Excel 365 et Excel 2021, vous pouvez utiliser la fonction XMatch, qui est un successeur moderne et plus puissant du match.

4 choses que vous devez savoir sur la fonction de correspondance

Comme vous venez de le voir, l'utilisation de Match in Excel est facile. Cependant, comme c'est le cas avec presque toutes les autres fonctions, il y a quelques spécificités que vous devez connaître:

1. La fonction de correspondance renvoie la position relative de la valeur de recherche dans le tableau, pas la valeur elle-même.
2. La correspondance est insensible à la casse , ce qui signifie qu'elle ne distingue pas entre les caractères minuscules et les caractères majuscules lorsqu'il s'agit de valeurs de texte.
3. Si le tableau de recherche contient plusieurs occurrences de la valeur de recherche, la position de la première valeur est renvoyée.
4. Si la valeur de recherche n'est pas trouvée dans le tableau de recherche, l'erreur # N / A est renvoyée.

Comment utiliser Match in Excel - Exemples de formule

Maintenant que vous connaissez les utilisations de base de la fonction de match Excel, discutons de quelques exemples de formules supplémentaires qui vont au-delà des bases.

Match partiel avec les faunes

Comme beaucoup d'autres fonctions, Match comprend les personnages de joker suivants:

• Marque d'interrogation (?) - remplace un seul caractère
• Asterisk (*) - remplace toute séquence de caractères

Note. Les caractères génériques ne peuvent être utilisés que dans des formules de match avec Match_Type réglé sur 0.

Une formule de correspondance avec les caractères génériques est utile dans des situations lorsque vous souhaitez ne pas correspondre à la chaîne de texte entière, mais seulement certains caractères ou une partie de la chaîne. Pour illustrer le point, considérez l'exemple suivant.

En supposant que vous avez une liste de revendeurs régionaux et de leurs chiffres de vente au cours du dernier mois. Vous souhaitez trouver une position relative d'un certain revendeur dans la liste (triée par les montants de vente en ordre décroissant), mais vous ne vous souvenez pas exactement de son nom, bien que vous vous souveniez de quelques premiers personnages.

En supposant que les noms de revendeur se trouvent dans la plage A2: A11, et vous recherchez le nom qui commence par "voiture", la formule se déroule comme suit:

=MATCH("car*", A2:A11,0)

Pour rendre notre formule de correspondance plus polyvalente, vous pouvez taper la valeur de recherche dans une cellule (E1 dans cet exemple), et concaténer cette cellule avec le caractère générique, comme ceci:

=MATCH(E1&"*", A2:A11,0)

Comme le montre la capture d'écran ci-dessous, la formule renvoie 2, qui est la position de "Carter":

Pour remplacer un seul caractère dans la valeur de recherche, utilisez le "?" Opérateur générique, comme ceci:

=MATCH("ba?er", A2:A11,0)

La formule ci-dessus correspondra au nom " Baker " et remettra sa position relative, qui est de 5.

Formule de match sensible à la casse

Comme mentionné au début de ce tutoriel, la fonction de correspondance ne distingue pas les caractères majuscules et minuscules. Pour créer une formule de correspondance sensible à la casse, utilisez la correspondance en combinaison avec la fonction exacte qui compare exactement les cellules, y compris le cas de caractère.

Voici la formule générique sensible à la casse pour faire correspondre les données:

La formule fonctionne avec la logique suivante:

• La fonction exacte compare la valeur de recherche avec chaque élément du tableau de recherche. Si les cellules comparées sont exactement égales, la fonction renvoie vrai, fausse autrement.
• Et puis, la fonction de correspondance compare True (qui est son lookup_value ) avec chaque valeur dans le tableau renvoyé par exact, et renvoie la position de la première correspondance.

Veuillez garder à l'esprit qu'il s'agit d'une formule de tableau qui nécessite une appuye sur le quart Ctrl Shift Entrée pour être terminée correctement.

En supposant que votre valeur de recherche est dans la cellule E1 et que le tableau de recherche est A2: A9, la formule est la suivante:

=MATCH(TRUE, EXACT(A2:A9,E1),0)

La capture d'écran suivante montre la formule de match sensible à la casse dans Excel:

Comparez 2 colonnes pour les matchs et les différences (match ISNA)

La vérification de deux listes pour les matchs et les différences est l'une des tâches les plus courantes d'Excel, et cela peut être effectué de diverses manières. Une formule ISNA / Match est l'une d'entre elles:

Pour toute valeur de la liste 2 qui n'est pas présente dans la liste 1, la formule renvoie " pas dans la liste 1 ". Et voici comment:

• La fonction de correspondance recherche une valeur de la liste 1 dans la liste 2. Si une valeur est trouvée, il renvoie sa position relative, # n / a erreur autrement.
• La fonction ISNA dans Excel ne fait qu'une seule chose - vérifie les erreurs # n / a (ce qui signifie "non disponible"). Si une valeur donnée est une erreur # n / a, la fonction renvoie true, fausse autrement. Dans notre cas, le vrai signifie qu'une valeur de la liste 1 n'est pas trouvée dans la liste 2 (c'est-à-dire qu'une erreur # n / a est renvoyée par correspondance).
• Parce qu'il peut être très déroutant pour vos utilisateurs de voir vrai pour les valeurs qui n'apparaissent pas dans la liste 1, vous enveloppez la fonction IF autour d'Isna pour afficher " pas dans la liste 1 " à la place, ou quel que soit le texte que vous voulez.

Par exemple, pour comparer les valeurs de la colonne B par rapport aux valeurs de la colonne A, la formule prend la forme suivante (où B2 est la cellule la plus supérieure):

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

Si vous vous en souvenez, la fonction de correspondance dans Excel est insensible à elle-même. Pour le faire distinguer le cas de caractère, intégrer la fonction exacte dans l'argument Lookup_Array , et n'oubliez pas d'appuyer sur Ctrl Shift Entrée pour terminer cette formule de tableau :

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

La capture d'écran suivante montre les deux formules en action:

Pour apprendre d'autres façons de comparer deux listes dans Excel, veuillez consulter le tutoriel suivant: Comment comparer 2 colonnes dans Excel.

Excel vlookup et match

Cet exemple suppose que vous avez déjà des connaissances de base de la fonction Excel Vlookup. Et si vous le faites, il y a de fortes chances que vous ayez rencontré ses nombreuses limitations (dont l'aperçu détaillé peut être trouvé pourquoi Excel Vlookup ne fonctionne pas) et recherche une alternative plus robuste.

L'un des inconvénients les plus ennuyeux de Vlookup est qu'il cesse de fonctionner après avoir inséré ou supprimé une colonne dans une table de recherche. Cela se produit parce que VLookup tire une valeur de correspondance en fonction du numéro de la colonne de retour que vous spécifiez (numéro d'index). Étant donné que le numéro d'index est "codé dur" dans la formule, Excel n'est pas en mesure de l'ajuster lorsqu'une nouvelle (s) colonne (s) est ajoutée ou supprimée du tableau.

La fonction Excel Match traite d'une position relative d'une valeur de recherche, ce qui en fait un ajustement parfait pour l'argument COL_INDEX_NUM de Vlookup. En d'autres termes, au lieu de spécifier la colonne de retour comme numéro statique, vous utilisez Match pour obtenir la position actuelle de cette colonne.

Pour rendre les choses plus faciles à comprendre, utilisons à nouveau la table avec les scores d'examen des étudiants (similaire à celui que nous avons utilisé au début de ce tutoriel), mais cette fois, nous récupérerons le score réel et non sa position relative.

En supposant que la valeur de recherche se trouve dans la cellule F1, le tableau de table est $ a 1: $ c 2 $ (c'est une bonne pratique de le verrouiller en utilisant des références de cellules absolues si vous prévoyez de copier la formule dans d'autres cellules), la formule se déroule comme suit:

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

L'argument de 3 ^points ( col_index_num ) est défini sur 3 parce que le score mathématique que nous voulons extraire est la colonne 3 ^RD du tableau. Comme vous pouvez le voir dans la capture d'écran ci-dessous, cette formule Vlookup régulière fonctionne bien:

Mais seulement jusqu'à ce que vous insérez ou supprimez une ou des colonnes:

Alors, pourquoi le #ref! erreur? Parce que COL_INDEX_NUM réglé sur 3 indique à Excel d'obtenir une valeur de la troisième colonne, alors qu'il n'y a maintenant que 2 colonnes dans le tableau de table.

Pour éviter que de telles choses ne se produisent, vous pouvez rendre votre formule VLookup plus dynamique en incluant la fonction de correspondance suivante:

MATCH(E2,A1:C1,0)

Où:

• E2 est la valeur de recherche, qui est exactement égale au nom de la colonne de retour, c'est-à-dire la colonne à partir de laquelle vous souhaitez tirer une valeur ( score mathématique dans cet exemple).
• A1: C1 est le tableau de recherche contenant les en-têtes de table.

Et maintenant, incluez cette fonction de correspondance dans l'argument COL_INDEX_NUM de votre formule Vlookup, comme ceci:

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

Et assurez-vous que cela fonctionne impeccablement, peu importe le nombre de colonnes que vous ajoutez ou supprimez:

Dans la capture d'écran ci-dessus, j'ai verrouillé toutes les références de cellules pour que la formule fonctionne correctement même si mes utilisateurs le déplacent vers un autre endroit dans la feuille de calcul. A Vous pouvez voir dans la capture d'écran ci-dessous, la formule fonctionne très bien après avoir supprimé une colonne; De plus, Excel est suffisamment intelligent pour ajuster correctement les références absolues dans ce cas:

Excel Hlookup et match

De la même manière, vous pouvez utiliser la fonction de match Excel pour améliorer vos formules HLookup. Le principe général est essentiellement le même que dans le cas de Vlookup: vous utilisez la fonction de correspondance pour obtenir la position relative de la colonne de retour et fournissez ce numéro à l'argument ROW_INDEX_NUM de votre formule hlookup.

En supposant que la valeur de recherche est dans la cellule B5, le tableau de table est B1: H3, le nom de la ligne de retour (valeur de recherche pour la correspondance) est dans la cellule A6 et les en-têtes de ligne sont A1: A3, la formule complète est la suivante:

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

Comme vous venez de le voir, la combinaison de Hlookup / Vlookup & Match est certainement une amélioration par rapport aux formules HLOVEUP et VLookup réguliers. Cependant, la fonction de correspondance n'élimine pas toutes leurs limites. En particulier, une formule de match Vlookup ne peut toujours pas regarder sa gauche, et Hlookup Match ne parvient pas à rechercher dans une ligne autre que la plus haut.

Pour surmonter les limitations ci-dessus (et quelques autres), envisagez d'utiliser une combinaison de correspondance d'index, qui fournit un moyen vraiment puissant et polyvalent de faire la recherche dans Excel, supérieur à Vlookup et Hlookup à bien des égards. Les exemples détaillés de guidage et de formule peuvent être trouvés dans Index & Match dans Excel - une meilleure alternative à Vlookup.

C'est ainsi que vous utilisez des formules de correspondance dans Excel. Espérons que les exemples discutés dans ce tutoriel s'avéreront utiles dans votre travail. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Excel correspond aux exemples de formule (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!