VERT.ZOEKEN of X.ZOEKEN (of INDEX en VERGELIJKEN)? Zo kies je de juiste

Wat is het verschil tussen VERT.ZOEKEN en X.ZOEKEN?

X.ZOEKEN krijgt twee aparte bereiken, zoekt standaard exact en kijkt in elke richting. VERT.ZOEKEN krijgt één tabel en een kolomnummer, zoekt alleen in de eerste kolom van die tabel en zoekt benaderend, tenzij je ONWAAR toevoegt. Microsoft noemt X.ZOEKEN op zijn eigen hulppagina "een verbeterde versie van VERT.ZOEKEN", die in elke richting werkt en standaard exact zoekt (Microsoft).

VERT.ZOEKEN is daarmee niet meteen afgeschreven. Toen Microsoft X.ZOEKEN in 2019 aankondigde, noemde het VERT.ZOEKEN de op twee na meest gebruikte functie van Excel, na SOM en GEMIDDELDE (Microsoft). Heel wat bestaande bladen rekenen ermee, en wie Excel gebruikt, komt het dus nog jaren tegen. De vraag is niet welke functie wint, maar welke past bij jouw blad en bij de versie van wie het opent.

Neem de klantenlijst van De Vliegende Velo, fietskoeriers in Antwerpen en de vijfde klant in onze Excel-cursus. Elke rit heeft een klantcode in kolom B. Rechts in het blad staat de lijst waarin dispatcher Kobe opzoekt wie die klant is en in welke gemeente die zit:

Rit R01 is voor klant K03, en die code staat in B2. Kobe wil weten naar welke gemeente de rit gaat. De gemeente staat in kolom M, rechts van de code, dus hier kunnen alle drie de functies het. Zo zoek je de gemeente van die klant op, met elk van de drie manieren:

=VERT.ZOEKEN(B2;L2:M6;2;ONWAAR)
=X.ZOEKEN(B2;L2:L6;M2:M6)
=INDEX(M2:M6;VERGELIJKEN(B2;L2:L6;0))

Alle drie geven Antwerpen. Het verschil zie je pas als het minder netjes gaat: een code die niet in de lijst staat, een kolom die erbij komt, een naam die links van de code staat. Daar gaan de volgende stukken over, telkens op dit blad.

Waarom geeft VERT.ZOEKEN een fout antwoord zonder foutmelding?

Omdat VERT.ZOEKEN benaderend zoekt als je het vierde argument weglaat. Volgens Microsoft is de standaardwaarde dan altijd WAAR (Microsoft). Benaderend betekent: neem de grootste waarde die niet groter is dan wat je zoekt. De functie gaat er daarbij van uit dat de eerste kolom van klein naar groot gesorteerd is, en controleert dat niet.

Op het blad van De Vliegende Velo zie je wat dat doet. Rit R10 is voor K06, een nieuwe klant die nog niet in de lijst staat. =VERT.ZOEKEN(B11;L2:M6;2) geeft geen fout, maar Wilrijk: K05 is de grootste code die niet groter is dan K06. De rit krijgt dus de gemeente van een andere klant, en niets in het blad waarschuwt je. Met ONWAAR erbij krijg je #N/B, en dan weet je tenminste dat er iets ontbreekt.

Laat je ONWAAR weg, dan geeft VERT.ZOEKEN voor een code die niet bestaat vaak een antwoord dat er geloofwaardig uitziet. Schrijf het vierde argument altijd, tenzij je echt een schijf zoekt.

Hoe vaak gebeurt dat in het echt? Felienne Hermans en collega's van de TU Delft telden in een onderzoek uit 2015 (TU Delft) 14.231 opzoekfuncties in de rekenbladen van Enron: 43% zocht benaderend, 57% exact. Van die benaderende opzoekingen zocht 23% in een bereik dat niet gesorteerd was, en in 11 gevallen vonden ze echte fouten (Felienne Hermans).

Dezelfde valkuil zit in VERGELIJKEN, de helft van het duo INDEX en VERGELIJKEN. Zonder derde argument is de standaardwaarde 1, benaderend, en dan moeten de waarden oplopend gesorteerd zijn (Microsoft). =INDEX(M2:M6;VERGELIJKEN(B11;L2:L6)) geeft voor K06 dus ook Wilrijk. X.ZOEKEN zoekt standaard exact (Microsoft), en dat is misschien zijn grootste voordeel.

