Recherches multicritères avec INDEX-MATCH

Une seule colonne ne permet pas toujours d’identifier une ligne de manière unique. Vous pouvez avoir besoin du prix d’un produit dans une taille précise, ou du salaire d’un employé dans un service particulier.

Il faut alors utiliser une recherche multicritère : effectuer une correspondance sur deux colonnes ou plus à la fois afin de déterminer exactement une ligne.

INDEX-MATCH gère cela élégamment en combinant les conditions dans un seul test de correspondance, sans nécessiter de colonnes auxiliaires supplémentaires.

Le modèle mental le plus simple consiste à réunir vos colonnes de clés en une seule. Ajoutez une colonne auxiliaire qui assemble le produit et la taille, puis effectuez une recherche ordinaire dans cette colonne.

Par exemple, une cellule auxiliaire peut contenir =A2&"|"&B2, ce qui produit « Shirt|Large ». Vous recherchez ensuite « Shirt|Large » avec MATCH dans cette colonne combinée.

Cette méthode fonctionne, mais elle encombre votre feuille. Les sections suivantes montrent comment vous passer entièrement de cette colonne auxiliaire.

L’astuce principale consiste à multiplier les deux tests de conditions dans MATCH.

(A2:A10=G1) produit un tableau de TRUE/FALSE pour le premier critère. (B2:B10=G2) fait de même pour le second. Leur multiplication, (A2:A10=G1)*(B2:B10=G2), produit 1 uniquement lorsque les deux conditions sont vraies, et 0 ailleurs.

MATCH recherche ensuite la valeur 1 pour trouver la ligne qui satisfait les deux conditions.

Dans les feuilles de calcul, TRUE se comporte comme 1 et FALSE comme 0. La multiplication de ces valeurs reproduit une logique AND :

• 1 multiplié par 1 = 1 (les deux conditions sont satisfaites)
• 1 multiplié par 0 = 0
• 0 multiplié par 1 = 0
• 0 multiplié par 0 = 0

Ainsi, seules les lignes qui satisfont les deux critères produisent un 1. Toutes les autres lignes deviennent 0. Ce 1 unique indique la ligne recherchée.

Entourez maintenant le tableau multiplié de MATCH, en recherchant la valeur exacte 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) renvoie la position de la première ligne où les deux conditions sont vraies.

Si la combinaison correspondante se trouve à la quatrième ligne de données, MATCH renvoie 4. C’est cette position dont INDEX a besoin pour récupérer le résultat.

Transmettez le résultat de MATCH à INDEX appliqué à la colonne souhaitée, par exemple le prix dans C2:C10.

La formule complète signifie : dans C2:C10, renvoyer la valeur de la ligne où le produit est égal à G1 et la taille est égale à G2.

Il s’agit d’une véritable recherche multicritère, sans colonne auxiliaire ni réorganisation de vos données.

Cette formule évalue des tableaux de conditions. Dans les versions modernes d’Excel et dans Google Sheets, il vous suffit d’appuyer sur Entrée pour qu’elle fonctionne.

Dans les versions anciennes d’Excel (avant les tableaux dynamiques), vous devez la valider comme formule matricielle avec Ctrl+Shift+Enter, ce qui ajoute des accolades. Si le résultat est incorrect ou affiche une erreur dans une ancienne version d’Excel, cette validation est généralement l’étape manquante.

Vous avez besoin de trois critères ? Il suffit d’ajouter un autre test par multiplication. Supposons que vous souhaitiez également faire correspondre une couleur de la colonne D à la valeur saisie dans G3.

Chaque facteur supplémentaire (range=criterion) restreint davantage le résultat. Seules les lignes où toutes les conditions sont vraies conservent un produit égal à 1 ; la moindre valeur FALSE transforme le produit entier en 0.

Ce modèle peut être étendu à autant de colonnes que nécessaire.

Données : A = produit, B = taille, C = prix. Vous souhaitez connaître le prix d’un « Shirt » en taille « Large ».

• G1 = « Shirt », G2 = « Large ».
• Les tableaux de conditions produisent un 1 uniquement sur la ligne Shirt+Large, par exemple la ligne 4.
• MATCH(1, ..., 0) renvoie 4.
• INDEX(C2:C10, 4) renvoie le prix de cette ligne.

Modifiez l’une ou l’autre des entrées : la formule retrouve instantanément la bonne ligne.

Gardez les points suivants à l’esprit :

• Plages de même taille : chaque plage de conditions et la colonne d’INDEX doivent avoir la même hauteur.
• Aucune correspondance : si aucune ligne ne satisfait tous les critères, MATCH renvoie #N/A. Entourez toute la formule de IFERROR.
• Doublons : si plusieurs lignes correspondent, MATCH ne renvoie que la première. Rendez vos critères suffisamment précis pour obtenir une correspondance unique.

Si plusieurs lignes peuvent correspondre et que vous préférez totaliser leurs valeurs plutôt que d’en récupérer une seule, SUMPRODUCT constitue une solution de remplacement claire à INDEX-MATCH saisi comme formule matricielle.

Cette fonction multiplie les tableaux de conditions par la colonne de valeurs, puis additionne les résultats : seules les lignes qui satisfont les deux critères contribuent au total. Il n’est pas nécessaire d’utiliser Ctrl+Shift+Enter, car SUMPRODUCT gère nativement les tableaux.

Utilisez INDEX-MATCH pour récupérer une seule valeur correspondante ; utilisez SUMPRODUCT pour agréger toutes les correspondances.

Testez vos connaissances sur les recherches multicritères.

Pour les recherches multicritères avec INDEX-MATCH :

• Multipliez les tableaux de conditions entre eux : (A=G1)*(B=G2) donne 1 uniquement lorsque toutes les conditions sont remplies (un AND logique).
• MATCH(1, ..., 0) trouve la position de la ligne correspondante.
• INDEX(returnCol, position) renvoie la valeur.

Ajoutez d’autres facteurs *(range=criterion) pour des conditions supplémentaires, veillez à ce que les plages aient la même hauteur, validez avec Ctrl+Shift+Enter dans les anciennes versions d’Excel et protégez la formule avec IFERROR.