Recherche croisée avec INDEX et XMATCH dans Excel

Liens rapides

• Comment fonctionnent les fonctions INDEX et XMATCH dans Excel
• Utiliser INDEX avec XMATCH pour des recherches unidimensionnelles
• Utiliser INDEX avec XMATCH pour des recherches bidimensionnelles

Pour de nombreux utilisateurs, la combinaison INDEX + XMATCH est la méthode de référence pour extraire une valeur depuis un jeu de données. Mais ce duo peut aussi effectuer des recherches à deux critères (recherche croisée), en retournant la valeur à l’intersection d’une ligne et d’une colonne spécifiées.

Cet article s’adresse à tous les niveaux d’utilisateurs Excel : on commence par les fondamentaux, puis on monte en complexité. Si vous ne connaissez pas INDEX et XMATCH, lisez depuis le début. Si vous maîtrisez déjà les recherches unidimensionnelles, rendez-vous directement à la section “Utiliser INDEX avec XMATCH pour des recherches bidimensionnelles”.

Comment fonctionnent INDEX et XMATCH

Avant de voir comment assembler ces deux fonctions dans une seule formule, explorons leur fonctionnement séparément.

La fonction INDEX

La fonction INDEX renvoie la valeur d’une plage selon un numéro de ligne et un numéro de colonne que vous spécifiez. Syntaxe :

=INDEX(a,b,c)

où

• a est la plage ou la table,
• b est le numéro de ligne,
• c est le numéro de colonne.

Par exemple :

=INDEX(T_Profit,3,6)

dans la cellule H2 renvoie la valeur qui se situe à l’intersection de la 3e ligne et de la 6e colonne de la table T_Profit.

Important : au lieu d’encoder en dur les numéros de ligne et de colonne, référencez des cellules qui contiennent ces numéros pour rendre la formule dynamique :

=INDEX(T_Profit,I2,I3)

Définition rapide : INDEX = extraire la valeur à la position (ligne, colonne) d’une plage.

La fonction XMATCH

XMATCH recherche un élément dans une plage et retourne sa position relative (1 = premier élément).

Syntaxe :

=XMATCH(a,b,c,d)

où

• a est l’élément recherché,
• b est la plage à parcourir,
• c est le type de correspondance (0 = correspondance exacte par défaut; -1 = exact ou plus petit; 1 = exact ou plus grand; 2 = correspondance via jokers),
• d est le mode de recherche (1 = premier→dernier par défaut, -1 = dernier→premier, 2 = recherche binaire pour b triée croissant, -2 = recherche binaire pour b triée décroissant).

XMATCH est disponible dans Excel 2021 et versions ultérieures (y compris Microsoft 365), ainsi que dans Excel pour le web et les applications mobiles.

Exemple :

=XMATCH(1927,T_Profit[Employee])

renvoie 3 si la valeur 1927 est le 3e élément de la colonne Employee de la table T_Profit.

Définition rapide : XMATCH = trouver la position d’une valeur dans une plage.

Comparaison courte : MATCH est l’ancêtre de XMATCH. XMATCH a des arguments par défaut qui favorisent la correspondance exacte, permet les jokers et la recherche inversée — plus pratique dans la plupart des scénarios.

Utiliser INDEX avec XMATCH pour des recherches unidimensionnelles

Associer INDEX et XMATCH rend les recherches plus robustes qu’avec VLOOKUP, surtout lorsque les colonnes peuvent changer d’ordre.

Scénario : récupérer le profit total généré par un employé dont l’ID est saisi en I2.

Formule à placer dans la cellule cible :

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

Explication :

• T_Profit est la table où se trouve la valeur,
• XMATCH(I2,T_Profit[Employee]) identifie la ligne correspondant à l’ID saisi en I2,
• 6 indique la 6e colonne de la table (colonne du profit total).

Astuce : cliquez l’en-tête de colonne dans une table structurée pour insérer automatiquement une référence structurée (p.ex. T_Profit[Profit2021]).

Important : vous n’avez pas besoin d’indiquer c et d pour XMATCH si vous voulez une correspondance exacte en lecture du haut vers le bas — ce sont les paramètres par défaut.

Utiliser INDEX avec XMATCH pour des recherches bidimensionnelles

La vraie puissance apparaît quand XMATCH calcule la ligne ET la colonne. Vous pouvez ainsi rechercher la valeur à l’intersection d’un employé (ligne) et d’une année ou d’un indicateur (colonne).

Scénario : trouver le profit réalisé par l’employé 1191 en 2021. Saisissez l’ID en I2 et l’en-tête de colonne (ex. 2021) en I3.

Formule :

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

Explication :

