Formule générique
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Sommaire
Recherches sensibles à la casse dans Excel
Par défaut, les recherches standard dans Excel ne sont pas sensibles à la casse. RECHERCHEV et INDEX / MATCH renverront simplement la première correspondance, en ignorant la casse.
Un moyen direct de contourner cette limitation consiste à utiliser une formule matricielle basée sur INDEX / MATCH avec EXACT. Cependant, si vous recherchez uniquement des valeurs numériques, SUMPRODUCT + EXACT offre également un moyen intéressant et flexible de faire une recherche sensible à la casse.
Dans l'exemple, nous utilisons la formule suivante
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Bien que cette formule soit une formule matricielle, il n'est pas nécessaire de la saisir avec Ctrl + Maj + Entrée, car SUMPRODUCT gère les tableaux de manière native.
Explication
SUMPRODUCT est conçu pour fonctionner avec des tableaux, qu'il multiplie, puis additionne.
Dans ce cas, nous sommes deux tableaux avec SUMPRODUCT: B3: B8 et C3: C8. L'astuce consiste à exécuter un test sur les valeurs de la colonne B, puis à convertir les valeurs TRUE / FALSE résultantes en 1 et 0. Nous exécutons le test avec EXACT comme ceci:
EXACT(E3,B3:B8)
Qui produit ce tableau:
(FAUX; FAUX; VRAI; FAUX; FAUX; FAUX)
Notez que la vraie valeur en position 3 est notre correspondance. Ensuite, nous utilisons le double négatif (c'est-à-dire -, qui est techniquement un "double unaire") pour contraindre ces valeurs VRAI / FAUX à 1 et 0. Le résultat est ce tableau:
(0; 0; 1; 0; 0; 0)
À ce stade du calcul, la formule SOMMEPROD ressemble à ceci:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multiplie ensuite simplement les éléments de chaque tableau ensemble pour produire un tableau final:
(0; 0; 775; 0; 0; 0)
Quel SUMPRODUCT additionne ensuite et renvoie 775.
Donc, l'essentiel de cette formule est que les valeurs FALSE sont utilisées pour annuler toutes les autres valeurs. Les seules valeurs qui survivent sont celles qui étaient VRAIES.
Notez que parce que nous utilisons SUMPRODUCT, cette formule a une particularité unique: s'il y a plusieurs correspondances, SUMPRODUCT renverra la somme de ces correspondances. Cela peut ou non être ce que vous voulez, alors faites attention si vous vous attendez à plusieurs matchs!
N'oubliez pas que cette formule ne fonctionne que pour les valeurs numériques, car SUMPRODUCT ne gère pas le texte. Si vous souhaitez récupérer du texte, utilisez INDEX / MATCH + EXACT.
