Generische Formel
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Zusammenfassung
Groß- und Kleinschreibung in Excel
Standardmäßig wird bei Standard-Lookups in Excel nicht zwischen Groß- und Kleinschreibung unterschieden. Sowohl VLOOKUP als auch INDEX / MATCH geben einfach die erste Übereinstimmung zurück und ignorieren den Fall.
Eine direkte Möglichkeit, diese Einschränkung zu umgehen, besteht darin, eine Array-Formel zu verwenden, die auf INDEX / MATCH mit EXACT basiert. Wenn Sie jedoch nur nach numerischen Werten suchen, bietet SUMPRODUCT + EXACT auch eine interessante und flexible Möglichkeit, eine Suche nach Groß- und Kleinschreibung durchzuführen.
Im Beispiel verwenden wir die folgende Formel
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Obwohl diese Formel eine Arrayformel ist, muss sie nicht mit Strg + Umschalt + Eingabetaste eingegeben werden, da SUMPRODUCT Arrays nativ behandelt.
Erläuterung
SUMPRODUCT wurde für die Arbeit mit Arrays entwickelt, die multipliziert und dann summiert werden.
In diesem Fall handelt es sich um zwei Arrays mit SUMPRODUCT: B3: B8 und C3: C8. Der Trick besteht darin, einen Test für die Werte in Spalte B durchzuführen und dann die resultierenden TRUE / FALSE-Werte in Einsen und Nullen umzuwandeln. Wir führen den Test mit EXACT wie folgt durch:
EXACT(E3,B3:B8)
Welches erzeugt dieses Array:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Beachten Sie, dass der wahre Wert in Position 3 unsere Übereinstimmung ist. Dann verwenden wir das doppelte Negativ (dh -, was technisch gesehen ein "doppeltes Unär" ist), um diese TRUE / FALSE-Werte in 1 und 0 zu zwingen. Das Ergebnis ist dieses Array:
(0; 0; 1; 0; 0; 0)
Zu diesem Zeitpunkt der Berechnung sieht die SUMPRODUCT-Formel folgendermaßen aus:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multipliziert dann einfach die Elemente in jedem Array, um ein endgültiges Array zu erstellen:
(0; 0; 775; 0; 0; 0)
Welches SUMPRODUCT summiert sich dann und gibt 775 zurück.
Der Kern dieser Formel ist also, dass die FALSE-Werte verwendet werden, um alle anderen Werte aufzuheben. Die einzigen Werte, die überleben, sind diejenigen, die WAHR waren.
Da wir SUMPRODUCT verwenden, weist diese Formel eine einzigartige Wendung auf: Wenn mehrere Übereinstimmungen vorhanden sind, gibt SUMPRODUCT die Summe dieser Übereinstimmungen zurück. Dies kann oder kann nicht sein, was Sie wollen, also seien Sie vorsichtig, wenn Sie mehrere Übereinstimmungen erwarten!
Denken Sie daran, dass diese Formel nur für numerische Werte funktioniert, da SUMPRODUCT keinen Text verarbeitet. Wenn Sie Text abrufen möchten, verwenden Sie INDEX / MATCH + EXACT.
