La fonction XMATCH est l’une des fonctions du bundle récemment lancé qui comprend XLOOKUP, LAMBDA et les fonctions d’aide Lambda (LHF) dans Google Sheets.
Une autre nouvelle fonctionnalité passionnante que nous avons obtenue avec ce bundle de fonctions est les Fonctions Nomées.
J’ai déjà publié les autres fonctions du bundle, que vous pouvez trouver dans mon Guide des Fonctions Google Sheets.
En tant que blogueur, ce lancement me met dans l’obligation de mettre à jour plusieurs tutoriels sur ce blog pour inclure de nouvelles solutions aux problèmes abordés.
Dans ce tutoriel, vous pouvez apprendre l’utilisation de la fonction XMATCH avec quelques exemples dans Google Sheets.
Le but de la fonction XMATCH dans Google Sheets
La fonction XMATCH dans Google Sheets recherche et renvoie la position relative d’un élément (search_key) dans une plage de ligne ou de colonne unique (lookup_range).
Cette fonction a quelques modes de recherche et de correspondance pour contrôler le comportement de la recherche de la position relative de l’élément qui la différencie de MATCH.
Syntaxe et arguments
Syntaxe: XMATCH(search_key, lookup_range, [match_mode], [search_mode])
Arguments:
Il y a quatre arguments dans la fonction XMATCH dans Google Sheets. Sur les quatre, les deux derniers sont des arguments optionnels.
Voyons ci-dessous quelles sont les rôles de ces quatre arguments et les valeurs par défaut des deux arguments optionnels.
-
search_key – La valeur (élément) à rechercher.
-
lookup_range – Une seule ligne ou colonne à prendre en compte pour la recherche.
-
match_mode (optionnel, 0 par défaut) – Les quatre modes pour trouver une correspondance pour search_key, qui sont explicites dans le tableau ci-dessous.
MODE DE CORRESPONDANCE DESCRIPTION 0 Correspondance exacte 1 Correspondance approximative (plus grand ou égal) -1 Correspondance approximative (plus petit ou égal) 2 Recherche binaire (pour les plages triées par ordre croissant) -2 Recherche binaire (pour les plages triées par ordre décroissant) -
search_mode (optionnel, 1 par défaut) – Les quatre modes pour rechercher à travers la plage de recherche, qui sont explicites dans le tableau ci-dessous.
MODE DE RECHERCHE DESCRIPTION 1 Recherche de la première entrée à la dernière -1 Recherche de la dernière entrée à la première
Comment utiliser la fonction XMATCH dans Google Sheets – Exemples
Correspondons une date dans une colonne de dates et obtenons sa position relative. Cela nous sera utile pour apprendre les arguments de la fonction XMATCH.
Comme note annexe, vous pouvez utiliser la fonction XMATCH de manière similaire dans une ligne.
A des fins d’exemple, j’ai saisi quelques dates aléatoires dans la colonne A, qui ne sont pas triées dans un ordre quelconque, et quelques noms dans la colonne B aux lignes correspondantes.
Supposons que ce soit une donnée d’exemple de réservation de chambre d’hôtel.
Comment effectuer une correspondance XMATCH dans ce tableau pour trouver la position relative d’une date de réservation donnée ? Comment utiliser cette sortie pour renvoyer le nom de la personne qui a réservé la chambre ?
Correspondance exacte et recherche de la première entrée à la dernière
Formule 1 (Correspondance exacte):
=xmatch(D3,A3:A12)La formule XMATCH ci-dessus recherche la date 24/09/2022 dans A3:A12 et renvoie la position relative de la date correspondante, c’est-à-dire 4.
Nous n’avons pas spécifié les modes de correspondance ou de recherche dans la formule XMATCH ci-dessus.
Elle prend donc les valeurs par défaut, c’est-à-dire 0 (correspondance exacte dans match_mode) et 1 (recherche de la première entrée à la dernière dans search_mode).
Nous pouvons l’utiliser dans la fonction INDEX pour obtenir le nom de la personne, c’est-à-dire Harry, qui a réservé la chambre à la date mentionnée.
Combinaison de la fonction XMATCH et de la fonction INDEX :
=index(B3:B12,xmatch(D3,A3:A12),1)Que se passe-t-il lorsque la clé de recherche dans la cellule D3 est 17/09/2022 ?
Il n’y a aucune correspondance dans la liste. Donc, la formule ci-dessus renverra #N/A, ce qui signifie non disponible.
Si vous utilisez le mode de correspondance 1, la formule recherchera la valeur suivante la plus grande disponible, c’est-à-dire 18/09/2022.
Formule 2 (Plus grand suivant) :
=xmatch(D3,A3:A12,1)La position relative de la première occurrence de la date 18/09/2022 dans la liste est 6. Donc c’est la valeur renvoyée.
Souvenez-vous, la formule utilise le mode de recherche par défaut, c’est-à-dire de la première entrée à la dernière.
La combinaison de la fonction INDEX et de la fonction XMATCH suivante renverra le nom de la personne qui a réservé la chambre à la date la plus grande suivante de la date recherchée, c’est-à-dire Rachel, le 18/09/2022.
=index(B3:B12,xmatch(D3,A3:A12,1),1)Date la plus petite suivante à 17/09/2022 dans la liste est 14/09/2022.
Formule 3 (Plus petit suivant) :
=xmatch(D3,A3:A12,-1)Donc, évidemment, la formule XMATCH ci-dessus renverra 2 comme position relative.
Comme précédemment, nous pouvons l’utiliser dans la fonction INDEX pour décaler autant de lignes dans B3:B12 et obtenir le nom correspondant.
Recherche de la dernière entrée à la première dans la fonction XMATCH
Toutes les formules XMATCH ci-dessus utilisent le mode de recherche par défaut 1, c’est-à-dire de la première entrée à la dernière entrée.
Que se passe-t-il lors de la recherche de la dernière entrée à la première entrée (-1) dans ces formules ? Comment diffèrent-elles lors de la récupération de la position relative ?
Correspondance exacte :
=xmatch(D3,A3:A12,0,-1)Note: Pour la correspondance exacte, la date dans D3 est 24/09/2022, et pour la correspondance approximative (plus grand et plus petit), elle est 17/09/2022.
Cette formule XMATCH retournera 4 même si le search_mode est la dernière entrée à la première, car il n’y a pas de doublons de la clé de recherche dans la liste.
C’est une autre histoire ci-dessous.
Plus grand suivant :
=xmatch(D3,A3:A12,1,-1)Le plus grand suivant à 17/09/2022 dans la liste est 18/09/2022. Sa position relative est 9 lorsqu’il est analysé depuis le bas (dernière entrée à la première).
Plus petit suivant :
=xmatch(D3,A3:A12,-1,-1)Le plus petit suivant à 17/09/2022 dans la liste est 14/09/2022. Sa position relative est 10 lorsqu’il est analysé depuis le bas (dernière entrée à la première).
Note : Dans la fonction XMATCH, la recherche de la première à la dernière et de la dernière à la première sont pertinents lorsqu’il y a des entrées en double dans la colonne de recherche (lookup_range).
Mode de recherche binaire 2 et -2
Pour une recherche et une correspondance ultra-rapides, il est préférable de trier la plage de recherche par ordre croissant ou décroissant.
Ensuite, nous pouvons utiliser les modes de recherche binaire 2 ou -2 en conséquence dans la fonction XMATCH.
Les modes de recherche binaire prennent également en charge la correspondance exacte, la correspondance suivante la plus petite ou la correspondance suivante la plus grande.
La correspondance se fera à partir de la première entrée jusqu’à la dernière dans une correspondance exacte dans une plage triée A-Z.
=xmatch("<strong>apple</strong>",{"<strong>apple</strong>";"apple";"orange";"orange"},0,<strong>2</strong>)Donc, la formule ci-dessus renverra 1.
Mais la correspondance se fera à partir de la dernière entrée jusqu’à la première dans une correspondance exacte dans une plage triée Z-A.
=xmatch("<strong>apple</strong>",{"orange";"orange";"apple";<strong>"apple</strong>"},0,<strong>-2</strong>)Alors ici, la formule renverra 4.
Qu’en est-il de la correspondance suivante la plus grande ou la plus petite en mode de recherche binaire dans la fonction XMATCH dans Google Sheets ?
Veuillez vérifier les Tableaux #1 et #2. La clé de recherche est 17/09/2022.
<Dans les modes de recherche binaire suivant le plus petit et le plus grand, la formule XMATCH renverra la position relative à partir des lignes les plus proches de la clé de recherche.
Veuillez jeter un coup d’œil à la capture d’écran ci-dessus. La ligne rouge montre la position de la date 17/09/2022 dans la liste.
J’ai marqué les dates correspondantes suivantes les plus petites et les plus grandes dans le Tableau 1 et le Tableau 2 ci-dessus.
Formules (Tableau 1; A-Z) :
=xmatch(date(2022,9,17),A3:A12,1,2)Sortie : 5
=xmatch(date(2022,9,17),A3:A12,-1,2)Sortie : 4
Formules (Tableau 2; Z-A) :
=xmatch(date(2022,9,17),D3:D12,1,-2)Sortie : 6
=xmatch(date(2022,9,17),D3:D12,-1,-2)Sortie : 7
Comment utiliser les jokers dans la fonction XMATCH dans Google Sheets
La fonction XMATCH prend également en charge les jokers.
Lorsque vous n’êtes pas certain que la colonne B contient « Total », « Sub Total » ou « Grand Total » et que vous souhaitez renvoyer le dernier montant total dans la colonne C, indépendamment de « Total », « Sub Total » ou « Grand Total », vous pouvez utiliser la combinaison INDEX et XMATCH suivante.
=index(C:C,xmatch("*Total*",B:B,2,-1))Veuillez consulter ce tableau si vous êtes assez novice en matière de jokers dans Google Sheets.
C’est tout. Merci pour votre visite. Profitez-en!
Ressources:
