VERT.ZOEKEN vs X.ZOEKEN: welke moet je gebruiken?

VERT.ZOEKEN is jarenlang de standaard geweest voor opzoeken in Excel, maar met X.ZOEKEN kwam er een functie die dezelfde klus flexibeler en met minder foutgevoeligheid klaart. Deze vergelijking laat zien waar ze verschillen, welke versie je nodig hebt, welke fouten je met VERT.ZOEKEN vaak maakt en wanneer je toch beter bij VERT.ZOEKEN blijft.

Kies X.ZOEKEN als je Excel het ondersteunt, anders VERT.ZOEKEN

Kort samengevat: gebruik X.ZOEKEN wanneer je op Microsoft 365, Excel 2021 of Excel 2024 werkt. De functie is eenvoudiger op te bouwen, breekt niet als je kolommen verplaatst en zoekt in beide richtingen. Werk je op Excel 2019 of ouder, dan is X.ZOEKEN niet beschikbaar en blijft VERT.ZOEKEN (of INDEX met VERGELIJKEN) de logische keuze.

Beide functies doen hetzelfde grondwerk: ze zoeken een waarde in een lijst en geven een bijbehorende waarde terug. Het verschil zit in het gemak, de richting en de foutbestendigheid. Wie dagelijks met opzoekformules werkt, merkt dat X.ZOEKEN minder onderhoud vraagt naarmate een bestand groeit.

Voor een bestaande werkmap hoef je niet meteen alles om te bouwen. VERT.ZOEKEN blijft gewoon werken; Microsoft haalt de functie niet weg. Maak je nieuwe formules en heb je een versie die X.ZOEKEN ondersteunt, dan is het verstandig om de nieuwe functie te leren, omdat je er de klassieke valkuilen van VERT.ZOEKEN mee omzeilt. In een gemengd team, waar niet iedereen dezelfde Excel-versie draait, weegt de compatibiliteit zwaarder dan het gemak en houd je het voorlopig bij VERT.ZOEKEN.

VERT.ZOEKEN zoekt van links naar rechts met een vast kolomnummer

VERT.ZOEKEN (verticaal zoeken) doorzoekt de meest linkse kolom van een bereik en geeft een waarde terug uit een kolom rechts daarvan. De syntaxis:

=VERT.ZOEKEN(zoekwaarde; tabelmatrix; kolomindex_getal; [benaderen])

• zoekwaarde: wat je zoekt, bijvoorbeeld een artikelnummer.
• tabelmatrix: het bereik waarin gezocht wordt; de zoekkolom moet de eerste kolom zijn.
• kolomindex_getal: het volgnummer van de kolom met het antwoord, geteld vanaf de zoekkolom.
• benaderen: ONWAAR of 0 voor een exacte overeenkomst, WAAR of 1 voor een benadering.

Let op het laatste argument. Laat je het weg, dan gebruikt Excel WAAR en zoekt het een benadering, wat op ongesorteerde data verkeerde uitkomsten geeft. Voor een betrouwbaar resultaat typ je bijna altijd ONWAAR. Een voorbeeld dat de prijs bij een artikelnummer in A2 opzoekt:

=VERT.ZOEKEN(A2; Artikelen!A:D; 4; ONWAAR)

De zwakke plek zit in dat vaste getal 4. Voeg je een kolom toe binnen de tabelmatrix, dan blijft de formule 4 tellen en haalt hij ineens de verkeerde kolom op, of geeft hij een #VERW!-fout. Hoe je zo'n verwijzingsfout herstelt, lees je in de #VERW!-fout oplossen.

X.ZOEKEN werkt met aparte zoek- en resultaatbereiken in elke richting

X.ZOEKEN scheidt de kolom waarin je zoekt van de kolom die je terugkrijgt. Daardoor maakt de volgorde van de kolommen niet meer uit. De syntaxis:

=X.ZOEKEN(zoekwaarde; zoeken_matrix; retourneren_matrix; [indien_niet_gevonden]; [overeenkomstmodus]; [zoekmodus])

• zoeken_matrix: de kolom of rij waarin je zoekt.
• retourneren_matrix: de kolom of rij waaruit het antwoord komt; die mag links of rechts liggen.
• indien_niet_gevonden: de tekst of waarde die verschijnt als er niets is gevonden, ingebouwd zodat je geen ALS.FOUT hoeft te nesten.
• overeenkomstmodus: 0 voor exact (de standaard), -1 of 1 voor de dichtstbijzijnde kleinere of grotere waarde, 2 voor jokertekens.
• zoekmodus: 1 van boven naar beneden, -1 van onder naar boven, 2 of -2 voor binair zoeken in gesorteerde data.

Dezelfde prijsopzoeking wordt hiermee korter en robuuster:

=X.ZOEKEN(A2; Artikelen!A:A; Artikelen!D:D; "niet gevonden")

Omdat je verwijst naar de kolommen zelf, blijft de formule kloppen als je later een kolom tussenvoegt. En met het vierde argument vang je een ontbrekende waarde meteen af.

De overeenkomstmodus en zoekmodus geven X.ZOEKEN extra kracht

