XMATCH in Excel-tutorial: overzicht, syntaxis en praktische toepassingen

Tijdens je data-analyses zijn er veel situaties waarin je de relatieve positie van een item in een matrix of celbereik moet vinden op basis van een bepaalde waarde of een patroon. 

De meest effectieve manier om pattern-matching in Excel uit te voeren is met de XMATCH-functie. XMATCH is een simpele maar krachtige functie met verschillende mogelijkheden voor nauwkeurige en flexibele matches. 

In deze tutorial behandelen we de basis van de Excel-functie XMATCH, analyseren we de syntaxis en de verschillende parameters en geven we praktische voorbeelden om de mogelijkheden te illustreren. Ook vergelijken we XMATCH met MATCH, een andere populaire functie voor soortgelijke taken.  Tot slot bekijken we veelvoorkomende valkuilen bij het gebruik van de XMATCH-functie en delen we best practices voor een solide en efficiënte toepassing.

Als je nog bezig bent met je Excel-leertraject en meer wilt ontdekken, bekijk dan onze Excel Fundamentals skill track om alle basisprincipes te leren. 

Wat is XMATCH?

XMATCH is een flexibele en robuuste functie voor matching-bewerkingen. Met XMATCH() kun je allerlei soorten matches uitvoeren, van verticale en horizontale opzoekingen tot exacte, benaderende en gedeeltelijke overeenkomsten. 

Voor wie al lang met Excel werkt, is de MATCH-functie waarschijnlijk bekend. Voor de Excel-update waarin XMATCH werd geïntroduceerd, was MATCH de standaardfunctie voor matching-taken. Je kunt hier meer over leren in onze Excel Lookup Functions-tutorial. 

Zoals we later zullen zien, schiet MATCH in sommige scenario's tekort. In dat opzicht kun je XMATCH zien als een verbeterde en langverwachte upgrade van de traditionele MATCH-functie, met zoeken in elke richting en meerdere matchtypes, waardoor hij geschikter is voor een breder scala aan use-cases.

Let op: de functie is alleen beschikbaar in Excel voor Microsoft 365 en Excel 2021. Voor gebruikers van eerdere Excel-versies is MATCH nog steeds de enige beschikbare functie.

Laten we nu de XMATCH-syntaxis en -parameters bekijken. 

XMATCH-syntaxis en -parameters

Dit is de basis-syntaxis van de XMATCH-functie:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Mogelijk gemaakt door

De functie heeft twee verplichte argumenten (dus lookup_value en lookup_array) en twee optionele (match_mode en search_mode) die zorgen voor meer precieze en flexibele bewerkingen.

Hieronder vind je een beschrijving van de vier parameters:

• Lookup_value: De te zoeken waarde.
• Lookup_array: De matrix of lijst waarin je zoekt. Dit kan een verticale of een horizontale lookup-matrix zijn.
• Match_mode: laat je het matchtype kiezen. Er zijn vier modi, waaronder:
• 0: Exacte match (de standaardmodus)
• -1: Exacte match of eerstvolgende kleinere waarde
• 1: Exacte match of eerstvolgende grotere waarde
• 2: Een wildcard-match, zoals * en ?.
• Search_mode: laat je de zoekmodus bepalen, inclusief zoekrichting en algoritme. Er zijn vier mogelijke waarden:
• 1: Zoek van eerst naar laatst (de standaardwaarde)
• -1: Zoek van laatst naar eerst (omgekeerd zoeken).
• 2:  Binaire zoekopdracht oplopend. De zoekopdracht gaat ervan uit dat de lookup-matrix oplopend is gesorteerd; anders levert dit ongeldige resultaten op.
• -2:  Binaire zoekopdracht aflopend. De zoekopdracht gaat ervan uit dat de lookup-matrix aflopend is gesorteerd; anders levert dit ongeldige resultaten op.

Match-modi: precisie en flexibiliteit

XMATCH heeft een optionele parameter match_mode waarmee je het matchtype kiest. Deze opties zijn erg handig en bieden manieren om zowel exact als bij benadering te matchen. Laten we de beschikbare match-modi in detail bekijken:

Exacte match

