Tutorials

Excel-Tutorial: Zelladresse eines Wertes in einem Bereich finden und ausgeben

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel die Zelladresse eines bestimmten Wertes innerhalb eines Bereichs ermittelst.

In Excel gibt es keine einzelne Funktion, die direkt „Suche Wert X und gib mir die Adresse A1 aus“ erledigt. Wir kombinieren daher zwei bis drei Funktionen: ADRESSE, VERGLEICH (oder XVERGLEICH) und manchmal ZEILE oder SPALTE.

Methode 1: Suche in einer Spalte (Der Klassiker)

Wenn du einen Wert in einer einzelnen Spalte suchst und die genaue Zelladresse (z. B. $A$5) erhalten möchtest.

Die Formel:

=ADRESSE(VERGLEICH(Suchkriterium; Suchmatrix; 0); SPALTE(Suchmatrix))

Schritt-für-Schritt-Erklärung:

  1. VERGLEICH(Suchkriterium; Suchmatrix; 0): Sucht den Wert und gibt die relative Position innerhalb der Matrix zurück (z. B. "er ist an 5. Stelle").
  2. SPALTE(Suchmatrix): Gibt die Nummer der Spalte zurück, in der gesucht wird.
  3. ADRESSE(Zeile; Spalte): Wandelt die Zeilen- und Spaltennummer in eine lesbare Zelladresse um.

Wichtig: Wenn deine Suchmatrix nicht in Zeile 1 beginnt, musst du den Versatz korrigieren: =ADRESSE(VERGLEICH("DeinWert"; A5:A20; 0) + ZEILE(A5) - 1; SPALTE(A5))

Methode 2: Suche in einer 2D-Matrix (Bereich über mehrere Spalten)

Dies ist die mächtigste Methode, falls du einen Wert in einem großen Bereich (z. B. A1:D10) suchst.

Die Formel:

=ADRESSE(SUMMENPRODUKT((Bereich=Suchwert)*ZEILE(Bereich)); SUMMENPRODUKT((Bereich=Suchwert)*SPALTE(Bereich)))

So funktioniert es:

  • (Bereich=Suchwert): Erstellt eine Matrix aus WAHR/FALSCH-Werten.
  • *`ZEILE(Bereich)`**: Wandelt WAHR in die jeweilige Zeilennummer um und FALSCH in 0.
  • SUMMENPRODUKT(...): Addiert diese Werte auf. Da der Wert (hoffentlich) nur einmal vorkommt, bleibt nur die Zeilennummer des Treffers übrig.
  • Das gleiche Prinzip wird für die SPALTE angewendet.

Methode 3: Die moderne Variante (mit XVERGLEICH)

Wenn du Microsoft 365 oder Excel 2021 nutzt, ist XVERGLEICH einfacher und sicherer.

Die Formel (für eine Spalte):

=ZELLE("adresse"; INDEX(A1:A100; XVERGLEICH("Suchwert"; A1:A100)))

Warum diese Methode gut ist:

  • XVERGLEICH: Findet die Position schneller und standardmäßig mit einer genauen Entsprechung.
  • INDEX: Greift direkt auf die Zelle zu.
  • ZELLE("adresse"; ...): Extrahiert die physische Adresse aus dem INDEX-Ergebnis.

Praxis-Beispiel: Zusammenfassung

Angenommen, du hast eine Liste mit Namen in A2:A10 und suchst die Adresse des Namens "Max".

Name Formel-Ergebnis
Max (in A5) =ADRESSE(VERGLEICH("Max"; A1:A10; 0); 1)
Ergebnis $A$5

Tipps für Profis:

  • Relative vs. Absolute Adresse: Die ADRESSE-Funktion hat einen dritten Parameter. =ADRESSE(Zeile; Spalte; 4) gibt die Adresse ohne $-Zeichen aus (z. B. A5 statt $A$5).
  • Blattname hinzufügen: Wenn du den Tabellennamen in der Adresse brauchst, nutze: =ZELLE("adresse"; INDEX(Bereich; ...)) – dies gibt oft den kompletten Pfad inklusive Tabellenblatt zurück.
  • Fehler abfangen: Umschließe deine Formel mit =WENNFEHLER(Formel; "Nicht gefunden"), falls der Wert nicht im Bereich existiert.

Welche Methode soll ich wählen?

  • Nimm Methode 1, wenn du Anfänger bist und nur in einer Liste suchst.
  • Nimm Methode 2, wenn dein Wert irgendwo in einer großen Tabelle stehen könnte.
  • Nimm Methode 3, wenn du die modernste Excel-Version hast.
0