De laatste twee argumenten van X.ZOEKEN maken uitzonderingen mogelijk die met VERT.ZOEKEN lastig zijn. De overeenkomstmodus bepaalt hoe streng er gematcht wordt:

• 0: exacte overeenkomst, de standaard.
• -1: de dichtstbijzijnde kleinere waarde, handig voor een staffel of tariefgrens.
• 1: de dichtstbijzijnde grotere waarde.
• 2: jokertekens, waarbij * voor meerdere tekens staat en ? voor één teken.

Een voorbeeld met een kortingsstaffel waarin je de korting zoekt bij een besteld aantal, zonder dat het exacte aantal in de tabel staat:

=X.ZOEKEN(D2; Staffel!A:A; Staffel!B:B; 0; -1)

De zoekmodus bepaalt de richting. Met 1 zoekt X.ZOEKEN van boven naar beneden, met -1 van onder naar boven. Dat laatste is nuttig als je de laatste in plaats van de eerste overeenkomst wilt, bijvoorbeeld de meest recente prijs in een chronologische lijst.

De belangrijkste verschillen staan hier naast elkaar

Deze tabel vat de praktische verschillen samen die bepalen welke functie prettiger werkt.

Kenmerk	VERT.ZOEKEN	X.ZOEKEN
Zoekrichting	Alleen naar rechts vanaf de eerste kolom	Links, rechts, omhoog en omlaag
Standaard overeenkomst	Benadering (WAAR)	Exact (0)
Kolom tussenvoegen	Breekt door het vaste indexgetal	Blijft werken
Niet-gevonden afhandelen	ALS.FOUT of ALS.NB nesten	Ingebouwd vierde argument
Meerdere kolommen teruggeven	Nee, één kolom per formule	Ja, geeft een bereik terug dat overloopt
Van onder naar boven zoeken	Nee	Ja, met zoekmodus -1
Horizontaal zoeken	Aparte functie HORIZ.ZOEKEN nodig	Zelfde functie
Beschikbaar vanaf	Alle moderne versies	Microsoft 365, Excel 2021, Excel 2024

X.ZOEKEN werkt alleen vanaf Excel 2021 en Microsoft 365

De beschikbaarheid is vaak de doorslaggevende factor. X.ZOEKEN is toegevoegd met de dynamische matrices en werkt in Microsoft 365, Excel 2021, Excel 2024 en Excel voor het web. In Excel 2019, 2016 en ouder ontbreekt de functie; open je daar een bestand met X.ZOEKEN, dan zie je een #NAAM?-fout omdat Excel de functienaam niet herkent.

Werk je in een team met verschillende Excel-versies, houd daar dan rekening mee. Een bestand met X.ZOEKEN opent bij een collega op Excel 2016 wel, maar de formules rekenen niet en tonen een fout. In zo'n gemengde omgeving is VERT.ZOEKEN of de combinatie INDEX met VERGELIJKEN de veilige keuze. X.ZOEKEN vereist bovendien een geactiveerde, actuele licentie; hoe activering precies werkt, lees je in wat software-activering is en hoe het werkt.

INDEX met VERGELIJKEN is het alternatief voor oudere versies

Kun je X.ZOEKEN niet gebruiken maar wil je toch naar links zoeken of ongevoelig zijn voor het verplaatsen van kolommen, gebruik dan INDEX met VERGELIJKEN. VERGELIJKEN zoekt de positie van de zoekwaarde en INDEX haalt op die positie de gewenste waarde op.

=INDEX(Artikelen!D:D; VERGELIJKEN(A2; Artikelen!A:A; 0))

De 0 in VERGELIJKEN dwingt een exacte overeenkomst af, net als ONWAAR bij VERT.ZOEKEN. Deze combinatie werkt in alle Excel-versies en kan, anders dan VERT.ZOEKEN, ook naar links opzoeken. Het nadeel is dat de formule langer en minder leesbaar is dan X.ZOEKEN, wat de reden is dat X.ZOEKEN de opvolger is geworden.

Bij VERT.ZOEKEN gaat het vaak op dezelfde punten mis

Levert VERT.ZOEKEN een verkeerd of leeg resultaat, dan zit de oorzaak meestal in een van deze punten.

• Benadering in plaats van exact. Zonder ONWAAR of 0 zoekt de functie de dichtstbijzijnde waarde. Op ongesorteerde data levert dat willekeurige uitkomsten op.
• Zoekwaarde niet in de eerste kolom. VERT.ZOEKEN kijkt alleen in de meest linkse kolom van de tabelmatrix. Staat de zoekkolom verderop, dan vindt hij niets.
• Getal versus tekst. Een artikelnummer dat in de ene lijst als getal en in de andere als tekst is opgeslagen, matcht niet. Maak beide gelijk.
• Spaties. Een onzichtbare spatie voor of achter de waarde verhindert de match. De functie SPATIES.WISSEN ruimt die op.
• Absolute verwijzing vergeten. Sleep je de formule door zonder de tabelmatrix met dollartekens vast te zetten ($A$2:$D$500), dan verschuift het bereik mee.

