Wie kann man in Excel nach einem Wert suchen und mehrere entsprechende Werte zurückgeben?
Dieses Tutorial behandelt das Suchen eines Wertes und das Zurückgeben mehrerer entsprechender Werte in Excel, wie in den folgenden Screenshots gezeigt:
Suchwert mit Array-Formel zurückgeben und mehrere entsprechende Werte anzeigen
Suchwert mit Filter zurückgeben und mehrere entsprechende Werte anzeigen
Suchwert mit definierter Funktion zurückgeben und mehrere entsprechende Werte anzeigen
Suchwert mit Array-Formel zurückgeben und mehrere entsprechende Werte anzeigen
Hier ist eine lange Array-Formel, die beim Suchen eines Wertes und dem Zurückgeben mehrerer entsprechender Werte helfen kann.
1. Geben Sie den Wert, nach dem Sie suchen möchten, in eine leere Zelle ein. Siehe Screenshot:
2. Geben Sie in der angrenzenden Zelle diese Formel ein: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)) und drücken Sie Shift + Ctrl + Enter Tasten gleichzeitig, dann ziehen Sie den Auto-Ausfüllen nach unten, um die Zellen auszufüllen, 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 steht für den Spaltenbereich, in dem Sie den bestimmten Wert suchen, $D$4 gibt die Zelle an, in die Sie den Suchwert im Schritt 1 eingeben, und 2 steht für das Finden der entsprechenden Werte in der zweiten Spalte.
Suchwert mit Filter zurückgeben und mehrere entsprechende Werte anzeigen
In Excel können Sie auch die Filterfunktion verwenden, um dieses Problem zu 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 dann auf die Pfeiltaste in der ersten Zelle des ausgewählten Bereichs und aktivieren Sie den Wert, den Sie nur in der Dropdown-Liste suchen möchten. Siehe Screenshot:
3. Klicken Sie auf OK, jetzt sehen Sie nur noch den Suchwert und seine entsprechenden Werte gefiltert.
Suchwert mit definierter Funktion zurückgeben und mehrere entsprechende Werte anzeigen
Wenn Sie sich für definierte Funktionen interessieren, können Sie das Problem auch mit einer definierten Funktion lösen.
1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic For Applications zu öffnen.
2. Klicken Sie auf Modul > Einfügen, um einModulfenster einzufügen, und kopieren Sie das folgende VBA in das Fenster.
VBA: Suchwert zurückgeben und mehrere entsprechende Werte anzeigen.
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 das Fenster und geben Sie diese Formel in eine Zelle ein =MyVlookup(A10,$A$2:$B$7,2) (A10 steht für den Suchwert, $A$2:$B$7 steht für den Datenbereich, 2 steht für die Spaltenindexnummer). Drücken Sie Shift + Ctrl + Enter. Ziehen Sie dann den Ausfüllkästchen nach unten, setzen Sie den Cursor in die Formelleiste und drücken Sie erneut Shift + Ctrl + Enter.
Tipp: Wenn Sie die Werte in horizontalen Zellen zurückgeben möchten, können Sie diese Formel eingeben =MyVlookup(A10, $A$2:$B$7, 2, "h").
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins