Name: Tremplin Numérique
Price range: $$$

Pour beaucoup, la combinaison index-xmatch dans Excel est la méthode incontournable pour récupérer une valeur à partir d'un ensemble de données. Cependant, vous pouvez également utiliser ce duo dynamique pour effectuer des recherches bidirectionnelles, renvoyant une valeur à l'intersection d'une ligne et d'une colonne spécifiées.

Cet article s'adresse aux lecteurs de tous les niveaux d'expertise Excel, augmentant en complexité au fur et à mesure de sa progression. Si vous n'êtes pas familier avec les fonctions Index et XMatch, continuez à lire. Cependant, si vous savez déjà comment utiliser l'index et XMatch pour les recherches à sens unique, sautez directement à la section Utilisation d'index avec xmatch pour les recherches bidirectionnelles.

Sommaire

Comment fonctionnent les fonctions index et xmatch d'Excel

Avant de voir comment ces deux fonctions peuvent être utilisées ensemble dans une formule, prenons un moment pour explorer comment ils fonctionnent isolément.

La fonction d'index

La fonction d'index renvoie une valeur dans une plage en fonction du numéro de ligne et du numéro de colonne que vous spécifiez. La syntaxe est la suivante:

=INDEX(a,b,c)

où

un est la gamme,
b est le numéro de ligne, et
c est le numéro de colonne.

Par exemple, taper:

=INDEX(T_Profit,3,6)

dans la cellule H2 renvoie la valeur dans la cellule qui se trouve à l'intersection de la deuxième ligne et de la troisième colonne de la table T_Profit.

De plus, au lieu de codage en dur les nombres de lignes et de colonnes, vous pouvez référencer les cellules les contenant, ce qui rend la formule plus flexible. Ici, tapant:

=INDEX(T_Profit,I2,I3)

Dans la cellule I5, tire le numéro de ligne de la cellule I2 et le numéro de colonne de la cellule I3.

La fonction XMatch

La fonction XMatch recherche un élément dans une plage et renvoie sa position.

Si vous travaillez dans l'application Excel Desktop sur un PC ou un Mac, vous devez utiliser Excel 2021 ou version ultérieure (y compris Excel pour Microsoft 365) pour utiliser la fonction XMatch. Il est également facilement disponible dans Excel pour le Web et sur la tablette Excel et les applications mobiles.

Voici la syntaxe:

=XMATCH(a,b,c,d)

où

un est l'article à rechercher,
b est la plage de recherche,
c est le type de correspondance (0 = correspondance exacte (par défaut); -1 = correspondance exacte ou le plus petit élément le plus petit; 1 = correspondance exacte ou le plus grand élément le plus grand; 2 = une correspondance générique), et
d est le mode de recherche (1 = d'abord pour durer (par défaut), -1 = dernier au premier, 2 = recherche binaire où b est dans l'ordre croissant, -2 = recherche binaire où b est en ordre décroissant).

Vous connaissez peut-être la fonction de match, qui est le prédécesseur de la fonction XMatch plus moderne. Ils fonctionnent de manière similaire, bien que les arguments par défaut dans la syntaxe XMatch soient plus intuitifs que ceux de la syntaxe de correspondance, favorisant une correspondance exacte par rapport à une correspondance approximative. En outre, XMatch vous permet de rechercher dans les deux sens et d'utiliser des caractères génériques pour des matchs partiels, dont vous ne pouvez pas faire avec Match.

Dans cet exemple, taper:

=XMATCH(1927,T_Profit(Employee))

dans la cellule H2 retourne 3, car le numéro d'identification de l'employé 1927 est la troisième valeur de la colonne des employés de la table T_Profit.

Remarquez comment les arguments c et d ne sont pas requis dans ce scénario, car nous voulons une correspondance exacte dans une recherche qui s'exécute du haut de la table vers le bas, et ce sont les paramètres par défaut de cette fonction.

De même, l'argument un Peut être une référence à une cellule contenant la valeur de recherche, ce qui signifie que vous pouvez facilement modifier la valeur de recherche sans modifier la formule:

=XMATCH(I2,T_Profit(Employee))