Benaderend zoeken is niet altijd fout. Bij tariefschijven is het precies de bedoeling: vanaf 0 km kost een rit bij De Vliegende Velo 6 euro, vanaf 3 km 9 euro, en een rit van 7,2 km valt in de schijf vanaf 6 km. Zo'n tabel staat altijd oplopend gesorteerd, en dan is WAAR de juiste keuze. Hoe dat werkt, lees je bij benaderend zoeken in tariefschijven.

Wat gebeurt er met VERT.ZOEKEN als je een kolom invoegt?

Dan wijst het kolomnummer naar de verkeerde kolom. In =VERT.ZOEKEN(B2;L2:M6;2;ONWAAR) is die 2 een vast getal. Voeg je tussen L en M een kolom in voor het telefoonnummer, dan maakt Excel van de tabel netjes L2:N6, maar de 2 blijft een 2. De formule geeft nu wat er in de nieuwe, lege kolom staat: een 0 in plaats van Antwerpen, en weer zonder foutmelding.

Microsoft zette dat zelf op het lijstje van zwaktes toen het X.ZOEKEN aankondigde, samen met het benaderend zoeken en het feit dat VERT.ZOEKEN niet naar links kan kijken (Microsoft). X.ZOEKEN en INDEX met VERGELIJKEN hebben geen kolomnummer: ze verwijzen naar het bereik met de antwoorden zelf. Na het invoegen wordt M2:M6 vanzelf N2:N6, en de formule geeft nog altijd Antwerpen.

Kan VERT.ZOEKEN naar links zoeken?

Nee. VERT.ZOEKEN zoekt altijd in de eerste kolom van de tabel en kan alleen teruggeven wat rechts daarvan staat. Op het blad van De Vliegende Velo staat de naam van de klant in kolom K, links van de code in L. Kobe wil die naam op elk ritbriefje, en met VERT.ZOEKEN lukt dat niet zonder de kolommen om te gooien. Dan kloppen de kolomnummers in andere formules misschien niet meer.

X.ZOEKEN kijkt waar je het zegt. =X.ZOEKEN(B2;L2:L6;K2:K6) zoekt K03 in de codes en neemt de naam op dezelfde plaats uit K2:K6: Café De Zwaan. =INDEX(K2:K6;VERGELIJKEN(B2;L2:L6;0)) doet hetzelfde, ook in versies zonder X.ZOEKEN. Omdat je het zoekbereik en het antwoordbereik apart opgeeft, maakt het niet uit welk van de twee links staat.

Wat als de code niet in de lijst staat?

Dan geeft X.ZOEKEN #N/B, net als VERT.ZOEKEN met ONWAAR. Het verschil zit in het vierde argument van X.ZOEKEN, indien_niet_gevonden: de tekst die er moet komen als er niets gevonden wordt (Microsoft). Met VERT.ZOEKEN heb je daarvoor een tweede functie nodig, zoals ALS.FOUT om foutwaarden op te vangen. Voor rit R10 en de nieuwe klant K06 ziet dat er zo uit:

=X.ZOEKEN(B11;L2:L6;M2:M6;"Nieuwe klant")
=ALS.FOUT(VERT.ZOEKEN(B11;L2:M6;2;ONWAAR);"Nieuwe klant")

Probeer het nu zelf, op het echte blad van De Vliegende Velo. Je zoekt de naam links van de code, trekt de formule door tot de laatste rit en vangt de nieuwe klant op. Vergeet de dollartekens niet, anders schuift de klantenlijst bij elke rij een rij mee naar beneden, en vallen er klanten uit.

Je formule rekende in je eigen browser, en we controleerden ze ook op een tweede versie van het blad, met andere ritten. Een formule die juist zoekt, telt dus, ook als ze er anders uitziet dan de onze. Wie de namen met de hand overtypt, valt door de mand. De oefening toont formules in je eigen dialect: Nederlandse namen met een puntkomma of Engelse met een komma.

Welke Excel-versies hebben X.ZOEKEN?

Microsoft 365 en Excel 2021 hebben X.ZOEKEN, Excel 2016 en Excel 2019 niet (Microsoft). Microsoft kondigde de functie aan in augustus 2019 en bracht ze in maart 2020 naar alle Office 365-abonnees in het Monthly Channel. Het Semi-Annual Channel volgde in juli 2020 (Microsoft). In de lijst met nieuwe functies van Excel 2021 staat X.ZOEKEN er ook bij (Microsoft).

De ondersteuning voor Office 2016 en 2019 stopte op 14 oktober 2025 (Microsoft), die voor Office 2021 op 13 oktober 2026 (Microsoft). De programma's kunnen blijven werken, alleen komen er geen beveiligingsupdates meer. Een collega, een klant of een school met Excel 2016 of 2019 kan je dus nog altijd tegenkomen, en die kent X.ZOEKEN niet.