Exacte match (match_mode = 0) is de standaardmatchmodus van XMATCH. Dit betekent dat Excel een exacte overeenkomst van de zoekwaarde in de lookup-matrix vindt; anders geeft het een NA-fout. Exacte matching is essentieel wanneer nauwkeurigheid en precisie belangrijk zijn, zoals bij het zoeken naar specifieke records in een database.

Hier is een eenvoudig voorbeeld van hoe je de XMATCH-functie gebruikt om de exacte positie van "Excel" te vinden in een lijst met technologieën in de DataCamp Course Catalog. We geven de zoekwaarde in D3 en de lookup-matrix (A3:A14) op, en Excel geeft correct positie 6 terug. 

Benaderende match

Er zijn situaties waarin je flexibel wilt matchen, omdat je de exacte zoekwaarde niet weet of omdat je naar benaderende resultaten zoekt. 

Gelukkig heeft XMATCH twee match-modi waarmee je kunt zoeken naar exact of eerstvolgende kleinere en exact of eerstvolgende grotere waarden. 

In het volgende voorbeeld gebruiken we benaderende matching om de dichtstbijzijnde aantallen cursussen te vinden bij de zoekwaarde 15. 

Bij gebruik van de eerstvolgende kleinere waarde (match_mode = -1) geeft Excel positie 6 terug, want 12 cursussen is het dichtstbijzijnde kleinere getal in de lijst. Bij de eerstvolgende grotere waarde geeft Excel 5 terug, want 16 cursussen is het dichtstbijzijnde grotere getal.

We gebruiken ook de exacte match om te laten zien wat er gebeurt als Excel geen exacte overeenkomst vindt.

Wildcard- en regex-matches

Een innovatieve functie in XMATCH is de mogelijkheid om basis-wildcards te gebruiken wanneer je match_mode op 2 zet, en geavanceerdere reguliere expressies wanneer je deze op 3 zet. Deze match-modi werken alleen met tekstgegevens.

XMATCH laat je twee populaire wildcards gebruiken:

• ?: om één willekeurig teken te matchen.
• *: om een willekeurige reeks tekens te matchen.

Hieronder vind je verschillende voorbeelden van het gebruik van regex-matches. In het eerste voorbeeld zoeken we naar technologieën die met een E beginnen. Als tweede zoeken we naar technologieën met een A in de naam. Tot slot zoeken we naar technologieën met drie letters.

Let op dat XMATCH altijd het eerste voorkomen in de lookup-matrix teruggeeft, tenzij we anders specificeren (zie de volgende sectie).

Wil je complexere reguliere expressies gebruiken, zet match_mode dan op 3. Reguliere expressies, vaak afgekort als "regex" of "regexp", zijn een manier om een zoekpatroon te definiëren, dat kan worden gebruikt voor allerlei tekstbewerkingen zoals zoeken, parsen en/of vervangen van tekst. 

Voor meer details over pattern matching is onze Excel Regex Tutorial: Mastering Pattern Matching with Regular Expressions een goed startpunt.

Hier is een vrij eenvoudig voorbeeld: we willen de positie vinden van een cursus waarvan de code drie letters aan het begin, drie aan het einde en twee cijfers in het midden (31) bevat.

XMATCH-zoekmodi: richting en efficiëntie

XMATCH introduceert een nieuwe parameter waarmee je de richting van de zoekopdracht bepaalt. Deze functie is handig wanneer je bijvoorbeeld eerder geïnteresseerd bent in het laatste voorkomen dan in het eerste, zoals bij sommige taken in tijdreeksanalyse. 

Laten we de verschillende zoekmodi in detail verkennen.

Omgekeerd zoeken

Om omgekeerd te zoeken, zet je de parameter search_mode op -1. Hieronder zie je bijvoorbeeld hoe je de eerste en de laatste cursus vindt die door gebruiker Peter is voltooid.

Binaire zoekopdracht

Tot slot biedt de parameter search_mode ook de mogelijkheid om een binair zoekalgoritme te gebruiken voor de matching-bewerking. Binaire zoekopdrachten zijn zoekalgoritmen die werken door het zoekinterval herhaaldelijk te halveren. Het is een van de meest effectieve zoekalgoritmen, maar het vereist dat de invoerdata vooraf zijn gesorteerd. 

