Hoe zoekt of vindt u waarden in een andere werkmap?

Het configureren van opzoekverwijzingen met VLOOKUP kan verwarrend zijn, vooral als u het pad, de naam of het werkblad van het brondocument vaak wijzigt. In dergelijke scenario’s kan het automatiseren van het opzoekproces met VBA een gestroomlijndere oplossing zijn, waarmee u waarden kunt opzoeken zelfs als de brondocumentmap gesloten is en het selecteren van bereiken en het retourneren van gegevens geautomatiseerd wordt.

Volg deze stappen om VBA te gebruiken voor opzoeking tussen documentmappen:

1. Druk tegelijkertijd op Alt + F11 om het venster van de Microsoft Visual Basic for Applications-editor te openen.

2.Klik in de VBA-editor op Invoegen>Module, kopieer vervolgens de volgende code en plak deze in het modulevenster:

VBA: Vlookup-gegevens en Retourneerwaarde uit een andere gesloten documentmap

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Belangrijke details:

• De code retourneert de overeenkomende waarde in een kolom die 2 kolommen rechts ligt van het opzoekbereik. Als u bijvoorbeeld kolom B selecteert, verschijnen de resultaten in kolom D.
• Als u het resultaat in een andere kolom wilt weergeven, wijzig dan het getal 2 in xRg.Offset(0,2).Formulain een andere waarde (bijv.)1 voor de volgende kolom, 3 voor de derde kolom naar rechts).
• Kies de juiste documentmap en het juiste werkblad wanneer u daarom wordt gevraagd; de code gebruikt altijd het eerste werkblad in het geselecteerde bestand. Pas de code aan als uw brondocument niet het eerste werkblad is.
• Sla uw bestand altijd op voordat u onbekende macro’s uitvoert. Macro’s kunnen na uitvoering niet ongedaan worden gemaakt.

3. Voer de macro uit door op de toets F5 te drukken of op de knop Uitvoeren te klikken. Er verschijnt een dialoogvenster met de titel „Kutools voor Excel” waarin u wordt gevraagd het celbereik te selecteren waarvan u de waarden wilt opzoeken.

4.Nadat u uw bereik hebt geselecteerd, klikt u op OK. Kort daarna verschijnt er nog een dialoogvenster waarin u wordt gevraagd de brondocumentmap te kiezen (zelfs als deze gesloten is). Blader naar het juiste bestand, selecteer het en klik op Openen om te bevestigen.

Wanneer de macro is voltooid, worden de overeenkomende waarden uit de brondocumentmap geretourneerd naar de doelkolom in uw Huidig werkblad. Als sommige waarden ontbreken, controleer dan of Zoekwaardebereik in de Huidig werkblad exact overeenkomen met die in de Brongegevens (hoofdletters en voorloopspaties/Volgende spaties zijn van belang voor een exacte overeenkomst).

Voordelen: Werkt met gesloten documentmappen, vermijdt hardcoded bestandspaden in formules en biedt flexibiliteit bij het dynamisch selecteren van brondocumenten.
Overwegingen: Macro’s moeten zijn ingeschakeld; VBA werkt mogelijk niet op beveiligde werkbladen of met niet-Excel-bestanden. Sla uw documentmap op in een macro-ondersteunend formaat (*.xlsm) als u deze methode regelmatig wilt gebruiken.

Als u fouten tegenkomt, controleer dan of u geen typefouten heeft gemaakt in werkblad- of bestandsnamen, of dat het geselecteerde bereik geschikt is en het bestandspad toegankelijk is. Overweeg voor foutopsporing de code regel voor regel door te nemen met de VBA-editor.

Naast de klassieke VLOOKUP-aanpak en VBA bestaan er alternatieve methoden voor opzoeking tussen documentmappen in Excel. Deze kunnen in bepaalde situaties zelfs beter werken—bijvoorbeeld bij een afwijkende gegevensstructuur, wanneer u formules verkiest boven macro’s, of als u meer flexibiliteit nodig heeft, zoals zoeken naar links of op basis van meerdere criteria.

INDEX- en MATCH-functies gebruiken tussen documentmappen

Met de combinatie van INDEX en MATCH kunt u waarden in elke richting opzoeken—links, rechts, boven of onder—in een andere documentmap. Dit is vooral handig wanneer de kolom waaruit u gegevens wilt ophalen, niet rechts van de opzoekkolom staat (een beperking van VLOOKUP).

Scenario: Stel dat u een prijs wilt ophalen uit een andere geopende documentmap, waarbij de vruchtnaam mogelijk niet in de eerste kolom staat.

1. Selecteer in uw doeldocumentmap de cel waar u het resultaat wilt weergeven (bijvoorbeeld C2) en voer de onderstaande formule in (vervang indien nodig documentmap, werkblad en bereik):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Druk op Enter. Kopieer de formule vervolgens naar andere rijen of vul deze in door de vulgreep te slepen.

Uitleg van parameters:

• [Price.xlsx]Sheet1!$B$1:$B$24: Het bereik waarin de prijzen zijn opgeslagen.
• B2: De vruchtnaam om op te zoeken.
• [Price.xlsx]Sheet1!$A$1:$A$24: Het bereik waarin uw opzoekwaarde wordt gezocht.
• De 0 aan het einde zorgt voor een exacte overeenkomst.

Sterke punten: Werkt bij opzoeken naar links én rechts; ideaal voor flexibele lay-outs.
Tips: Verplaats of hernoem brondocumenten niet zonder uw formule bij te werken.

XLOOKUP gebruiken voor opzoeking tussen documentmappen (Excel 365 en nieuwer)

Als u Excel 365 of Excel 2021 gebruikt, is de nieuwe XLOOKUP-functie nog flexibeler. Hiermee kunt u eenvoudig exacte overeenkomsten vinden, opzoeking naar links ondersteunen en automatisch omgaan met ontbrekende waarden zonder foutmeldingen.

Gebruik als volgt:
Voer in de cel waar u het resultaat wilt weergeven het volgende in:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Druk op Enter en kopieer de formule indien nodig. „Niet gevonden” kunt u vervangen door elke aangepaste tekst die u wilt weergeven wanneer een opzoeking mislukt.

Voordelen: Flexibeler dan VLOOKUP en eenvoudiger te beheren; voorkomt veelvoorkomende fouten die vaak optreden bij oudere formules. XLOOKUP is echter alleen beschikbaar in nieuwere Excel-versies.

Overweeg voor complexe vereisten zoals meerdere zoekcriteria, zoeken in samengevoegde werkbladen of het vermijden van prestatieproblemen in zeer grote bestanden om Brongegevens in gestructureerde tabellen te organiseren of Excel Power Query te gebruiken om efficiënt Gegevens Koppelen tussen meerdere documentmappen uit te voeren.