Hoe een vlookup uitvoeren om twee lijsten in gescheiden werkbladen te vergelijken?
Stel dat je twee werkbladen hebt, elk met een lijst van namen zoals in de bovenstaande schermafbeeldingen gedemonstreerd. Je wilt misschien controleren welke namen uit Namen-1 ook voorkomen in Namen-2. Deze vergelijking handmatig uitvoeren, vooral bij lange lijsten, kan tijdrovend en zeer foutgevoelig zijn. In dit artikel worden verschillende efficiënte methoden geïntroduceerd om je snel en nauwkeurig te helpen bij het vergelijken van de twee lijsten en het vinden van overeenkomstige waarden in verschillende werkbladen.
Vlookup om twee lijsten in aparte werkbladen te vergelijken met formules
Vlookup om twee lijsten in aparte werkbladen te vergelijken met Kutools voor Excel
Voorwaardelijke opmaak met formule tussen werkbladen
VBA-code - Automatisch lijsten vergelijken en overeenkomsten markeren of extraheren
Vlookup om twee lijsten in aparte werkbladen te vergelijken met formules
Een praktische en directe aanpak om lijsten die zich in verschillende Excel-werkbladen bevinden te vergelijken is door gebruik te maken van de functie VLOOKUP. Deze methode helpt je om efficiënt alle namen die in zowel Namen-1 als Namen-2 voorkomen te extraheren of te markeren:
1. Selecteer in het werkblad Namen-1 een cel naast je lijstgegevens (bijvoorbeeld cel B2) en voer de volgende formule in:
=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)Druk vervolgens op Enter. Als de naam in de huidige rij voorkomt in Namen-2, retourneert de formule de naam; zo niet, dan wordt een #N/B-fout weergegeven. Zie het onderstaande voorbeeld:
2. Kopieer de formule naar beneden door het vulhandvat te slepen om elke naam in Namen-1 te vergelijken met alle namen in Namen-2. Overeenkomende items tonen de naam, terwijl die niet gevonden worden een foutwaarde weergeven:
Opmerkingen:
1. Voor meer duidelijkheid kun je deze alternatieve formule gebruiken om "Ja" of "Nee" indicatoren voor overeenkomsten te retourneren:
=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")Deze formule toont "Ja" voor namen die in beide werkbladen voorkomen en "Nee" voor namen die alleen in Namen-1 voorkomen:
2. Wanneer je deze formules gebruikt, vervang A2 met de eerste cel in je lijst, Namen-2 met de naam van het referentiewerkblad, en pas $A$2:$A$19 aan om overeen te komen met het werkelijke gegevensbereik in je werkblad. Onthoud dat bereiken moeten beginnen en eindigen met de juiste rijnummers om ervoor te zorgen dat al je gegevens zijn opgenomen.
3. Tips voor gebruik: Als je #N/B-fouten tegenkomt waar overeenkomsten zouden moeten zijn, controleer dan zorgvuldig op mogelijke problemen veroorzaakt door extra spaties, verschillen in gegevensopmaak (tekst versus getal), of typfouten in je lijsten. Gebruik TRIM of CLEAN in een hulpkolom om gegevens op te schonen indien nodig.
4. Om per ongeluk overschrijving te voorkomen, overweeg dan om je gegevens te backuppen voordat je bulkformules toepast. Daarnaast kun je na de vergelijking Filter gebruiken op je formuleresultaatkolom om snel alle overeenkomsten of unieke items te bekijken.
Vlookup om twee lijsten in gescheiden werkbladen te vergelijken
Als je Kutools voor Excel hebt, met de functie Selecteer dezelfde & verschillende cellen, kun je met enkele klikken dezelfde of verschillende waarden uit twee afzonderlijke werkbladen vinden en markeren. Deze functie vermindert aanzienlijk het risico op handmatige fouten en bespaart veel tijd, vooral bij grote datasets. Klik hier om Kutools voor Excel te downloaden!
Kutools voor Excel: met meer dan 300 handige Excel-add-ins, gratis te proberen zonder beperkingen gedurende 30 dagen. Download en probeer nu gratis!
Vlookup om twee lijsten in aparte werkbladen te vergelijken met Kutools voor Excel
Als je Kutools voor Excel hebt, kan de functie Selecteer dezelfde & verschillende cellen je helpen om snel twee lijsten uit verschillende werkbladen te vergelijken en gemeenschappelijke namen tussen deze twee werkbladen te selecteren of markeren - alles zonder ingewikkelde formules in te voeren. Deze methode is vooral effectief wanneer je te maken hebt met grote hoeveelheden gegevens of een visuele, kleurgecodeerde weergave wilt die gemakkelijk in één oogopslag te interpreteren is.
Na installatie van Kutools voor Excel, volg deze stappen om je lijsten gemakkelijk te vergelijken:
1. Ga naar het tabblad Kutools, klik vervolgens op Selecteer > Selecteer dezelfde & verschillende cellen zoals hieronder weergegeven:
2. In het geopende dialoogvenster Selecteer dezelfde & verschillende cellen:
(1.) Onder Zoek waarden in, selecteer het bereik uit Namen-1 dat je wilt vergelijken;
(2.) Onder Volgens, selecteer het bereik uit Namen-2 om mee te vergelijken;
(3.) In de sectie Gebaseerd op, kies Per rij om rijen respectievelijk te vergelijken;
(4.) Vanuit de Zoek-sectie, selecteer Dezelfde Waarden om overeenkomsten te identificeren en markeren;
(5.) Optioneel kun je een achtergrond- of lettertypekleur instellen om de resultaten te markeren en overeenkomsten visueel te laten uitkomen.
3. Klik op OK, en je ziet een meldingsvenster dat laat zien hoeveel overeenkomstige cellen zijn gevonden en gemarkeerd. Alle namen die in beide lijsten voorkomen, worden geselecteerd en visueel benadrukt, wat verdere beoordeling of wijziging vereenvoudigt:
Klik hier om Kutools voor Excel nu te downloaden en gratis te proberen!
Praktische tips: Als je werkbladen grote datasets bevatten, overweeg dan om de filterfunctie te gebruiken na het markeren om alleen de overeenkomsten snel te bekijken. Controleer ook, voordat je de vergelijking uitvoert, dat je bereikselecties correct zijn uitgelijnd en geen koprijen bevatten tenzij bedoeld, omdat discrepanties de resultaten kunnen beïnvloeden.
In uitzonderlijke gevallen, als de functie niet de verwachte resultaten retourneert, controleer dan of beide lijsten op dezelfde manier zijn opgemaakt (bijvoorbeeld beide als tekst, zonder verborgen voorloop- of volgspaties), omdat formatenverschillen mogelijk overeenkomsten kunnen missen.
Voorwaardelijke opmaak met formule tussen werkbladen
Als je liever geen formules in kolommen schrijft of add-ins gebruikt, kun je Voorwaardelijke opmaak met een aangepaste formule gebruiken om visueel overeenkomende namen in één werkblad te identificeren op basis van de gegevens van een ander werkblad. Deze methode is eenvoudig en vereist geen VBA, maar retourneert geen aparte lijst met resultaten - in plaats daarvan formatteert het alleen overeenkomsten voor een snelle visuele controle.
Toepasselijke scenario's: Deze oplossing is ideaal voor gebruikers die een niet-intrusieve, visuele indicator van overeenkomstige waarden willen en de structuur van het werkblad niet willen wijzigen. De beperking is dat regels voor voorwaardelijke opmaak geen andere werkmap rechtstreeks kunnen refereren, en formule-kruisbladreferenties werken alleen binnen hetzelfde bestand.
Stappen:
1. Selecteer in Namen-1 het bereik waarop je markeren wilt toepassen (bijvoorbeeld A2:A19).
2. Ga naar Start > Voorwaardelijke opmaak > Nieuwe regel > Gebruik een formule om te bepalen welke cellen te formatteren.
3. Voer in het formulevak de volgende formule in:
=COUNTIF('Names-2'!$A$2:$A$19,A2)>0Dit controleert of de waarde in A2 van Namen-1 ergens voorkomt in Namen-2!A2:A19.
4. Klik op Opmaak om een markeringskleur te kiezen, klik vervolgens op OK om de regel toe te passen. Elke overeenkomst wordt automatisch gemarkeerd in je geselecteerde bereik.
Praktische tips: Je kunt de bereiken aanpassen op basis van je daadwerkelijke gegevens, en de COUNTIF-stap kan worden gecombineerd met filteren om je te richten op alleen gemarkeerde cellen. Zorg ervoor dat beide werkbladen zich in hetzelfde werkboek bevinden bij het instellen van kruisbladreferenties, omdat Excel geen regels voor voorwaardelijke opmaak ondersteunt die externe bestanden refereren.
Foutmeldingen: Als markeringen niet verschijnen zoals verwacht, controleer dan je celbereikselecties en kruisbladreferenties op fouten. Zorg ervoor dat er geen voorloop- of volgspaties of formateninconsistenties zijn die overeenkomsten missen. Indien nodig, gebruik TRIM in een hulpkolom om de lijsten op te schonen voor een nauwkeurige vergelijking.
VBA-code - Automatisch lijsten vergelijken en overeenkomsten markeren of extraheren
Voor gebruikers die bekend zijn met macro's biedt het gebruik van VBA-code een zeer flexibele en geautomatiseerde manier om twee lijsten in aparte werkbladen te vergelijken. Deze aanpak stelt je in staat om overeenkomende namen te markeren of de overeenkomende waarden naar een nieuwe locatie te extraheren, wat vooral nuttig is bij het verwerken van grote hoeveelheden gegevens of het snel bijwerken van je lijsten.
Toepasselijke scenario's: Deze oplossing is vooral effectief wanneer je herhaaldelijk vergelijkingen wilt uitvoeren, zeer grote datasets wilt verwerken, rapportages wilt automatiseren, of wilt aanpassen hoe overeenkomsten worden verwerkt of gepresenteerd. Hoewel kennis van VBA nodig is, krijg je het voordeel van volledige automatisering en controle. Een nadeel is dat macro's moeten zijn ingeschakeld in het werkboek, wat mogelijk niet in alle omgevingen is toegestaan vanwege beveiligingsinstellingen.
Hoe de macro uit te voeren om overeenkomsten in Namen-1 te markeren als ze voorkomen in Namen-2:
1. Klik op Ontwikkelaarstools > Visual Basic om het Microsoft Visual Basic for Applications-venster te openen. Klik in het venster op Invoegen > Module en plak de volgende code in de nieuwe module:
Sub HighlightMatchingNames()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cell As Range
Dim matchFound As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws1 = Worksheets("Names-1")
Set ws2 = Worksheets("Names-2")
Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
For Each cell In rng1
Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not matchFound Is Nothing And cell.Value <> "" Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub2. Klik in de VBA-editor op de 
knop om de code uit te voeren. Deze macro scant de namen in kolom A van het werkblad "Namen-1", en als een naam ook voorkomt in kolom A van het werkblad "Namen-2", zal het die cel in "Namen-1" markeren met een gele vulkleur. Eventuele vorige markeringen in het bereik worden gewist voordat de nieuwe vergelijking wordt uitgevoerd.
Problemen oplossen: Als geen cellen zijn gemarkeerd, controleer dan of beide werkbladen exact de namen "Namen-1" en "Namen-2" hebben, en dat je gegevensbereiken beginnen bij A2. Zorg ervoor dat macro's zijn ingeschakeld, en dat noch het werkblad is beveiligd of gefilterd. Deze aanpak kan eenvoudig worden aangepast; je kunt bijvoorbeeld de markeringskleur veranderen, of de code aanpassen om overeenkomende resultaten naar een ander werkblad of kolom te kopiëren.
Samenvatting en suggesties: Afhankelijk van je behoeften en technisch comfortniveau kun je kiezen uit ingebouwde formuleoplossingen, macro-automatisering, slimme add-ins zoals Kutools, of eenvoudige visualisatie met Voorwaardelijke opmaak. Wanneer je formules of VBA gebruikt, controleer je altijd je gegevens op extra spaties of inconsistente opmaak, die vaak bronnen van fouten zijn. Backup je gegevens voordat je bulkwijzigingen maakt, vooral wanneer je macro's of add-ins voor het eerst gebruikt. Als je problemen tegenkomt zoals formules die niet bijwerken of onjuiste overeenkomsten, controleer dan op relatieve/absolute bereikfouten en verifieer de werkbladnamen. Door de methode te selecteren die past bij je workflow, kun je effectief en efficiënt lijsten in verschillende werkbladen in Excel vergelijken.
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