Met XMATCH kun je binaire zoekopdrachten gebruiken bij een oplopend gesorteerde matrix (search_mode = 2) en een aflopend gesorteerde matrix (search_mode = -2). Als de lookup-matrix niet is gesorteerd, geeft de functie een fout.

Praktische toepassingen en voorbeelden van XMATCH

XMATCH is geschikt voor een breed scala aan Excel-taken, van basis tot geavanceerd. In deze sectie behandelen we enkele van de meest aansprekende praktische toepassingen.

Tweedimensionale opzoekingen met INDEX/XMATCH

De functies INDEX en XMATCH kunnen samenwerken om tweedimensionale opzoekingen uit te voeren, dus tegelijk in rijen en kolommen. Door deze twee functies te combineren, kun je een dynamische lookup maken die goed werkt in domeinen zoals financiële analyse, waar het heel gebruikelijk is om variabelen over categorieën te vergelijken. 

In de afbeelding hieronder combineren we bijvoorbeeld INDEX en MATCH om het aantal ingeschreven gebruikers in Excel-cursussen tijdens Q2 op te halen. INDEX neemt hier drie argumenten: de matrix waarin je de waarde zoekt, het rijnummer en het kolomnummer. De waarden van de laatste twee argumenten worden geleverd door meerdere XMATCH-functies.

 Als we de technologie of het kwartaal willen wijzigen, hoef je alleen de zoekwaarden in de XMATCH-functies aan te passen en het resultaat van INDEX verandert mee.

INDEX/XMATCH werkt erg vergelijkbaar met INDEX/MATCH. Wil je meer weten over dit krachtige duo met praktische voorbeelden, bekijk dan onze How to INDEX MATCH Multiple Criteria in Excel -tutorial.

Opzoekingen op meerdere kolommen

Een andere sterke toepassing van XMATCH is het combineren van meerdere criteria om opzoekingen over meerdere kolommen te maken. Dat lijkt misschien niet intuïtief, omdat XMATCH een zoekwaarde en een lookup-matrix nodig heeft. Maar we kunnen booleaanse logica gebruiken om binnen de XMATCH-formule een tijdelijke matrix te maken met nullen en enen die aangeven welke rijen aan je criteria voldoen. 

Stel bijvoorbeeld dat je de eerste technologie in de lijst wilt vinden met minder dan 1000 ingeschreven gebruikers in elk kwartaal. Je kunt de voorwaarden combineren door de resultaten in elke kolom te vermenigvuldigen met het *-symbool. Excel vindt onder water de matrix met allemaal enen, in ons geval Tableau, en dus positie 5.

Dynamisch filteren van arrays

XMATCH kan ook samenwerken met de functies FILTER en ISNUMBER om dynamisch filteren van arrays te verbeteren.

Hieronder zie je hoe dat werkt. We gebruiken deze functies om de informatie over voltooide cursussen per gebruiker te filteren op basis van de gebruikersnaam. 

Eerst gebruiken we XMATCH om de relatieve positie van elke gebruiker in de tabel te berekenen op basis van de lookup-matrix met de namen waarop we willen filteren. Dit levert een tabel op met de relatieve posities van elke naam, inclusief lege waarden voor gebruikers die niet in de filterlijst staan.

Vervolgens zetten we de kolom met relatieve posities en lege waarden om naar TRUE of FALSE met de functie ISNUMBER.

Tot slot gebruiken we FILTER om alle kolommen te selecteren die we willen tonen, en de kolom met TRUE en FALSE om rijen te filteren.

Omgaan met lege waarden met ISNA

Wanneer XMATCH geen match kan vinden, geeft het een NA-fout. Hoewel dat al informatief is, kun je de resultaten verfraaien en misinterpretaties voorkomen met de functies ISNA en IF.

Stel dat we een tabel hebben met de cursussen die Sam en Peter hebben voltooid, en we willen weten welke cursussen ze gemeen hebben. Als we alleen XMATCH gebruiken, geeft Excel de relatieve posities van gemeenschappelijke cursussen terug, en anders lege waarden. Als we XMATCH doorgeven als argument aan ISNA, geeft de functie in plaats daarvan TRUE of FALSE. Met de IF-functie kunnen we de booleaanse waarden vervolgens vervangen door andere berichten.

