Über die VERGLEICH-Funktion habe ich ja schon allerhand geschrieben. Mit ihr lässt sich die Position eines gesuchten Wertes in einer Liste ermitteln, und in Kombination mit der INDEX-Funktion steckt sie jeden SVERWEIS locker in die Tasche (siehe hier, hier und hier).
Ein etwas weniger bekanntes Feature sorgt sogar für noch mehr Flexibilität: Man kann unter bestimmten Voraussetzungen nämlich auch Joker-Zeichen bei der Suche verwenden.
Und so geht’s:
VERGLEICH: Ein kurzer Überblick
Für alle, die diese Funktion nicht so gut kennen kommt hier kurz die generelle Anwendung.
=VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])
Wie die ersten beiden Parameter schon andeuten, wird eine Tabelle – die Suchmatrix – nach einem Suchkriterium durchstöbert. Wird dieses Kriterium gefunden, so liefert die Funktion die relative Position der betreffenden Zelle innerhalb der Tabelle.
Mit dem optionalen Parameter „Vergleichstyp“ kann man festlegen, auf welche Weise die Tabelle durchsucht werden soll. Dabei sind folgende Werte als Vergleichstyp zulässig:
1 (oder nicht angegeben): sucht nach dem größten Wert, der kleiner oder gleich dem Suchkritierum ist. Wichtig: Die Tabelle muss dazu aufsteigend sortiert sein.
-1: sucht nach dem kleinsten Wert, der größer oder gleich dem Suchkritierum ist. Hier muss die Liste unbedingt absteigend sortiert sein.
0: findet nur Werte mit exakter Übereinstimmung. Die Sortierung der Tabelle ist dabei egal. Dieser Parameter ist vermutlich der am häufigsten genutzte.
Ein paar Beispiele machen die Unterschiede deutlicher (die Beispieldatei kannst du dir hier herunterladen):
Die Wirkung der verschiedenen Vergleichstypen
Die Suche mit Jokern
Interessant wird es beim Vergleichstyp 0, der ja für die exakte Suche steht. Und genau hier kann man die bekannten Jokerzeichen verwenden:
* steht für eine beliebige Anzahl von beliebigen Zeichen
? steht für genau 1 beliebiges Zeichen (?? für 2 Zeichen, ??? für 3 usw.)
Und damit sind eben auch solche Suchen möglich:
Die VERGLEICH-Funktion mit Jokerzeichen
Was macht man aber, wenn man explizit nach dem Stern oder dem Fragezeichen suchen möchte?
In diesem Fall muss vor das Jokerzeichen eine Tilde ~ gestellt werden. Damit wird der Joker maskiert, also seiner Funktion beraubt und stattdessen wird genau nach diesem Zeichen gesucht:
Maskierte Jokerzeichen
Wie du siehts, lässt sich mit diesem kleinen Trick deutlich mehr Flexibilität aus der VERGLEICH-Funktion herauskitzeln.
Wofür setzt du die VERGLEICH-Funktion ein?
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Sehe ich dass richtig, dass man bei der Suche B*t nur Bukarest findet, weil das vor Budapest steht und man keinen Hinweis bekommt, dass noch eine weitere Lösung existiert?
Hallo J.D.,
das ist richtig, diese Lösung liefert immer genau den ersten Treffer. Um alles angezeigt zu bekommen, wäre eine andere Herangehensweise notwendig (das ist vielleicht mal etwas für einen Folgeartikel).
Schöne Grüße,
Martin
super Funktion.
p.S. in der Beispieldatei muss die bedingte Formatierung noch geändert werden statt -7 muss es darin -5 heißen
. . . (=ZEILE(N6)-7=VERGLEICH(N$3;N$6:N$32;0))
da immer das Feld 2 Zeilen unter dem Suchbegriff markiert wird.
Hallo Peter,
danke für den Hinweis, die Beispieldatei ist jetzt korrigiert.
Schöne Grüße,
Martin
Ich finde diese Themen ziemlich spannend, weil ich selber die Funktionen INDEX, ?VERWEIS und VERGLEICH kaum benutzt habe. Darum habe ich mir letztens auch Dein e-Book „Sverweis & Co.“ gekauft.
Diese Newsletter-Themen passen da richtig gut rein, als Beispiele für die Nutzung der o.g. Funktionen und auch als Funktionserweiterung.
Ich schlage vor, die drei erwähnten Artikel „Ein echtes Dreamteam: INDEX und VERGLEICH“, „Das Dreamteam aufgebohrt: INDEX +VERGLEICH mit mehreren Kriterien“, Teil 1 und 2, mit in Dein e-Book aufzunehmen. Dann kostet’s eben 17,95 EUR statt 14,95 EUR. Wasmeiinst Du dazu?
Mit freundlichen Grüßen
Jörg Böhmichen
Hallo Jörg,
vielleicht gibt es mal eine Neuauflage oder eine Fortsetzung, dann werde ich diese Themen bestimmt aufnehmen.
Schöne Grüße,
Martin