Zelladresse statt wert finden

In Excel können Sie nach einer Zelle suchen und anstelle des Zellwerts die Zelladresse erhalten. Nehmen wir an, Sie haben den entsprechenden Wert in Zelle B12. Mit der Formel können Sie die Adresse B12 erhalten, nicht ihren Wert.

In diesem Tutorial werden Möglichkeiten untersucht, wie man hierfür eine Formel schreiben kann.

Zellenadresse statt Wert abrufen (CELL + INDEX + MATCH)

Bitte befolgen Sie die folgenden Schritte, um diese Formel zu schreiben:

1. Geben Sie zunächst die CELL-Funktion in eine Zelle ein und geben Sie im ersten Argument „address“ als info_type an.
   
2. Geben Sie anschließend im zweiten Argument die Funktion INDEX ein.
   
3. Verweisen Sie dann im Array-Argument des INDEX auf die Namen, die Sie in Spalte A haben. Der Bereich ist A2:A1001.
   
4. Geben Sie nun im zweiten Argument des INDEX die MATCH-Funktion ein.
   
5. Geben Sie von dort aus in MATCH den Suchwert im Argument „lookup_value“ an und verweisen Sie erneut auf den Bereich A2:A1001 im Argument „lookup_array“. Und verwenden Sie 0 im Argument [match_type], um eine exakte Übereinstimmung durchzuführen.
   
6. Geben Sie am Ende die schließenden Klammern ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

Wenn Sie die Eingabetaste drücken, wird die Zellenadresse des gesuchten Werts zurückgegeben.

Der Name „Alec Wright“ steht in Zelle A5, und das ist auch das Ergebnis.

 =CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0)))

Wie funktioniert diese Formel?

Um diese Formel zu verstehen, müssen Sie sie in drei Teile teilen, da wir drei Funktionen verwendet haben.

Im ersten Teil haben wir die MATCH-Funktion, die den Suchwert des Zellennamens A2:A1001 abgleicht und die Positionsnummer im Ergebnis zurückgibt.

Danach übernimmt INDEX diese Positionsnummer von MACTH und erhält den Bereichswert an der ^4. Position. Das bedeutet Zelle A5.

SECRET : INDEX gibt den Wert der 4. Position oder Zelle A5 des Bereichs im obigen Beispiel zurück. Aber INDEX als Funktion gibt zwei verschiedene Werte zurück. Einer ist der Zellenwert (den Sie oben sehen können) und der zweite ist die Adresse der Zelle, in der der Wert gespeichert ist (A5). Als Benutzer können Sie die Zellenadresse nicht sehen oder abrufen, aber die CELL-Funktion ist intelligent genug, um sie abzurufen und zu verwenden.

Letztendlich gibt die CELL-Funktion die Adresse der Zelle zurück, indem sie im Ergebnis eine absolute Referenz verwendet.

Wenn Sie Dollarzeichen aus der Zellenadresse entfernen möchten, können Sie SUBSTITUTE verwenden.

 =SUBSTITUTE(CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0))),"$","")

Rufen Sie die Zellenadresse mit ADDRESS + MATCH ab

Es gibt eine andere Möglichkeit, eine Formel zu schreiben, um die Zellenadresse zu erhalten.

 =ADDRESS(MATCH(C1,A1:A1001,0),1)

Diese Formel besteht aus zwei Teilen: Im ersten Teil haben wir die MATCH-Funktion, um die Positionsnummer der Zelle zu ermitteln, in der Sie den Suchwert haben.

Danach haben Sie in der ADDRESS-Funktion die von MATCH (im ersten Argument) zurückgegebene Zahl, die das ROW-Argument zum Festlegen der Zeilennummer ist.

Und im zweiten Argument haben wir die Spaltennummer.

Zeile 5 und Spalte 1 erstellen eine Zellenadresse A5.

Empfehlung: ^1. Methode