Hoe een waarde opzoeken en meerdere bijbehorende waarden retourneren in Excel?

Deze tutorial gaat over het opzoeken van een waarde en het retourneren van meerdere bijbehorende waarden in Excel, zoals te zien is in de onderstaande schermafbeeldingen:

Waarde opzoeken en meerdere bijbehorende waarden retourneren met een arrayformule

Waarde opzoeken en meerdere bijbehorende waarden retourneren met Filter

Waarde opzoeken en meerdere bijbehorende waarden retourneren met een gedefinieerde functie

Waarde opzoeken en meerdere bijbehorende waarden retourneren met een arrayformule

Hier is een lange arrayformule die kan helpen bij het opzoeken van een waarde en het retourneren van meerdere bijbehorende waarden.

1. Voer de waarde die u wilt opzoeken in een lege cel in. Zie screenshot:

2. Typ in de aangrenzende cel deze formule =ALS(ISFOUT(INDEX($A$1:$B$7,KLEINSTE(ALS($A$1:$A$7=$D$4,RIJ($A$1:$A$7)),RIJ(1:1)),2)),'',
INDEX($A$1:$B$7,KLEINSTE(ALS($A$1:$A$7=$D$4,RIJ($A$1:$A$7)),RIJ(1:1)),2)) in de cel, en druk op Shift + Ctrl + Enter toetsen tegelijkertijd, sleep dan de Auto Vul naar beneden om de cellen te vullen totdat de eerste lege cel verschijnt. Zie screenshot:

Opmerking: In de bovenstaande formule staat $A$1:$B$7 voor het gegevensbereik, $A$1:$A$7 staat voor het kolombereik waarin u de bepaalde waarde zoekt, $D$4 geeft de cel aan waarin u de zoekwaarde typt in stap 1, 2 geeft aan dat de bijbehorende waarden in de tweede kolom worden gezocht.

Waarde opzoeken en meerdere bijbehorende waarden retourneren met Filter

In Excel kunt u ook de filterfunctie gebruiken om dit probleem op te lossen.

1. Selecteer het kolombereik waarin u de waarde wilt opzoeken, en klik op Gegevens > Filter. Zie screenshot:

2. Klik vervolgens op de pijlknop in de eerste cel van het geselecteerde bereik en vink de waarde aan die u wilt opzoeken in de keuzelijst. Zie screenshot:

3. Klik op OK, nu ziet u alleen de opzoekwaarde en de bijbehorende waarden gefilterd.

Waarde opzoeken en meerdere bijbehorende waarden retourneren met een gedefinieerde functie

Als u geïnteresseerd bent in gedefinieerde functies, kunt u het probleem ook met een gedefinieerde functie oplossen.

1. Druk op Alt + F11 toetsen om het Microsoft Visual Basic For Applications-venster te openen.

2. Klik op Module > Invoegen om een modulevenster in te voegen, en kopieer de onderstaande VBA-code in het venster.

VBA: Waarde opzoeken en meerdere bijbehorende waarden retourneren.

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. Sluit het venster, en typ deze formule in een cel =MyVlookup(A10,$A$2:$B$7,2) ( A10 staat voor de zoekwaarde, $A$2:$B$7 staat voor het gegevensbereik, 2 staat voor het kolomindexnummer). Druk op Shift + Ctrl + Enter toetsen. Sleep daarna de vulgreep naar beneden over de cellen, plaats de cursor in de Formulebalk, en druk opnieuw op Shift + Ctrl + Enter.

Tip: Als u de waarden in horizontale cellen wilt retourneren, kunt u deze formule typen =MyVlookup(A10, $A$2:$B$7, 2, "h").