{count, plural, one {Cursus} other {Cursussen}}
Introductie tot statistiek in Google Sheets
{hours} uur
48.3K
Meestal, als je voor het eerst met Excel leert werken, is de kans groot dat VERT.ZOEKEN (VLOOKUP) wordt geïntroduceerd als de belangrijkste methode voor data wrangling. VLOOKUP is inderdaad een krachtige functie die veel taken rond gegevensverwerking aankan, zoals geïllustreerd in Data wrangling met VLOOKUP in spreadsheets. In dezelfde tutorial bespraken we ook de basis van de combinatie INDEX-MATCH, een andere methode voor verticale tabelopzoekingen met verschillende voordelen ten opzichte van VLOOKUP, weliswaar met een wat ingewikkeldere formule.
Deze voordelen omvatten onder andere:
- Dynamische kolomverwijzingen: Hiermee kun je veilig kolommen in je dataset invoegen en verwijderen zonder dat dit de zoekfunctie beïnvloedt.
- Hogere verwerkingssnelheid bij grote datasets: Als je tabellen omvangrijk zijn, merk je waarschijnlijk dat INDEX-MATCH veel sneller is dan VLOOKUP omdat INDEX-MATCH alleen kijkt naar de kolom die je wilt ophalen en de zoekkolom.
- Locatie van de zoekwaarden: VLOOKUP kan niet kijken naar waarden links van de eerste kolom die je in het tabelbereik selecteert, maar INDEX-MATCH kan ook horizontaal zoeken en heeft die beperking dus niet.
- Beperking van de lengte van de zoekwaarde: VLOOKUP kan geen waarden opzoeken die langer zijn dan 255 tekens, maar INDEX-MATCH kan dat wel.
Bovendien maakt INDEX-MATCH het ook mogelijk om te matchen op basis van de waarde van meerdere kolommen, en hoofdlettergevoelig te matchen. Dat is erg handig wanneer je te maken hebt met bepaalde ID’s die kunnen verschillen op basis van kleine en hoofdletters. Daarom duikt deze tutorial dieper in de geavanceerde functies van de combinatie INDEX-MATCH en laat hij met voorbeelden zien wanneer deze functies van pas komen.
De basis van INDEX-MATCH
In zijn eenvoudigste vorm kun je INDEX-MATCH op dezelfde manier gebruiken als VLOOKUP, om simpele verticale tabelopzoekingen uit te voeren op basis van een gemeenschappelijke sleutel. De basisstructuur van de formule ziet er als volgt uit:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: Het bereik van de kolom waarvan je de waarde wilt ophalen.
- lookup_value: De waarde die je wilt opzoeken (de sleutel).
- lookup_column_range: Het bereik van de kolom die de sleutels bevat waarop je wilt matchen.
- match_type: Ofwel 0 of 1. Waarbij 0 een exacte overeenkomst aangeeft en 1 een benaderende overeenkomst. Dit is analoog aan de ONWAAR/WAAR-vlag in VLOOKUP.
Laten we nu illustreren hoe INDEX-MATCH werkt met een paar voorbeeldtabellen. Deze eerste tabel bevat enkele namen en unieke sleutels. De tweede tabel bevat dezelfde sleutels (niet per se in dezelfde volgorde) en enkele beschrijvingen. De taak is dus om de beschrijvingen uit de tweede tabel te koppelen aan de namen in de eerste tabel met INDEX-MATCH. De tabellen zie je hieronder:
 |
 |