Veel van deze valkuilen verdwijnen met X.ZOEKEN, dat standaard exact zoekt en de zoek- en resultaatkolom apart benoemt.

X.ZOEKEN vervangt ook HORIZ.ZOEKEN

Vroeger had je voor horizontaal opzoeken een aparte functie nodig: HORIZ.ZOEKEN zocht in de bovenste rij en gaf een waarde uit een rij eronder. X.ZOEKEN doet dat met dezelfde syntaxis, alleen wijs je nu een rij aan in plaats van een kolom. Staan de maanden in rij 1 en de omzet in rij 2, dan haal je de omzet van een maand zo op:

=X.ZOEKEN("maart"; A1:L1; A2:L2)

Je hoeft dus geen tweede functie te leren. Zowel verticaal als horizontaal opzoeken loopt via X.ZOEKEN, wat het aantal functies dat je moet onthouden terugbrengt. HORIZ.ZOEKEN blijft wel bestaan voor oudere bestanden, net als VERT.ZOEKEN.

Op grote bestanden let je op de rekenwijze

Bij lijsten van tienduizenden rijen speelt snelheid mee. VERT.ZOEKEN met een benaderende zoekopdracht (WAAR) op gesorteerde data is snel, omdat Excel dan een binaire zoekmethode gebruikt. Diezelfde snelheid haal je bij X.ZOEKEN met zoekmodus 2 of -2, die eveneens binair zoekt in oplopend of aflopend gesorteerde gegevens.

Werk je met exacte overeenkomsten op ongesorteerde data, dan doorloopt de functie de lijst rij voor rij; dat is bij beide functies vergelijkbaar. Een grotere winst boek je door niet naar hele kolommen te verwijzen (A:A) maar naar een begrensd bereik of een Excel-tabelkolom, zodat Excel niet ruim een miljoen lege rijen meeneemt in de berekening.

Herhaal je dezelfde opzoeking honderden keren in een kolom, dan telt elke overbodige berekening op. Vermijd het om binnen één cel meerdere zware X.ZOEKEN- of VERT.ZOEKEN-formules te nesten wanneer je met een enkele opzoeking en een verwijzing naar het resultaat toe kunt. Dat houdt de herberekening van een groot bestand merkbaar sneller.

Een praktijkvoorbeeld laat het verschil in gedrag zien

Stel je hebt een prijslijst met in kolom A het artikelnummer, in kolom B de naam en in kolom C de prijs. Je wilt bij een artikelnummer de naam ophalen.

Met VERT.ZOEKEN lukt dat prima, want de naam staat rechts van de zoekkolom:

=VERT.ZOEKEN(F2; A:C; 2; ONWAAR)

Draai je de opdracht om en wil je bij een naam het artikelnummer terugkrijgen, dan loopt VERT.ZOEKEN vast, omdat het antwoord links van de zoekkolom ligt. X.ZOEKEN heeft daar geen moeite mee:

=X.ZOEKEN(F2; B:B; A:A)

Wil je in één keer zowel de naam als de prijs, dan geef je bij X.ZOEKEN een resultaatbereik van meerdere kolommen op. Het antwoord overloopt automatisch naar de cellen ernaast:

=X.ZOEKEN(F2; A:A; B:C)

Dat overlopen kan wel een #OVERLOOP!-fout geven als de buurcellen bezet zijn. Zorg dus dat de cellen rechts van je formule leeg zijn wanneer je een meerkoloms resultaat verwacht.

Kies op basis van versie, richting en samenwerking

Gebruik deze vuistregels om snel te beslissen.

• Kies X.ZOEKEN als je Microsoft 365, Excel 2021 of Excel 2024 hebt en de rest van het team ook. Je krijgt kortere formules, exact zoeken als standaard en het antwoord mag aan beide kanten staan.
• Kies VERT.ZOEKEN als het bestand ook geopend wordt in Excel 2019 of ouder, of als je een bestaande werkmap onderhoudt waarin al met VERT.ZOEKEN wordt gewerkt.
• Kies INDEX met VERGELIJKEN als je op een oudere versie toch naar links wilt zoeken; die combinatie kan wat X.ZOEKEN doet, maar werkt in alle versies.

Twijfel je nog, kijk dan naar wat je precies nodig hebt. Zoek je één waarde op basis van één sleutel en staat het antwoord rechts, dan volstaat VERT.ZOEKEN prima. Wil je naar links zoeken, meerdere kolommen tegelijk teruggeven, de laatste in plaats van de eerste match pakken, of een ontbrekende waarde netjes opvangen, dan is X.ZOEKEN de logische keuze. Voor het combineren van twee lijsten op een gedeelde sleutel voldoen beide; de leesbaarheid van je formule geeft dan de doorslag.

Wil je de brontabel eerst opschonen of samenvatten voordat je erin gaat opzoeken, dan is een draaitabel vaak handiger dan een reeks losse formules; zie hoe je een draaitabel in Excel maakt. De officiële beschrijving van beide functies vind je in de documentatie van Microsoft over X.ZOEKEN.