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").