Formule générique
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Sommaire
Pour rechercher des valeurs avec INDEX et MATCH, en utilisant plusieurs critères, vous pouvez utiliser une formule matricielle. Dans l'exemple illustré, la formule en H8 est:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Explication
C'est une formule plus avancée. Pour des informations de base, consultez Comment utiliser INDEX et MATCH.
Normalement, une formule INDEX MATCH est configurée avec MATCH défini pour parcourir une plage d'une colonne et fournir une correspondance basée sur des critères donnés. Sans concaténer les valeurs dans une colonne d'assistance ou dans la formule elle-même, il est impossible de fournir plus d'un critère.
Cette formule contourne cette limitation en utilisant la logique booléenne pour créer un tableau de uns et de zéros pour représenter les lignes correspondant aux 3 critères, puis en utilisant MATCH pour correspondre au premier 1 trouvé. Le tableau temporaire de uns et de zéros est généré avec cet extrait:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Ici, nous comparons l'article en H5 à tous les articles, la taille en H6 à toutes les tailles et la couleur en H7 à toutes les couleurs. Le résultat initial est trois tableaux de résultats TRUE / FALSE comme ceci:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Astuce: utilisez F9 pour voir ces résultats. Sélectionnez simplement une expression dans la barre de formule et appuyez sur F9.
L'opération mathématique (multiplication) transforme les valeurs TRUE FALSE en 1s et 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Après multiplication, nous avons un seul tableau comme celui-ci:
(0;0;1;0;0;0;0)
qui est introduit dans la fonction MATCH en tant que tableau de recherche, avec une valeur de recherche de 1:
MATCH(1,(0;0;1;0;0;0;0))
À ce stade, la formule est une formule INDEX MATCH standard. La fonction MATCH renvoie 3 à INDEX:
=INDEX(E5:E11,3)
et INDEX renvoie un résultat final de 17,00 $.
Visualisation des tableaux
Les tableaux expliqués ci-dessus peuvent être difficiles à visualiser. L'image ci-dessous montre l'idée de base. Les colonnes B, C et D correspondent aux données de l'exemple. La colonne F est créée en multipliant les trois colonnes ensemble. C'est le tableau transmis à MATCH.
Version sans baie
Il est possible d'ajouter un autre INDEX à cette formule, en évitant d'avoir à entrer sous forme de formule matricielle avec Ctrl + Maj + Entrée:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
La fonction INDEX peut gérer les tableaux de manière native, de sorte que le deuxième INDEX est ajouté uniquement pour "attraper" le tableau créé avec l'opération logique booléenne et renvoyer le même tableau à MATCH. Pour ce faire, INDEX est configuré avec zéro ligne et une colonne. L'astuce de ligne zéro oblige INDEX à renvoyer la colonne 1 du tableau (qui est déjà une colonne de toute façon).
Pourquoi voudriez-vous la version sans baie? Parfois, les gens oublient d'entrer une formule matricielle avec Ctrl + Maj + Entrée, et la formule renvoie un résultat incorrect. Ainsi, une formule non matricielle est plus "à l'épreuve des balles". Cependant, le compromis est une formule plus complexe.
Remarque: dans Excel 365, il n'est pas nécessaire de saisir des formules matricielles d'une manière spéciale.