• Le premier XMATCH calcule le numéro de ligne pour l’ID,
• Le second XMATCH recherche la position de l’en-tête de colonne dans les en-têtes de la table (#Headers), et INDEX renvoie la valeur à l’intersection.

Erreur courante : #N/A dû au type de format

Si vos en-têtes de colonne ressemblent à des nombres (ex. des années) mais que vous obtenez #N/A, c’est souvent un problème de format : Excel stocke parfois les en-têtes de table sous forme de texte.

Solution :

1. Sélectionnez la cellule contenant la valeur de recherche (I3 dans l’exemple).
2. Dans l’onglet Accueil du ruban, ouvrez la liste des formats de nombre et choisissez “Texte”.
3. Entrez en mode édition (F2) sur la cellule I3, puis validez (Entrée) pour forcer le format.

Après cette étape, la formule devrait renvoyer la valeur attendue.

Tapez différents paramètres dans I2 et I3 pour voir la formule retourner d’autres résultats.

Bonnes pratiques et optimisation

• Préférez les tables structurées (Insérer > Tableau) : références stables, gestion automatique des lignes et des en-têtes.
• Nommez vos plages si vous les réutilisez souvent (Formules > Définir un nom).
• Pour des correspondances partielles, utilisez XMATCH avec le type 2 (jokers). Ex :

=XMATCH("*Smith*",T_Profit[Employee],2)

• Pour des recherches sensibles aux espaces, nettoyez les données avec TRIM() et SUPPRIMER.CHAINE ou NETTOYER.
• Si vous travaillez avec des dates, assurez-vous que les formats régionaux sont cohérents (jj/mm/aaaa vs mm/jj/aaaa selon la langue Excel).

Important : XMATCH n’est pas disponible dans Excel 2016 et versions antérieures. Voir alternatives ci‑dessous.

Alternatives et cas d’échec

• Si XMATCH n’est pas disponible : utilisez MATCH au lieu de XMATCH, puis INDEX(MATCH(…),MATCH(…)). MATCH ne gère pas les jokers et sa valeur par défaut peut être approximative si vous n’indiquez pas 0 pour une correspondance exacte.

Exemple équivalent avec MATCH :

=INDEX(T_Profit,MATCH(I2,T_Profit[Employee],0),MATCH(I3,T_Profit[#Headers],0))

• XLOOKUP (RECHERCHEX en français) peut remplacer certaines utilisations mais est centré sur les recherches unidimensionnelles. Pour une recherche croisée, INDEX + XMATCH reste la méthode la plus claire.
• VLOOKUP/HLOOKUP (RECHERCHEV/RECHERCHEH) sont plus fragiles : colonne de recherche fixe, sensibilité au déplacement des colonnes, et lenteur sur très grands jeux.
• FILTER (FILTRER) et LET combinés peuvent offrir des solutions dynamiques avec les matrices dynamiques, mais leur complexité augmente pour des recherches simples.

Contre‑exemple : ne faites pas dépendre des positions de colonnes fixes dans un modèle qui évolue. Si vous ajoutez ou réordonnez des colonnes, RECHERCHEV risque de se casser; INDEX+XMATCH reste stable.

Méthodologie rapide (mini‑méthode)

1. Convertir la zone source en tableau structuré (Ctrl+T).
2. Nommer la table (p.ex. T_Profit).
3. Décider des cellules d’entrée (p.ex. I2 = ID, I3 = Année).
4. Écrire la formule INDEX avec deux XMATCH pour ligne et colonne.
5. Tester différents paramètres et valider les erreurs #N/A.
6. Ajouter validation des données et menus déroulants pour fiabiliser l’entrée.

Mise en place d’une validation des données pour les recherches croisées

Créer des listes déroulantes pour I2 et I3 accélère la recherche et évite les erreurs de saisie. Attention : Excel ne permet pas d’utiliser directement les en-têtes d’une table comme source d’une liste de validation. Solutions :

• Référencer directement les cellules (p.ex. =T_Profit[[#Headers],[2021]] n’est pas forcément accepté). Au lieu de cela, sélectionnez la plage d’en-têtes hors de la zone de validation.
• Définir un nom pour la plage d’en-têtes (Formules > Définir un nom) et utiliser ce nom comme Source.

Checklist de mise en production

• Les données source sont dans un tableau structuré.
• Les en-têtes ont le bon format (texte vs nombre vs date).
• Les formules INDEX+XMATCH sont testées avec valeurs présentes, absentes et partielles.
• Validation des données pour les cellules d’entrée.
• Noms de plages documentés.
• Sauvegarde/contrôle de version du classeur.

Scénarios de test et critères d’acceptation

• Test 1 : valeur existante — la formule renvoie la valeur attendue.
• Test 2 : valeur inexistante — la formule renvoie #N/A; l’utilisateur reçoit un message clair via SIERREUR.
• Test 3 : changement d’ordre des colonnes — la formule continue de fonctionner.
• Test 4 : entrée via menu déroulant — la formule renvoie la valeur attendue et ne produit pas d’erreur de saisie.

Exemple avec gestion d’erreur :

=SIERREUR(INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers])),"Non trouvé")

Modèles et snippets utiles

• Formule basique :

=INDEX(Table,XMATCH(valeur_ligne,Table[ColonneCle],0),XMATCH(valeur_col,Table[#Headers],0))

• Avec message d’erreur lisible :

=SIERREUR(INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers])),"Valeur introuvable")

• Pour correspondance insensible à la casse (utiliser une colonne auxiliaire normalisée) :

=INDEX(T_Profit,XMATCH(MINUSCULE(I2),MINUSCULE(T_Profit[Employee]),0),XMATCH(I3,T_Profit[#Headers],0))

(Remarque : MINUSCULE dans un XMATCH matriciel peut nécessiter Ctrl+Maj+Entrée dans les anciennes versions d’Excel; les versions modernes gèrent les matrices dynamiques automatiquement.)

Arbre de décision pour choisir une méthode de recherche

flowchart TD
  A[Besoin de recherche] --> B{Recherche unidimensionnelle ou croisée ?}
  B -->|Unidimensionnelle| C[RECHERCHEX 'XLOOKUP' ou XMATCH+INDEX]
  B -->|Croisée| D[INDEX + XMATCH]
  C --> E{XMATCH disponible ?}
  E -->|Oui| F[Préférer XMATCH ou RECHERCHEX]
  E -->|Non| G[Utiliser MATCH ou RECHERCHEV selon le cas]
  D --> H{En-têtes numériques ?}
  H -->|Oui| I[Vérifier format: convertir en Texte si nécessaire]
  H -->|Non| J[Déployer formule et validation]

Compatibilité et migration

• XMATCH et RECHERCHEX requièrent Excel 2021, Microsoft 365 ou Excel pour le web. Si vous partagez avec des utilisateurs sur Excel 2016, préférez MATCH + INDEX.
• Lors de la migration, documentez les formules modernes et fournissez une version de secours utilisant MATCH pour la rétrocompatibilité.

Risques et mitigations

• Risque : entrée d’en-tête dans un format différent → Mitigation : standardiser le format (Texte) et ajouter validation.
• Risque : ajout/suppression de colonnes → Mitigation : utiliser des références structurées et éviter les références par numéro de colonne codées en dur.
• Risque : erreurs #N/A non informatives → Mitigation : encapsuler avec SIERREUR et messages personnalisés.

Exemples avancés et variantes

• Recherche avec valeur partielle (joker) :

=INDEX(T_Profit,XMATCH("*Smith*",T_Profit[Employee],2),XMATCH("2021",T_Profit[#Headers],0))

• Recherche croisée sur plusieurs critères (approche par ajout d’une colonne clé) :

1. Créer une colonne calculée dans la table : Cle = [Employee] & “|” & [Region]
2. Utiliser XMATCH sur cette clé combinée pour retrouver la ligne.

• Utiliser LET pour clarifier la formule :

=LET(
  id,I2,
  col,I3,
  r,XMATCH(id,T_Profit[Employee]),
  c,XMATCH(col,T_Profit[#Headers]),
  INDEX(T_Profit,r,c)
)

LET permet de nommer les sous-expressions pour une lecture et un débogage plus simples.

Notes sur la localisation

• Dans la version française d’Excel, les séparateurs d’arguments peuvent être des points-virgules (;) selon les paramètres régionaux. Exemple :

=INDEX(T_Profit;XMATCH(I2;T_Profit[Employee]);XMATCH(I3;T_Profit[#Headers]))

• Les noms des rubans sont traduits : “Home” → “Accueil”, et le format de nombre “Text” → “Texte”.

Important : adaptez les séparateurs et les noms de fonctions si vous partagez le classeur entre utilisateurs de différentes langues.

Résumé

INDEX + XMATCH offre une méthode fiable et flexible pour effectuer des recherches à une ou deux dimensions dans Excel. Elle s’adapte aux modifications de structure de la table, fonctionne bien avec des menus déroulants et supporte les correspondances exactes, partielles et la recherche inversée. Si XMATCH n’est pas disponible, MATCH reste une alternative avec quelques limitations.

Points clés :

• Utilisez des tables structurées et des plages nommées.
• XMATCH retourne une position ; INDEX extrait la valeur à cette position.
• Traitez les formats d’en-têtes (texte vs nombre) pour éviter les #N/A.
• Encapsulez avec SIERREUR pour de meilleurs messages utilisateurs.

Fiche rapide Microsoft 365 :

Microsoft 365 Personnel inclut l’accès aux applications Office comme Word, Excel et PowerPoint sur plusieurs appareils, 1 To de stockage OneDrive, et plus. Vérifiez la disponibilité de XMATCH selon votre abonnement et version d’Excel.

Fin de l’article.