Wie wendet man eine Zwei-Wege-Suchformel in Excel an?
Die Zwei-Wege-Suche ermöglicht es Ihnen, den Wert an der Schnittstelle einer bestimmten Zeile und Spalte innerhalb einer Tabelle abzurufen. Diese Methode ist besonders wertvoll, wenn Ihr Datensatz eindeutige Zeilenbeschriftungen und Spaltenüberschriften enthält und Sie gezielt einen bestimmten Wert anhand dieser Kriterien ermitteln möchten. Stellen Sie sich beispielsweise vor, Sie verwalten einen Verkaufsbericht, eine Anwesenheitsliste oder eine Budgettabelle und benötigen rasch den Wert, der einem bestimmten Datum und der Kennung eines Mitarbeiters zugeordnet ist. Mit der Zwei-Wege-Suchfunktion von Excel extrahieren Sie diese Informationen effizient und präzise. Der folgende Screenshot zeigt ein typisches Anwendungsszenario: Der Wert an der Schnittstelle der Zeile „AA-3“ und der Spalte „5. Jan.“ wird zurückgegeben.
Zwei-Wege-Suche mithilfe von Formeln
VBA-Makro für die Zwei-Wege-Suche
Zwei-Wege-Suche mithilfe von Formeln
Die Zwei-Wege-Suche in Excel bietet einen unkomplizierten Weg, um den Wert an der Schnittstelle vorgegebener Zeilen- und Spaltenüberschriften zu ermitteln – besonders effizient bei gut strukturierten Tabellen. Sie lässt sich vielseitig einsetzen, etwa um Mitarbeiterdaten nach Datum zu vergleichen, Budgetzahlen nach Region und Monat abzurufen oder Testergebnisse für einen bestimmten Schüler und ein bestimmtes Fach zu finden.
Obwohl Formeln flexibel und praktisch sind, liegt ihre wesentliche Einschränkung darin, dass die Tabellenstruktur unverändert bleiben muss. Für dynamischere oder automatisierte Anforderungen eignen sich oft andere Lösungen besser – weitere Methoden finden Sie im Folgenden.
Gehen Sie wie folgt vor, um eine Zwei-Wege-Suche mithilfe von Formeln durchzuführen:
1. Listen Sie die Spalten- und Zeilenüberschriften auf, nach denen Sie suchen möchten. Klare und konsistente Überschriften verhindern Suchfehler durch überflüssige Leerzeichen oder inkonsistente Formate. Hier sehen Sie ein Beispiel für eine gut strukturierte Tabelle:
2. Geben Sie in der Zelle, in der das Ergebnis erscheinen soll, je nach Layout Ihrer Tabelle eine der folgenden Formeln ein:
Formel 1: Kombination aus INDEX und VERGLEICH
=INDEX(A1:I8,MATCH(L1,A1:A8,0),MATCH(L2,A1:I1,0))Diese Formel ermittelt die Zeilen- und Spaltenindizes, indem sie die angegebenen Überschriften abgleicht, und liefert anschließend den Wert an deren Schnittstelle.
Formel 2: SUMMENPRODUKT für numerische Tabellen
=SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8)SUMMENPRODUKT eignet sich ideal, wenn Ihre Daten ausschließlich aus numerischen Werten bestehen, und liefert bei Textwerten unter Umständen nicht das erwartete Ergebnis.
Formel 3: SVERWEIS mit VERGLEICH
=VLOOKUP(L1,$A$1:$I$8,MATCH(L2,B1:I1,0)+1,FALSE)Bei dieser Methode wird zunächst die Zeile gesucht und anschließend mithilfe der VERGLEICH-Funktion der Spaltenversatz ermittelt.
Tipps：
(1)Parametererklärung:
A1:A8ist der Bereich für Zeilenbeschriftungen,L1ist die spezifische Zeilenbeschriftung, die Sie suchen möchten;A1:I1ist der Bereich für Spaltenüberschriften,L2ist die Ziel-Spaltenüberschrift;A1:I8ist der gesamte Tabellenbereich – passen Sie diese Bezüge bei Bedarf an Ihre Daten an.
(2) Wenn Ihr Suchwertbereich Textwerte enthält und Sie SUMMENPRODUKT verwenden, wird 0 zurückgegeben. In solchen Fällen empfiehlt sich die Kombination aus INDEX/VERGLEICH.
Achten Sie beim Eingeben von Formeln darauf, dass Ihre Überschriftenwerte in L1 (für Zeilen) und L2 (für Spalten) exakt mit denen in Ihrer Tabelle übereinstimmen – gegebenenfalls auch hinsichtlich der Groß-/Kleinschreibung.
3. Drücken Sie die Eingabetaste, um die Formel zu bestätigen. Die ausgewählte Zelle zeigt nun den Wert an der Schnittstelle Ihrer angegebenen Zeilenbeschriftung und Spaltenüberschrift an.
Hinweise und Fehlerbehebung:
- Wenn die Formel einen Fehler wie #NV zurückgibt, prüfen Sie sorgfältig, ob Ihre Überschriften zusätzliche Leerzeichen oder inkonsistente Groß-/Kleinschreibung enthalten.
- Beim Kopieren von Formeln in andere Zellen müssen relative Bezüge gegebenenfalls in absolute Bezüge umgewandelt werden – nutzen Sie hierfür bei Bedarf das $-Symbol.
- Wenn Ihre Tabelle groß oder variabel ist, sollten Sie zur besseren Skalierbarkeit dynamische benannte Bereiche oder alternative Lösungen wie das unten stehende VBA-Makro in Betracht ziehen.
VBA-Makro für die Zwei-Wege-Suche
In Situationen, in denen eine formelbasierte Zwei-Wege-Suche zu restriktiv ist – etwa bei der Notwendigkeit schreibungsunabhängiger Suchen, der Unterstützung dynamischer Bereichsgrößen oder der Automatisierung wiederholter Suchvorgänge – bietet ein benutzerdefiniertes VBA-Makro eine praktikable Lösung. VBA erweist sich besonders als wertvoll für Anwender, die regelmäßig mit sich ändernden Tabellenstrukturen arbeiten oder Suchvorgänge nahtlos in automatisierte Arbeitsabläufe integrieren müssen.
So richten Sie ein VBA-Makro für die Zwei-Wege-Suche in Excel ein und verwenden es:
1. Wechseln Sie zu Entwicklertools > Visual Basic, wodurch der Editor für Microsoft Visual Basic for Applications geöffnet wird. Klicken Sie auf Einfügen > Modul, um ein neues Modul hinzuzufügen, und fügen Sie den folgenden Code in das Modul ein:
Sub TwoWayLookupMacro()
Dim tblRange As Range
Dim rowLabel As String
Dim colLabel As String
Dim rowIdx As Variant
Dim colIdx As Variant
Dim result As Variant
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set tblRange = Application.InputBox("Select the table range for lookup", xTitleId, Type:=8)
rowLabel = Application.InputBox("Enter the row label to find", xTitleId, Type:=2)
colLabel = Application.InputBox("Enter the column header to find", xTitleId, Type:=2)
On Error GoTo 0
rowIdx = Application.Match(LCase(rowLabel), Application.Index(tblRange, 0, 1), 0)
colIdx = Application.Match(LCase(colLabel), Application.Index(tblRange, 1, 0), 0)
If IsError(rowIdx) Or IsError(colIdx) Then
MsgBox "Row or column label not found. Please check your input.", vbExclamation, xTitleId
Exit Sub
End If
result = tblRange.Cells(rowIdx, colIdx).Value
MsgBox "The value at the intersection is: " & result, vbInformation, xTitleId
End Sub2. Um das Makro auszuführen, klicken Sie auf die Schaltfläche 
oder drücken Sie F5. Anschließend werden Sie aufgefordert, Ihren Tabellenbereich auszuwählen sowie die Zeilen- und Spaltenbeschriftungen einzugeben. Das Makro zeigt den Wert an der Schnittstelle in einem Dialogfeld an.
Praktische Tipps:
- Stellen Sie sicher, dass sich Ihre Tabellenüberschriften in der ersten Zeile und der ersten Spalte des ausgewählten Bereichs befinden, um eine genaue Übereinstimmung zu gewährleisten.
- Dieses Makro verwendet eine schreibungsunabhängige Suche, indem es die Eingabe in Kleinbuchstaben umwandelt – so werden häufige Fehler bei der Groß- und Kleinschreibung zuverlässig vermieden.
- Falls Ihr Tabellenlayout abweicht, passen Sie das Makro gegebenenfalls an, um eine korrekte Indizierung sicherzustellen.
- Für anspruchsvollere Anwendungsfälle lässt sich der VBA-Code erweitern, um Batch-Suchen durchzuführen oder Ergebnisse direkt in Excel-Zellen zu schreiben.
Sollten Probleme auftreten, z. B. dass Überschriften nicht gefunden werden, vergewissern Sie sich, dass die Beschriftungen und Datenbereich weder führende/Leerzeichen am Ende noch versteckte Zeichen enthalten.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins