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").
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen