Ce tutoriel vous apprendra comment utiliser la fonction XMATCH pour rechercher une valeur dans plusieurs colonnes sur Google Sheets. Vous apprendrez à coder la formule étape par étape afin de la comprendre et de la modifier selon vos besoins.
L’objectif principal des fonctions de recherche, telles que MATCH ou XMATCH, dans Google Sheets est de trouver la position d’un élément dans une colonne ou une ligne. Elle renverra un nombre relatif aux lignes du tableau, pas aux lignes du classeur. Nous pouvons ensuite utiliser ce nombre pour faire référence à d’autres fonctions telles que INDEX, FILTER, QUERY, etc., afin d’effectuer des tâches supplémentaires.
La fonction XMATCH est utilisée pour rechercher dans un tableau unidimensionnel. Elle nécessite au moins deux arguments pour fonctionner : search_key et lookup_range. Le lookup_range doit être un tableau unidimensionnel.
Syntaxe de la fonction XMATCH sur Google Sheets :
XMATCH(search_key, lookup_range, [match_mode], [search_mode])
Lorsque vous souhaitez utiliser XMATCH avec plusieurs colonnes, vous devrez peut-être faire glisser la formule à travers. Cependant, cela ne répondra pas à vos besoins lorsque vous souhaitez l’utiliser avec d’autres fonctions telles que INDEX ou FILTER.
Alors, comment étendre la plage de recherche de XMATCH à toutes les colonnes d’un tableau ?
Nous pouvons utiliser l’une des fonctions LAMBDA, précisément la fonction MAP, avec d’autres fonctions pour y parvenir.
XMATCH pour plusieurs colonnes sur Google Sheets (sans faire glisser)
Supposons que vous ayez un modèle de vue calendrier sur Google Sheets et que vous souhaitiez rechercher une date particulière pour trouver sa position relative.
La plage de la vue calendrier est B3:H16 et la date à rechercher se trouve dans la cellule J6.
Généralement, pour effectuer une recherche avec XMATCH sur plusieurs colonnes, vous utiliseriez la formule suivante dans la cellule K6 et la feriez glisser jusqu’à obtenir une valeur autre que #N/A :
=XMATCH($J$6, B3:B16)
La formule renverra 9 dans la quatrième colonne. Cela s’explique par le fait que, dans la formule, la référence search_key est absolue et la référence lookup_range est relative. Ainsi, la référence de colonne dans lookup_range augmente lorsque vous la faites glisser vers la droite.
Voici comment utiliser la fonction MAP lambda avec XMATCH pour rechercher dans plusieurs colonnes sans faire glisser la formule :
=SORTN(MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col)))))
Important: Supprimez le wrapper SORTN() pour obtenir les valeurs correspondantes de toutes les colonnes, le cas échéant. Dans ce cas, les résultats seraient {#N/A; #N/A; #N/A; 9; #N/A; #N/A; #N/A}, car il n’y a qu’une seule valeur correspondante.
Cette formule applique la fonction XMATCH à chaque colonne de la plage B3:H16, en utilisant la valeur de la cellule J6 comme clé de recherche. La fonction MAP renvoie ensuite un tableau de résultats, qui est transmis à la fonction SORTN pour renvoyer la première valeur du tableau, autre que #N/A.
Vous pouvez utiliser cette formule avec INDEX de deux manières :
- Pour renvoyer une valeur de n’importe quelle colonne dans la même ligne.
- Pour renvoyer une valeur de la même colonne de n’importe quelle ligne.
Nous verrons ces deux astuces supplémentaires après avoir expliqué la formule XMATCH pour plusieurs colonnes.
Explication de la formule
Nous avons utilisé les fonctions INDEX, COLUMNS, SEQUENCE, SORTN et MAP pour utiliser XMATCH sur plusieurs colonnes dans Google Sheets. Voici un aperçu de leurs rôles :
1. Formule de base
La formule de base est =XMATCH($J$6,B3:H16). Cependant, cette formule ne fonctionnera pas car elle viole la syntaxe de XMATCH, qui n’autorise pas l’utilisation de plusieurs colonnes comme plage de recherche.
Pour remédier à cela, nous pouvons utiliser la fonction INDEX pour renvoyer la première colonne de B3:H16, ce qui donnera à XMATCH une seule colonne (un tableau unidimensionnel) à rechercher. Cette formule est présentée ci-dessous :
=XMATCH($J$6,INDEX(B3:H16,0,1)) // Formule #1
La fonction INDEX prend trois arguments : la référence, la ligne et la colonne. Dans la formule ci-dessus, l’argument de colonne est défini à 1, ce qui signifie que la fonction INDEX renverra la première colonne de la référence (plage de recherche).
Mettez de côté cette formule pour le moment.
2. Tableau virtuel pour le mappage
Nous pouvons utiliser la fonction COLUMNS pour trouver le nombre de colonnes dans la plage de recherche. Si nous enveloppons la fonction COLUMNS avec la fonction SEQUENCE, nous obtiendrons un tableau de nombres comme suit : {1; 2; 3; 4; 5; 6; 7}.
=SEQUENCE(COLUMNS(B3:H16)) // Formule #2
Oui ! Nous avons 7 colonnes dans la plage.
Ce tableau peut être utilisé pour représenter les colonnes dans la plage de recherche, spécifiquement l’argument de colonne de la formule INDEX dans la Formule #1.
Remplacer simplement 1 par la Formule #2 ne suffit pas. Nous devons mapper chaque valeur de la Formule #2 en utilisant la fonction MAP. Cela équivaut à utiliser XMATCH pour plusieurs colonnes. Nous l’apprendrons à l’étape suivante. Mettez également cette formule de côté.
3. Fonction MAP pour utiliser XMATCH dans toutes les colonnes
Voici la formule générique que nous suivrons pour XMATCH dans plusieurs colonnes sur Google Sheets :
Formule générique :
MAP(formule#2, LAMBDA(col, formule_expression))
Où :
- formule#2 est la séquence de numéros de colonne.
- col est le nom de formule#2.
- formule_expression est la formule de base avec l’argument de colonne dans INDEX remplacé par le nom col.
Voici la formule codée en fonction de la formule générique ci-dessus :
=MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col)))) // Formule #3
Cette formule renverra #N/A dans les colonnes qui n’ont aucune correspondance. Nous pouvons envelopper cette formule avec SORTN pour supprimer ces erreurs et ne renvoyer que la valeur correspondante. Il s’agit de notre formule finale pour XMATCH dans plusieurs colonnes.
XMATCH dans plusieurs colonnes : Renvoyer une valeur de la ligne correspondante
Je pense qu’il sera simple d’expliquer comment utiliser XMATCH dans plusieurs colonnes et renvoyer une valeur de la ligne correspondante avec la formule générique suivante :
=INDEX( référence, formule_xmatch_multiples_colonnes )
Où :
- référence est la colonne à partir de laquelle vous souhaitez renvoyer la valeur.
- formule_xmatch_multiples_colonnes est la formule qui renvoie la position relative d’une clé de recherche après correspondance dans plusieurs colonnes.
Appliquons cela à un scénario réel.
Supposons que vous disposiez du tableau suivant dans la plage A1:D, où A1:A contient les noms de pays et B1:D contient la production de blé en millions de tonnes au cours des trois dernières années. Vous souhaitez utiliser XMATCH pour rechercher une quantité de production de blé particulière dans les colonnes B à D et renvoyer le nom du pays de la colonne A.
Si la clé de recherche se trouve dans la cellule G2, la formule suivante fera cela :
=INDEX( A1:A, SORTN(MAP(SEQUENCE(COLUMNS(B1:D)),LAMBDA(col,XMATCH(G2,INDEX(B1:D,0,col))))))
Je sais que certains d’entre vous savent peut-être comment modifier la fonction XMATCH dans ce combo pour renvoyer une correspondance approximative. Cependant, je ne recommande pas de le faire lors de l’utilisation de XMATCH pour plusieurs colonnes et renvoi d’une valeur.
Créer une liste déroulante à partir de la plage B2:D dans la cellule G2 est un bon moyen de s’assurer que la clé de recherche est toujours une correspondance exacte. Cela évitera les erreurs dans la formule.
XMATCH dans plusieurs colonnes : Renvoyer une valeur de la colonne correspondante
Vous pouvez avoir besoin de renvoyer une valeur de la ligne correspondante de différentes manières. Si vous souhaitez renvoyer l’en-tête, utilisez la formule générique suivante :
=LET( clé, formule_xmatch_multiples_colonnes, INDEX(B1:D,1,XMATCH(1,clé,1)) )
Si vous souhaitez renvoyer la valeur juste en dessous de la valeur correspondante, utilisez la formule générique suivante :
=LET( clé, formule_xmatch_multiples_colonnes, INDEX(B1:D,sortn(clé)+1,XMATCH(1,clé,1)) )
Pour obtenir la valeur juste au-dessus de la valeur correspondante, remplacez +1 par -1.
Dans les deux formules, remplacez formule_xmatch_multiples_colonnes par la formule sans le wrapper SORTN().
Conclusion
Vous pouvez utiliser XLOOKUP pour effectuer une correspondance approximative de la clé de recherche. Cependant, si vous modifiez la formule, il est important de prendre conscience des implications.
Par exemple, vous pouvez utiliser une correspondance approximative pour renvoyer la position relative de la clé de recherche dans toutes les colonnes. Pour cela, vous devez supprimer le wrapper SORTN() de la formule. Cependant, il est important d’éviter d’utiliser une correspondance approximative lorsque vous souhaitez renvoyer une valeur après une correspondance, car cela pourrait entraîner des résultats inattendus.
