Laden Sie die Beispielarbeitsmappe herunter
Groß-/Kleinschreibung beachten SVERWEIS - Excel
In diesem Tutorial wird gezeigt, wie Sie in Excel mit zwei verschiedenen Methoden ein SVERWEIS mit Berücksichtigung der Groß-/Kleinschreibung durchführen.
Methode 1 - Groß-/Kleinschreibung beachten SVERWEIS mit Hilfsspalte
=SVERWEIS(MAX(GENAU(E2,$B$2:$B$7)*(ZEILE($B$2:$B$7))),$C$2:$D$7,2,0)
SVERWEIS-Funktion
Die SVERWEIS-Funktion wird verwendet, um eine ungefähre oder genaue Übereinstimmung für einen Wert in der äußersten linken Spalte eines Bereichs zu suchen und gibt den entsprechenden Wert aus einer anderen Spalte zurück. Standardmäßig funktioniert SVERWEIS nur für Werte, bei denen die Groß-/Kleinschreibung nicht beachtet wird:
Groß-/Kleinschreibung beachten SVERWEIS
Durch die Kombination von SVERWEIS, EXACT, MAX und ROW können wir eine SVERWEIS-Formel erstellen, bei der die Groß-/Kleinschreibung beachtet wird, die den entsprechenden Wert für unser SVERWEIS mit Berücksichtigung der Groß-/Kleinschreibung zurückgibt. Gehen wir ein Beispiel durch.
Wir haben eine Liste von Artikeln und ihren entsprechenden Preisen (beachten Sie, dass bei der Artikel-ID die Groß-/Kleinschreibung beachtet wird):
Angenommen, wir werden aufgefordert, den Preis für einen Artikel anhand seiner Artikel-ID wie folgt abzurufen:
Um dies zu erreichen, müssen wir zunächst eine Hilfsspalte mit ROW erstellen:
=ROW() klicken und ziehen (oder doppelklicken), um alle Zeilen im Bereich vorzufüllen
Als nächstes kombinieren Sie SVERWEIS, MAX, EXACT und ROW in einer Formel wie folgt:
=SVERWEIS(MAX(GENAU(,)*(ZEILE())), ,,0)
=SVERWEIS(MAX(GENAU(E2,$B$2:$B$7)*(ZEILE($B$2:$B$7))),$C$2:$D$7,2,0)
Wie funktioniert die Formel?
- Die EXACT-Funktion vergleicht die Item-ID in E2 (Suchwert) mit den Werten in B2:B7 (Suchbereich) und gibt ein Array von TRUE zurück, wenn es eine genaue Übereinstimmung oder FLASEs in einem Array gibt {FLASE, FLASE, FLASE, FLASE, FLASE, WAHR}.
- Dieses Array wird dann mit dem ROW-Array {2, 3, 4, 5, 6, 7} multipliziert (beachten Sie, dass dies unserer Hilfsspalte entspricht).
- Die MAX-Funktion gibt den Maximalwert aus dem resultierenden Array {0,0,0,0,0,7} zurück, der in unserem Beispiel 7 ist.
- Dann verwenden wir das Ergebnis als unseren Nachschlagewert in einem SVERWEIS und wählen unsere Hilfsspalte als Nachschlagebereich. In unserem Beispiel gibt die Formel den übereinstimmenden Wert von 16,00 USD zurück.
Methode 2 - Groß-/Kleinschreibung beachten SVERWEIS mit „virtueller“ Hilfsspalte
=SVERWEIS(MAX(GENAU(D2,$B$2:$B$7)*(ZEILE($B$2:$B$7))),AUSWÄHLEN({1,2},REIHE($B$2:$B$7) , $C$2:$C$7),2,0)
Diese Methode verwendet dieselbe Logik wie die erste Methode, macht jedoch das Erstellen einer Hilfsspalte überflüssig und verwendet stattdessen CHOOSE und ROW, um eine „virtuelle“ Hilfsspalte wie folgt zu erstellen:
=SVERWEIS(MAX(GENAU(,)*(ZEILE())), WÄHLEN({1,2}, ZEILE(), ), ,0)
=SVERWEIS(MAX(GENAU(D2,$B$2:$B$7)*(ZEILE($B$2:$B$7))),AUSWÄHLEN({1,2},REIHE($B$2:$B$7) ,$C$2:$C$7),2,0)
Wie funktioniert die Formel?
- Der erste Teil der Formel funktioniert genauso wie die erste Methode.
- Die Kombination von CHOOSE und ROW gibt ein Array mit zwei Spalten zurück, eine für die Zeilennummer und eine andere für den Preis. Das Array wird durch ein Semikolon getrennt, um die nächste Zeile darzustellen, und ein Komma für die nächste Spalte wie folgt: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Wir können dann das Ergebnis aus dem ersten Teil der Formel in einem SVERWEIS verwenden, um den entsprechenden Wert aus unserem CHOOSE- und ROW-Array zu finden.
Groß-/Kleinschreibung beachten SVERWEIS in Google Tabellen
Alle oben genannten Beispiele funktionieren in Google Sheets genauso wie in Excel.