Zoals je ziet, mist in de eerste tabel de beschrijving. Laten we nu de naam koppelen aan de beschrijving met INDEX-MATCH:
Makkelijk toch? Zoals je in de video ziet, is de eerste stap het selecteren van een column_range met de gegevens die we willen ophalen in de INDEX-functie. In dit geval is dat Description (M2:M8). De tweede stap is het selecteren van de lookup_value binnen de MATCH-functie, dus cel B2. Tot slot voegen we het lookup_column_range (L2:L8) toe in de MATCH-functie en kiezen we 0 als match_type omdat we een exacte overeenkomst van de lookup_value willen. De rest is op ENTER drukken en de formule naar beneden kopiëren om de namen met de beschrijvingen te koppelen.
INDEX-MATCH kan, net als VLOOKUP, ook worden gebruikt om tabellen op afzonderlijke werkbladen te koppelen. Een voorbeeldvideo zie je hieronder:
Let op: Je hebt misschien gemerkt dat de kolombereiken die ik in de video’s selecteer, vaak worden omringd door $-tekens. Dit doe ik om vaste bereiken te selecteren die niet veranderen als je de formules verticaal of horizontaal kopieert. Die notatie blijf ik in deze tutorial gebruiken.
Tot nu toe gedraagt dit zich heel erg als een simpele VLOOKUP, maar dan met wat meer werk. Toch zijn er, zoals aan het begin van deze tutorial genoemd, verschillende voordelen als je de INDEX-MATCH-route volgt. Een van de nuttigste is dynamische kolomverwijzingen. Met andere woorden: je kunt kolommen toevoegen aan het tabelbereik waarin je zoekt, zonder dat dit de zoekactie beïnvloedt.
Als we in ons voorbeeld een nieuwe kolom zouden toevoegen en we gebruikten VLOOKUP, dan zouden we de formule in de linkertabel moeten herschrijven om naar de beschrijving te verwijzen, die dan kolom 3 zou worden. Gebruik je echter INDEX-MATCH, met zijn dynamische kolomverwijzingen, dan is dat niet nodig. Deze vergelijking zie je in de onderstaande video:
Het bovenstaande voorbeeld laat zien hoe de kolom die de zoekactie met INDEX-MATCH uitvoerde zich aanpaste toen de nieuwe kolom met de types ruimteschepen werd toegevoegd, terwijl de kolom met VLOOKUP dat niet deed. Dit voordeel van INDEX-MATCH ten opzichte van VLOOKUP komt echt tot zijn recht bij zoekopdrachten in grote Excel-tabellen, zoals een verkooprapport. Stel je bijvoorbeeld voor dat je meerdere bedrijfsdashboards in Excel hebt die allemaal verwijzen naar één tabel met alle data. Als je VLOOKUP gebruikt en het wordt op een dag nodig om in het midden een kolom in te voegen om een extra kenmerk toe te voegen, dan moet je de VLOOKUP-aanroepen aanpassen in alle dashboards die van die tabel afhankelijk zijn. Maar als je de dashboards hebt gebouwd met INDEX-MATCH als je opzoekmethode, dan heb je dat probleem niet.
Geavanceerd INDEX-MATCH: hoofdlettergevoelig matchen
Dynamische kolomverwijzingen zijn erg nuttig, maar dat is niet het enige voordeel van INDEX-MATCH. Zo kun je de basissyntax van INDEX-MATCH uitbreiden om hoofdlettergevoelige zoekopdrachten uit te voeren. We gaan hiervoor een EXACT-functie binnen de MATCH-functie toevoegen. De gedetailleerde syntax zie je hieronder:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: Het relevante tabelbereik dat zowel de zoekkolom als de gewenste kolom bevat die je wilt ophalen.
- lookup_value: De waarde die je wilt opzoeken (de sleutel)
- lookup_column_range: Het bereik van de kolom die de sleutels bevat waarop je wilt matchen
- match_type: Ofwel 0 of 1. Waarbij 0 een exacte overeenkomst aangeeft en 1 een benaderende overeenkomst. Dit is analoog aan de ONWAAR/WAAR-vlag in VLOOKUP
- desired_column_number: Hetzelfde als het kolomindexnummer in VLOOKUP. Dit is de kolomindex die de gegevens bevat die je wilt ophalen.
Hoofdlettergevoelig matchen met deze aangepaste INDEX-MATCH-combinatie kan erg handig zijn bij het matchen van sleutels met letters die verschillen in hoofd- en kleine letters (bijv. je hebt twee verschillende sleutels, de ene is "00567UUp" en de andere "00567UUP"). Zulke situaties kom je bijvoorbeeld tegen in het veelgebruikte Salesforce CRM bij het matchen van Account-ID’s of Opportunity-ID’s. Voor nu gebruiken we geen Salesforce-datasets, maar een aangepaste versie van onze voorbeeldtabellen in de onderstaande video om te laten zien hoe de bovenstaande formule in de praktijk werkt.
Let op: de bovenstaande functiemix staat tussen accolades. Dit betekent dat het een matrixformule is en moet worden ingevoerd met Ctrl+Shift+Enter.
Zoals je ziet kan de syntax van deze formule lang zijn om te typen, maar de resultaten zijn precies zoals je verwacht. Hij zoekt de waarden op net als de normale INDEX-MATCH, maar houdt rekening met de letter-casing. De magie komt van de EXACT-functie, die een array van TRUE en FALSE teruggeeft. Waar het TRUE is in deze array, is er een waarde met exact dezelfde letter-casing. Vervolgens bepaalt de MATCH-functie de positie van de eerste TRUE.
Let erop dat je Ctrl+Shift+Enter indrukt wanneer je klaar bent met het schrijven van de formule, anders krijg je een #N/B-fout die lastig te achterhalen kan zijn.
Geavanceerd INDEX-MATCH: matchen op basis van meerdere kenmerken
Hoofdlettergevoelige zoekopdrachten in Excel zijn best cool, maar er is nóg een functie die misschien wel beter is: zoekopdrachten die rekening houden met meerdere criteria. Rekening houden met meerdere criteria gebeurt vaak bij voorwaardelijke tel- of somfuncties zoals AANTAL.ALS.MEV en SOM.ALS.MEV (COUNTIFS of SUMIFS), maar het is minder gebruikelijk bij zoekfuncties in Excel. Toch kan het in bepaalde situaties handig zijn. Stel dat je een HR-Excelbestand hebt en je wilt het salaris van een medewerker opzoeken op basis van voornaam EN achternaam. Als je slechts één van beide kolommen gebruikt, is het mogelijk dat je het salaris van de verkeerde medewerker krijgt, omdat er veel mensen met dezelfde voornaam of achternaam kunnen zijn. De kans dat je de juiste persoon vindt, is veel groter als je ophaalt op basis van zowel voor- als achternaam.
Laten we nu kijken naar de structuur van INDEX-MATCH waarbij meerdere kenmerken worden meegenomen:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
Grotendeels heeft de formule exact dezelfde onderdelen als de normale INDEX-MATCH, maar hij kan nu rekening houden met n zoekwaarden met het bijbehorende aantal zoekbereiken. Als je een opfrisser nodig hebt van wat deze onderdelen zijn, kun je hierboven naar de basis-syntax van INDEX-MATCH scrollen. De reden dat we een "1" in de MATCH-functie hebben, is dat deze formule arrays van enen en nullen maakt die de overeenkomsten van elk gewenst criterium weergeven. Vervolgens gebruikt hij MATCH om de positie van de eerste 1 te bepalen.
Net als bij de hoofdlettergevoelige formule staat ook deze tussen accolades, wat betekent dat je op Ctrl+Shift+Enter moet drukken wanneer je klaar bent met typen, omdat dit een matrixformule is.
Laten we in de onderstaande video bekijken hoe deze formule in de praktijk werkt door ruimteschepen te selecteren die tot een bepaald ras behoren en een bepaalde maximale warpsnelheid hebben:
Voilà! Nu heb je de tools om te matchen op basis van meerdere sleutels. Onthoud dat ik in dit kleine voorbeeld slechts twee heb gebruikt, de sleutel en de maximale warpsnelheid, maar in theorie kun je criteria blijven toevoegen als je dat wilt. Terug naar ons HR-voorbeeld: je zou kunnen matchen op voor- en achternaam én functietitel om de kans te minimaliseren dat je het salaris van de verkeerde medewerker ophaalt.
Conclusie
Gefeliciteerd! Je bent nu vertrouwd met INDEX-MATCH en de vele dingen die je ermee kunt doen, voorbij een simpele verticale zoekactie. Als je ooit complexe Excel-rapporten moet beheren, wordt deze functiemix waarschijnlijk je beste vriend. Ik leerde net als velen VLOOKUP op school, maar toen ik Excel serieus ging gebruiken voor taken zoals hoofdlettergevoelige zoekopdrachten van Salesforce-account-ID’s, merkte ik dat INDEX-MATCH op de lange termijn een veel betere optie was, ondanks het gedoe en de foutgevoeligheid bij het schrijven van de formule. Ik raad je aan het eens te proberen als je de volgende keer een verticale zoekactie in Excel moet doen. Wie weet ga je het net zo waarderen als ik.
Wil je nog meer leren? Neem dan een kijkje in DataCamp’s cursus Data-analyse met spreadsheets.
Onderwerpen
