Zelladresse eines Werts in einer Tabelle ermitteln

Der höchste Umsatz eines Jahres ist schnell mit MAX gefunden. Aber in welcher Zelle steht er, also in welchem Monat und welcher Filiale? Excel kann die Adresse ermitteln.

Die Kurzlösung (Microsoft 365)

Die Tabelle steht in B2:E5, der gesuchte Wert in H1. Diese Formel listet alle Zellen auf, in denen der Wert vorkommt:

=ZUSPALTE(WENN(B2:E5=H1;ADRESSE(ZEILE(B2:E5);SPALTE(B2:E5);4);NV());2)

Nur die erste Fundstelle:

=INDEX(ZUSPALTE(WENN(B2:E5=H1;ADRESSE(ZEILE(B2:E5);SPALTE(B2:E5);4);NV());2);1)

Für den größten Wert setzen Sie statt H1 einfach MAX(B2:E5) ein.

So funktioniert es

WENN prüft jede Zelle der Tabelle. Wo der Wert passt, erzeugt ADRESSE die Adresse dieser Zelle, sonst entsteht der Fehler #NV. ZUSPALTE stellt alle Ergebnisse in eine Spalte und lässt mit dem Argument 2 die Fehler weg. Übrig bleiben die Adressen der Fundstellen, zeilenweise von oben links nach unten rechts. Die 4 in ADRESSE erzeugt eine Adresse ohne Dollarzeichen, etwa C3.

Tabelle B2:E5
5839
42116
7131017
1221514

Für den Wert 2 liefert die Formel C3 und C5, für 17 die Zelle E4.

Klassisch: nur für eindeutige Werte

In älteren Excel-Versionen findet man oft diese Formel:

=ADRESSE(SUMMENPRODUKT((B2:E5=H1)*ZEILE(B2:E5));SUMMENPRODUKT((B2:E5=H1)*SPALTE(B2:E5)))

Sie addiert Zeilen- und Spaltennummer aller Fundstellen. Das stimmt nur, wenn der Wert genau einmal vorkommt. Im Beispiel liefert sie für den größten Wert 17 richtig E4, für den kleinsten Wert 2 aber F8: Die Zeilen 3 und 5 ergeben zusammen 8, die Spalten 3 und 3 ergeben 6, also Spalte F. Die Adresse liegt sogar außerhalb der Tabelle.

Prüfen Sie deshalb vorher mit =ZÄHLENWENN(B2:E5;H1), wie oft der Wert vorkommt.

Zeile und Spalte statt Adresse

Oft braucht man nicht die Adresse, sondern die Überschriften dazu, also Monat und Filiale. Dann ist es einfacher, Zeilen- und Spaltenüberschrift direkt zu holen. Stehen die Monate in A2:A5 und die Filialen in B1:E1:

=ZUSPALTE(WENN(B2:E5=H1;A2:A5&" / "&B1:E1;NV());2)

Typische Fehler

  • #ÜBERLAUF!: Unter der Formel ist nicht genug Platz für alle Fundstellen. Bereich darunter freihalten.
  • Kein Ergebnis bei Dezimalzahlen: Ein berechneter Wert wie 0,1+0,2 ist intern nicht exakt 0,3. Runden Sie Tabelle und Suchwert gleich, etwa mit RUNDEN.
  • Adresse ohne Blattname: ADRESSE kann den Blattnamen ergänzen: ADRESSE(zeile;spalte;4;;"Tabelle1").

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.