Binaire zoekopdracht voor grote datasets

We noemden al dat XMATCH je laat kiezen voor een binair zoekalgoritme tijdens matching-bewerkingen. Deze functie kan een gamechanger zijn bij grote datasets. Als de lookup-matrix gesorteerd is, kan kiezen voor de binaire zoekmachine je matching aanzienlijk versnellen.

XMATCH vs MATCH: belangrijkste verschillen

Zoals gezegd is XMATCH ontworpen om de traditionele MATCH-functie te vervangen. Ze hebben hetzelfde doel, maar XMATCH biedt nieuwe mogelijkheden en is krachtiger dan MATCH. Hieronder vind je de belangrijkste verschillen tussen XMATCH en MATCH:

Veelvoorkomende valkuilen en oplossingen

Er zijn veel mogelijke toepassingen van XMATCH. Het is echter belangrijk om de veelvoorkomende problemen te noemen die je kunt tegenkomen. Hier is een lijst met reguliere valkuilen en tips om ze aan te pakken.

• Controleer datatypes: XMATCH levert onverwacht gedrag op als je het toepast op een bereik met het verkeerde datatype. Zorg er dus voor dat je voor je kolommen de juiste datatypes hebt ingesteld voordat je de functie toepast.
• Consistentie in bereiken: Hoewel XMATCH horizontale en verticale lookup-bereiken ondersteunt, kan het mixen ervan leiden tot onverwachte problemen en prestatieverlies. Probeer waar mogelijk consistent te zijn en kies óf horizontaal óf verticaal.
• Benut match-modi: XMATCH heeft verschillende match-modi. De standaardmodus is exact, maar je kunt je opzoekingen verfijnen met benaderende zoekopdrachten en regex-bewerkingen.
• Beter met gesorteerde matrices: Over het algemeen presteert XMATCH beter op gesorteerde matrices. Dat geldt voor de standaardfunctie, en is een vereiste als je binaire zoekopdrachten wilt gebruiken. 
• Omgaan met N/A-fouten:  Zoals gezegd geeft XMATCH een NA-fout als er geen match is in je opzoekingen. De beste manier om dit aan te pakken en de leesbaarheid te vergroten, is door de resultaten door te geven aan een combinatie van IF- en ISNA-functies, of aan een IFNA-functie.

Prestatie-overwegingen

XMATCH is niet de enige functie om opzoekingen in Excel uit te voeren. Je kunt bijvoorbeeld ook VLOOKUP, HLOOKUP of zelfs legacy-functies zoals MATCH overwegen. Maar als het gaat om zoeksnelheid, is XMATCH waarschijnlijk het snelst, vooral bij geordende matrices, waar je ook de kracht van binaire zoekopdrachten kunt benutten.

Het is ook goed om te vermelden dat XMATCH is ontworpen als een dynamische array-formule, wat betekent dat het arrays van variabele grootte kan retourneren, afhankelijk van de invoergegevens. Dynamische array-formules zijn vooral geschikt voor grote datasets of complexe berekeningen. 

Conclusie

Gefeliciteerd, je bent tot het einde gekomen. XMATCH is een krachtige functie die je niet wilt missen in je Excel-analyses. Als zelfstandige functie of in combinatie met andere Excel-functies tilt XMATCH je opzoekingen naar een hoger niveau. 

Maar er is nog veel meer te leren in de wondere wereld van Excel. Bekijk onze cursussen, blogs en tutorials om een Excel-wizard te worden:

• XLOOKUP() vs. VLOOKUP(): een vergelijking voor Excel-gebruikers
• Data Manipulation in Excel Cheat Sheet 
• Excel | Master Advanced Techniques Course
• Intermediate Power Query in Excel 
• Excel Shortcuts Cheat Sheet 
• Excel Formulas Cheat Sheet 
• How to INDEX MATCH Multiple Criteria in Excel 
• Data Wrangling with INDEX-MATCH in Spreadsheets 
• Excel Regex Tutorial: Mastering Pattern Matching with Regular Expressions