Thor fragt sich, ob es eine Möglichkeit gibt, eine Suche durchzuführen, ohne eine Spalte oder Zeile angeben zu müssen und das Ergebnis die Adresse der Zelle zu sein, in der sich der Wert befindet. Zum Beispiel möchte er einen Wert nachschlagen (z. B. 345 oder „Mein Text“) und die Funktion alle Zellen in allen Arbeitsblättern in der Arbeitsmappe durchsuchen lassen und die vollständige Adresse der Zelle zurückgeben, in der der Wert gefunden wurde.

Der von Ihnen verwendete Ansatz wird durch den Bereich bestimmt, den Sie durchsuchen möchten.

Wenn Sie in demselben Arbeitsblatt suchen möchten, in dem die Antwort angezeigt werden soll, können Sie eine Formel wie die folgende verwenden:

=ADDRESS(MAX(ROW(1:5)(A1:E5="my text")), MAX(COLUMN(A1:E1)(A1:E5="my text")),4)

Dies sollte als Array-Formel eingegeben werden (drücken Sie Strg + Umschalt + Eingabetaste) und es wird nur im Bereich A1: E5 gesucht. Falls gewünscht, können Sie den Bereich ändern, indem Sie die Formel entsprechend anpassen.

Ein größerer Suchbereich wäre das Betrachten eines gesamten Arbeitsblatts. Dies kann weiterhin mit einer Array-Formel wie der folgenden erfolgen:

=ADDRESS(MAX(ROW(Sheet1!1:65000)(IF(Sheet1!1:65000=$A$1,1,0))), MAX(COLUMN(Sheet1!$1:$65000)IF(Sheet1!1:65000=$A$1,1,0)))

Die Formel geht davon aus, dass das Gesuchte in Zelle A1 gespeichert ist.

Sie sollten die Bezeichnung Sheet1 in den Namen des Arbeitsblatts ändern, das durchsucht werden soll.

Wenn Sie einen größeren Bereich durchsuchen möchten, z. B. alle Arbeitsblätter in einer Arbeitsmappe, verwenden Sie am besten ein Makro, das die Suchfunktion in Excel aufruft.

Function FindAddr(vValue As Variant)

Dim wks As Worksheet     Dim rCell As Range     Dim bFound As Boolean

bFound = False     For Each wks In ActiveWorkbook.Worksheets         With wks             Set rCell = .Cells.Find _               (What:=vValue, After:=.Cells(1), _               LookIn:=xlValues, LookAt:=xlWhole, _               SearchOrder:=xlByRows, _               SearchDirection:=xlNext, _               MatchCase:=False)

If Not rCell Is Nothing Then                 bFound = True                 Exit For             End If         End With     Next     If bFound Then         FindAddr = wks.Name & "!" & _           rCell.Address(False, False)

Else         FindAddr = "Not Found"

End If     Set wks = Nothing     Set rCell = Nothing End Function

Diese Funktion kann von einem anderen Makro aufgerufen werden, das alles übergibt, wonach im Parameter vValue gesucht werden soll. Die Funktion gibt entweder die vollständige Adresse (einschließlich des Arbeitsblattnamens) der ersten Übereinstimmung zurück oder „Nicht gefunden“, wenn keine Übereinstimmung vorliegt.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (11524) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Mit einer Funktion nach einem Wert suchen.