Wat gebeurt er als je X.ZOEKEN opent in een oude Excel?

Microsoft zegt zelf dat je in Excel 2016 of 2019 een werkmap met X.ZOEKEN kan tegenkomen, als iemand anders ze in een nieuwere versie maakte (Microsoft). Wat er dan gebeurt, beschrijft Microsoft voor nieuwe functies in het algemeen: zodra een oudere versie het blad herberekent, geven ze #NAME? in plaats van hun resultaat (Microsoft). In een Nederlandstalige Excel heet die fout #NAAM?.

Deel je een bestand met iemand van wie je de versie niet kent, kies dan VERT.ZOEKEN met ONWAAR of INDEX met VERGELIJKEN. Een oud blad hoef je niet om te zetten: Microsoft schreef bij de aankondiging dat VERT.ZOEKEN en HORIZ.ZOEKEN gewoon ondersteund blijven (Microsoft).

Waarom gebruikt een formule soms een komma en soms een puntkomma?

Omdat het scheidingsteken tussen de argumenten afhangt van de landinstellingen van je besturingssysteem en van Excel, niet van Windows of Mac. Microsoft noemt de komma en de puntkomma de twee meest voorkomende, en een formule met het verkeerde teken werkt niet (Microsoft). Daarbovenop zijn de functienamen en de foutwaarden vertaald. Wie een formule van een Engelstalige site overneemt, past dus vaak twee dingen aan.

In onze cursus kies je zelf welke vorm je ziet, en alle formules passen zich aan, ook die in de oefening hierboven. Wil je ook de andere formules kennen die je in elke taal tegenkomt, zoals SOM.ALS en ALS, lees dan de belangrijkste Excel formules voor beginners.

Is INDEX en VERGELIJKEN nog nodig?

Ja, in drie gevallen: als iemand het bestand opent in een versie zonder X.ZOEKEN, als je een rij en een kolom tegelijk zoekt, en als je zoekwaarde heel lang is. Bij een zoekwaarde van meer dan 255 tekens geeft VERT.ZOEKEN #WAARDE!, en Microsoft raadt dan zelf INDEX en VERGELIJKEN aan (Microsoft).

In een tabel met rijen en kolommen, zoals de gewichtstoeslag per klasse en zone van De Vliegende Velo, zoek je met twee keer VERGELIJKEN de rij en de kolom. INDEX geeft dan wat op het kruispunt staat: =INDEX(L14:N16;VERGELIJKEN(Q13;K14:K16;0);VERGELIJKEN(Q14;L13:N13;0)) geeft 5 euro voor een middelzwaar pakje naar de haven. Hoe je dat duo opbouwt, leer je in INDEX en VERGELIJKEN.

Vergeet alleen de 0 niet. Zonder die 0 zoekt VERGELIJKEN benaderend, met dezelfde stille fouten als VERT.ZOEKEN zonder ONWAAR. Microsofts eigen gids over opzoeken wijst intussen naar X.ZOEKEN en X.VERGELIJKEN als verbeterde versies (Microsoft), en X.VERGELIJKEN zoekt standaard exact (Microsoft).

VERT.ZOEKEN, X.ZOEKEN of INDEX en VERGELIJKEN: welke kies je?

Kies X.ZOEKEN als iedereen die het bestand opent een versie met X.ZOEKEN heeft. Anders kies je INDEX met VERGELIJKEN, of VERT.ZOEKEN met ONWAAR als het antwoord rechts van de code staat. Twijfel je over de versie van je lezers, dan is de oudere functie de veilige keuze. Per situatie ziet dat er zo uit:

Wie vaak tussen twee lijsten opzoekt, doet trouwens wat een database met een JOIN doet: twee tabellen koppelen op een code. Op van Excel naar SQL lees je hoe VERT.ZOEKEN zich vertaalt, en in de SQL-cursus leer je het in tabellen koppelen met JOIN.

Opzoeken leer je het best op een blad dat je kan zien, met codes die niet bestaan en kolommen die erbij komen. In module 5 van onze Excel-cursus doe je dat bij De Vliegende Velo, van VERT.ZOEKEN tot X.ZOEKEN in elke richting. Op Excel-oefeningen met uitleg zie je hoe de oefeningen werken, en op Excel leren voor je werk lees je hoe de cursus aansluit bij wat je op je werk nodig hebt.