Google Sheets-Kochbuch: Position eines Suchergebnisses ermitteln

Ich unterscheide hier zwei Fälle:

1. suchen in einer Spalte oder Zeile nach der Position eines Wertes
2. suchen in einer Matrix (Array) nach der Zelle bzw. den Zellen mit dem jeweiligen Inhalt

Suchen in einer Spalte oder Zeile nach der Position eines Wertes

• =MATCH(Suchwert;Bereich;0)
  • Das ist eine recht einfache Aufgabe. Wir suchen im Bereich nach der relativen Position des Suchwertes, wobei die Liste nicht sortiert sein muss.
  • Sie erhalten nicht die absolute Zeilenadresse, sondern die relative Position in der Liste.
  • Dabei funktioniert MATCH mit Zeilen und mit Spalten.

Suchen in einer Matrix nach der Position eines Wertes

Nehmen wir die linke Tabelle als Eingabedaten:

A1:C3: Eingabebereich 1 2 3 4 5 6 7 8 9

Hilfsliste 1 $A$1 2 $B$1 3 $C$1 4 $A$2 5 $B$2 6 $C$2 7 $A$3 8 $B$3 9 $C$3

Zelladressen $A$1 $B$1 $C$1 $A$2 $B$2 $C$2 $A$3 $B$3 $C$3

• =VLOOKUP(F1;{FLATTEN(A1:C3)\FLATTEN(ARRAYFORMULA(ADDRESS(ROW(A1:C3);COLUMN(A1:C3))))};2;0)
  • Der Suchwert steht in der Zelle F1.
  • Der Suchbereich ist A1:C3.
  • Wir basteln uns eine temporäre Hilfsliste mit zwei Spalten (das Array mit den geschweiften Klammern).
    • FLATTEN(A1:C3) erzeugt die linke Spalte, es werde einfach alle Werte des Arrays in einer Spalte angeordnet.
    • In der folgenden Spalte (\) erzeugt die ARRAYFORMULA die Zelladressen.
    • FLATTEN sorgt dafür, dass das Array, das von ARRAYFORMULA erzeugt wird, auch in eine Spalte umgewandelt wird.
  • Mit dem VLOOKUP suchen wir in der Hilfsliste nach dem Wert, und lassen uns die zweite Spalte zurückgeben (in der die Adresse steht).

Suchen in einer Matrix nach allen Vorkommen eines Wertes

Wenn der Wert mehrmals vorkommen kann und wir alle Vorkommen suchen wollen, verwenden wir am besten QUERY.

• =QUERY({FLATTEN({A1:C3})\FLATTEN(ARRAYFORMULA(ADDRESS(ROW(A1:C3);COLUMN(A1:C3))))};"SELECT Col2 WHERE Col1="&F1&"";0)
  • Die Hilfsliste, in der gesucht werden soll, erzeugen wir genauso wie im VLOOKUP-Beispiel.
  • Der Suchbefehl ist jetzt etwas anders. Der Query lautet: Wähle in Spalte 2 (Col2) die Zeilen aus, in denen in Spalte 1 (Col1) der Suchwert steht.
  • Der Suchwert ist in der Zelle F1 enthalten. Der Suchwert ist hier ein nummerischer Wert, eine Zeichenkette müssten wir noch in Hochkommata setzen.

• =QUERY({FLATTEN({A1:C3})\FLATTEN(ARRAYFORMULA(ADDRESS(ROW(A1:C3);COLUMN(A1:C3))))};"SELECT Col2 WHERE Col1='"&F1&"'";0)
  • Hier suchen wir nach einer Zeichenkette, die in Zelle F1 steht. Zu den weiteren Suchmöglichkeiten siehe die Dokumentation zu QUERY.

Hervorheben der gefundenen Werte

... tbd ... Hervorheben durch bedingte Formatierung...

Die Beispiele auf dieser Seite wurden getestet: 03/2022