Wie sucht man in Excel nach einem Wert und gibt dabei mehrere zugehörige Werte zurück?
In diesem Tutorial geht es darum, wie Sie in Excel einen Wert suchen und mehrere zugehörige Werte zurückgeben können, wie in den folgenden Screenshots gezeigt:
Wert suchen und mehrere zugehörige Werte mit einer Matrixformel zurückgeben
Wert suchen und mehrere zugehörige Werte mit Filter zurückgeben
Wert suchen und mehrere zugehörige Werte mit einer benutzerdefinierten Funktion zurückgeben
Wert suchen und mehrere zugehörige Werte mit einer Matrixformel zurückgeben
Hier ist eine leistungsstarke Matrixformel, die Ihnen hilft, einen Wert zu suchen und alle zugehörigen Ergebnisse auf einen Schlag zurückzugeben.
1. Geben Sie den gesuchten Wert in eine leere Zelle ein. Siehe Screenshot:
2. Geben Sie in die benachbarte Zelle diese Formel ein:=WENN(ISTFEHLER(INDEX($A$1:$B$7;KKLEINSTE(WENN($A$1:$A$7=$D$4;ZEILE($A$1:$A$7));ZEILE(1:1));2));„";)
INDEX($A$1:$B$7;KKLEINSTE(WENN($A$1:$A$7=$D$4;ZEILE($A$1:$A$7));ZEILE(1:1));2))und drücken Sie gleichzeitig die Tasten Umschalt + Strg + Enter, ziehen Sie dann den AutoAusfüllen--Griff nach unten, bis die erste leere Zelle erscheint. Siehe Screenshot:
Hinweis: In der obigen Formel steht $A$1:$B$7 für den Datenbereich, $A$1:$A$7 für die Spalte, in der Sie den gesuchten Wert finden möchten, $D$4 für die Zelle, in die Sie in Schritt 1 den Suchwert eingegeben haben, und die Zahl 2 gibt an, dass die zugehörigen Werte in der zweiten Spalte des Datenbereichs gesucht werden.
Wert suchen und mehrere zugehörige Werte mit Filter zurückgeben
In Excel können Sie dieses Problem auch ganz einfach mit der Filterfunktion lösen.
1. Wählen Sie den Spaltenbereich aus, in dem Sie den Wert suchen möchten, und klicken Sie auf Daten > Filter. Siehe Screenshot:
2. Klicken Sie anschließend auf die Pfeilschaltfläche in der ersten Zelle Ihres ausgewählten Bereichs und aktivieren Sie im Dropdown-Menü ausschließlich den Wert, nach dem Sie suchen möchten. Siehe Screenshot:
3. Klicken Sie auf OK, um nun nur den Suchwert und seine zugehörigen Werte herauszufiltern.
Wert suchen und mehrere zugehörige Werte mit einer benutzerdefinierten Funktion zurückgeben
Falls Sie sich für benutzerdefinierte Funktionen interessieren, lässt sich das Problem damit ebenfalls lösen.
1. Drücken Sie die Tasten Alt + F11, um das Fenster Microsoft Visual Basic für Applikationen zu öffnen.
2. Klicken Sie auf Modul > Einfügen, um ein Modul-Fenster einzufügen, und kopieren Sie den folgenden VBA-Code in dieses Fenster.
VBA: Einen Wert suchen und mehrere zugehörige Werte zurückgeben.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Schließen Sie dieses Fenster und geben Sie folgende Formel in eine Zelle ein:=MyVlookup(A10;$A$2:$B$7;2) (A10 steht für den Suchwert, $A$2:$B$7 bezeichnet den Datenbereich und 2 gibt die Spaltenindexnummer an). Drücken Sie anschließend Umschalt + Strg + Enter. Ziehen Sie dann den Ausfüllkästchen-Griff nach unten, platzieren Sie den Cursor in der Formel-Leiste und drücken Sie erneut Umschalt + Strg + Enter.
Tipp: Wenn Sie die Werte in horizontalen Zellen zurückgeben möchten, geben Sie einfach diese Formel ein: =MyVlookup(A10, $A$2:$B$7, 2, „h").
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