où la cellule I2 contient l'ID de l'employé à consulter dans la colonne Player de la table T_Score.

Utilisation d'index avec XMatch pour les recherches à sens unique

Bien que les fonctions Index et XMatch puissent être utiles par elles-mêmes, leur véritable potentiel est réalisé lorsqu'il est utilisé ensemble. La clé pour comprendre comment la combinaison index-xmatch peut être utilisée pour effectuer des recherches bidirectionnelles est d'abord de comprendre comment elle fonctionne dans des situations unidimensionnelles.

Disons que vous souhaitez voir le bénéfice total généré par un employé lorsque vous tapez leur identifiant dans la cellule I2.

Pour ce faire, dans la cellule I2, type:

=INDEX(T_Profit,XMATCH(I2,T_Profit(Employee)),6)

où

T_profit est le nom du tableau où la valeur sera trouvée,
XMatch (I2, T_Profit (employé)) indique à la fonction d'index quelle ligne de la colonne des employés à regarder, en fonction de la valeur de la cellule I2, et
6 Indique la fonction d'index pour renvoyer la valeur dans la sixième colonne de cette ligne.

Dans ce scénario, vous n'avez pas besoin de saisir les arguments de type de correspondance ou de mode de recherche pour la partie XMatch de la formule, car les paramètres par défaut renvoient une correspondance exacte et l'aspect de haut en bas.

Mais que se passe-t-il si vous souhaitez retourner la valeur d'une autre colonne, comme le profit d'un employé au cours d'une année donnée? C'est là que les recherches bidirectionnelles sont utiles.

Utilisation d'index avec xmatch pour les recherches bidirectionnelles

L'avantage de l'utilisation d'index avec XMatch pour les recherches bidirectionnelles est que vous pouvez modifier les paramètres de votre recherche sans modifier la formule. En effet, XMatch identifie à la fois le numéro de ligne et le numéro de colonne, ce qui signifie que vous n'avez pas à les cocoder dans la formule.

Supposons que vous souhaitiez rapidement savoir combien d'employés à profit 1191 gagnés en 2021.

Pour ce faire, dans la cellule I4, type:

=INDEX(T_Profit,XMATCH(I2,T_Profit(Employee)),XMATCH(I3,T_Profit(#Headers)))

où

T_profit est le nom du tableau où la valeur sera trouvée,
XMatch (I2, T_Profit (employé)) indique à la fonction d'index quelle ligne de la colonne des employés à regarder, en fonction de la valeur de la cellule I2, et
Xmatch (i3, t_profit (#heders)) indique à la fonction d'index quelle colonne de la table T_Profit à regarder, en fonction de la valeur dans la cellule i3.

Si vos en-têtes de colonne contiennent des valeurs numériques, comme les dates, vous pouvez voir une erreur # n / a lorsque vous appuyez sur Entrée.

En effet, Excel stocke en fait des en-têtes de colonne sous forme de texte, même s'ils semblent numériques. Ainsi, pour vous assurer d'obtenir une correspondance comme pour la valeur de recherche et le tableau de recherche, sélectionnez la cellule contenant la valeur de recherche correspondante (qui, dans cet exemple, est la cellule i3), et dans l'onglet Accueil sur le ruban, cliquez sur « Texte » dans le menu déroulant Format Number.

Ensuite, sélectionnez la cellule contenant la valeur de recherche de colonne (I3), appuyez sur F2 pour activer le mode d'édition de cellule et appuyez sur Entrée. Maintenant, Excel voit à la fois la valeur de recherche et le tableau de recherche comme ayant le même format de numéro, de sorte que la formule renvoie correctement la valeur attendue.

Maintenant, tapez différents paramètres de recherche dans les cellules I2 et i3, et voyez la formule renvoyer le résultat correspondant.

Maintenant que vous savez comment utiliser l'index et le XMatch pour effectuer des recherches bidirectionnelles, vous pouvez aller plus loin et utiliser la validation des données pour créer des menus déroulants dans les cellules contenant les valeurs de recherche, accélérant le processus de recherche et vous assurer de ne pas accidentellement saisir une valeur invalide.