VERT.ZOEKEN (VLOOKUP) in Excel: wat het doet en de valkuilen

VERT.ZOEKEN doet eigenlijk maar één ding, en dat ding doet het goed: het zoekt een waarde op in de meest linkse kolom van een tabel en geeft je een waarde terug uit een andere kolom op diezelfde rij. Typ een artikelnummer, krijg de bijbehorende voorraad. Typ een klantnummer, krijg de naam. Zodra dat principe eenmaal bij je landt, snap je meteen waar de functie voor is bedoeld en waar ze vastloopt.

De meeste uitleg begint bij de knoppen: welke haakjes, welk argument op welke plek. Ik draai het liever om. Belangrijker dan de exacte volgorde is de vraag wanneer je VERT.ZOEKEN nodig hebt, wanneer de nieuwere X.ZOEKEN een verstandiger keuze is, en welke fout zowat iedereen een keer maakt. Pas als dat helder is, wordt de formule zelf logisch in plaats van een rijtje dat je uit je hoofd leert en na een week weer kwijt bent.

Wat doet VERT.ZOEKEN eigenlijk?

Stel je twee lijsten voor die iets met elkaar te maken hebben. In de ene staat een verkoopoverzicht met alleen productcodes, in de andere een lange lijst met codes en de bijbehorende namen en prijzen. Je wilt bij elke code uit de eerste lijst de juiste naam uit de tweede halen, zonder dat je honderden regels met de hand naast elkaar legt. Dat is precies het werk waar VERT.ZOEKEN voor gemaakt is: het legt automatisch de verbinding tussen twee tabellen via een gedeelde sleutel.

De functie neemt de waarde die je kent, zoekt die van boven naar beneden in de eerste kolom van het bereik dat je opgeeft, en geeft dan de waarde terug die op dezelfde rij in een kolom naar keuze staat. Eén detail bepaalt bijna alles wat later misgaat: VERT.ZOEKEN kijkt altijd naar rechts. De kolom waarin je zoekt moet de meest linkse van je bereik zijn, en het antwoord moet daar rechts van staan. Dat is geen instelling die je kunt omzetten, maar hoe de functie in elkaar zit.

De naam verklapt de helft. De V staat voor verticaal: je zoekt van boven naar beneden door een kolom. Er bestaat een zusterfunctie die precies het omgekeerde doet en horizontaal door een rij zoekt, maar die kom je in de praktijk zelden tegen, omdat gegevens vrijwel altijd in kolommen onder elkaar staan en niet in rijen naast elkaar. Wie dat eenmaal ziet, begrijpt waarom VERT.ZOEKEN zoveel vaker opduikt dan zijn horizontale broer: het past gewoon bij de manier waarop de meeste mensen een tabel opbouwen.

Welke vier dingen vraagt de formule?

Onder de motorkap vraagt VERT.ZOEKEN om vier stukjes informatie, en het helpt om ze niet als code te zien maar als vier gewone vragen die je beantwoordt.

1. De zoekwaarde: wat zoek je op? Meestal een cel met een code, een naam of een nummer.
2. Het tabelbereik: in welke tabel moet gezocht worden? Denk eraan dat de zoekkolom links ligt.
3. Het kolomindexnummer: het hoeveelste kolomnummer, geteld vanaf links binnen dat bereik, bevat het antwoord?
4. Benaderen of exact: kies ONWAAR voor een exacte overeenkomst. Dat wil je in bijna alle gevallen.

Dat laatste argument is de stille boosdoener achter veel rare uitkomsten. Laat je het weg of zet je het op benaderen, dan gaat Excel ervan uit dat je kolom oplopend gesorteerd is en pakt het de dichtstbijzijnde waarde die niet groter is. Bij een zoektocht op een exacte code of naam is dat zelden wat je bedoelt. Vandaar de vuistregel: zet ONWAAR neer, tenzij je heel bewust een benadering wilt, bijvoorbeeld bij het indelen in prijsklassen of cijfergrenzen.

Wanneer is VERT.ZOEKEN de juiste keuze, en wanneer niet?

VERT.ZOEKEN komt tot zijn recht op het moment dat je twee gegevensbronnen aan elkaar moet knopen via een gemeenschappelijk kenmerk. Een exportbestand uit je boekhouding koppelen aan een eigen prijslijst. Een lijst met e-mailadressen verrijken met de naam die erbij hoort. Aanwezigheidsregistratie omzetten naar afdelingen. In al die gevallen heb je een sleutel die in beide lijsten voorkomt, en wil je de rest van de rij ophalen. Dat is de kerntaak, en daarvoor is de functie snel, betrouwbaar en makkelijk uit te leggen aan een collega.

Er is één plek waar het benaderende zoeken juist zinvol is, en die wordt vaak vergeten: het indelen in klassen. Wil je bij een bedrag de juiste kortingstrap vinden, of bij een score de juiste beoordeling, dan zet je een tabel met ondergrenzen oplopend gesorteerd en laat je VERT.ZOEKEN de dichtstbijzijnde waarde eronder pakken. In dat geval is benaderen precies wat je bedoelt. Buiten dit soort grenzentabellen blijft ONWAAR echter de veilige standaard.

Er zijn ook situaties waarin je de functie beter niet inzet. Staat het antwoord links van je zoekkolom, dan houdt het op: VERT.ZOEKEN kan niet naar links kijken. Verandert de indeling van je bron regelmatig, dan is het kolomindexnummer een breekpunt, want een ingevoegde kolom schuift alles op en je formule wijst ineens naar de verkeerde plek. En moet je op twee kenmerken tegelijk zoeken, bijvoorbeeld een naam binnen een bepaalde vestiging, dan wordt VERT.ZOEKEN al snel een kunstgreep. Voor die gevallen bestaat er beter gereedschap, en daar kom ik zo op terug.

Een voorbeeld met een kleine tabel

Een voorbeeld maakt het concreet. Stel dat je een klein magazijnlijstje hebt met een code, een productnaam en de voorraad. Je wilt weten hoeveel stuks er van code A-102 op voorraad liggen, zonder de lijst zelf door te lezen.

Code	Product	Voorraad
A-101	Toetsenbord	24
A-102	Muis	60
A-103	Webcam	12

Wat hier gebeurt, is dat de formule “A-102” opzoekt in de kolom Code, de rij vindt waarop die code staat, en vervolgens de derde kolom van het bereik teruggeeft: de voorraad, 60. Wil je in plaats daarvan de productnaam, dan zet je een 2 op de plek van het kolomindexnummer. Merk op wat je hier niet kunt: als je alleen het getal 60 had en daar de code bij wilde zoeken, dan lukt dat niet met deze opzet, want de code staat links van de voorraad. Dat is de linkerbeperking in de praktijk.

In het echt typ je de zoekwaarde zelden letterlijk in de formule. Je verwijst naar een cel, bijvoorbeeld naar een veld waar iemand een code intypt, zodat het antwoord meebeweegt zodra die code verandert. Ga je de formule daarna naar beneden kopiëren over een hele lijst, zet dan het tabelbereik vast met dollartekens, dus A$2:C$4 in plaats van A2:C4. Vergeet je dat, dan schuift het bereik met elke rij mee omlaag en verdwijnen de bovenste regels uit beeld. Het is een klein detail dat verrassend veel foutmeldingen veroorzaakt bij mensen die denken dat hun formule klopt.

De valkuilen: #N/B en het kijken naar links

De foutmelding die je vroeg of laat tegenkomt is #N/B, kort voor “niet beschikbaar”. Excel zegt daarmee eerlijk: ik heb de waarde die je zocht niet gevonden. Vervelend, maar eigenlijk behulpzaam, want het betekent bijna altijd dat er iets kleins niet klopt in plaats van iets groots. De kunst is om niet in paniek de hele formule te herschrijven, maar rustig de vier gebruikelijke oorzaken langs te lopen.

Waarom krijg je #N/B?

De eerste oorzaak is de meest letterlijke: de waarde staat echt niet in de zoekkolom. Vaak is het subtieler. Een spatie achter de code die je met het oog niet ziet, een 0 die als tekst is opgeslagen in de ene tabel en als getal in de andere, of een typefout in de zoekwaarde. Een tweede veelgemaakte fout is een verkeerd kolomindexnummer, waardoor je naar een kolom wijst die buiten je bereik valt. Een derde is dat je bereik niet begint bij de zoekkolom, waardoor Excel op de verkeerde plek zoekt.

En dan is er de beperking die geen fout is maar een grens: VERT.ZOEKEN kan alleen naar rechts kijken, nooit naar links vanaf de meest linkse kolom. Wie zijn tabel zo heeft ingericht dat het antwoord links van de sleutel staat, kan draaien wat hij wil, het gaat niet werken. Op dat punt is het verstandiger om je tabel te herordenen of om een functie te gebruiken die deze grens niet kent. Precies daar komt X.ZOEKEN in beeld.

VERT.ZOEKEN of X.ZOEKEN?

X.ZOEKEN is de nieuwere opvolger, en op de punten waar VERT.ZOEKEN worstelt, is ze eenvoudig beter. Ze kan wel naar links kijken, omdat je apart aanwijst waar je zoekt en waar het antwoord staat. Ze heeft geen kolomindexnummer nodig, dus een ingevoegde kolom breekt je formule niet meer. En ze staat standaard op een exacte overeenkomst, waardoor de klassieke valkuil van het vergeten ONWAAR-argument vervalt. Voor wie vandaag begint, is X.ZOEKEN vaak de rustigere keuze.

Het verschil zie je goed als je hetzelfde voorbeeld nog eens doet. Waar je bij VERT.ZOEKEN nog het bereik en een kolomnummer 3 opgaf, wijs je bij X.ZOEKEN twee dingen apart aan: de kolom waarin je zoekt en de kolom waaruit je het antwoord haalt. Verplaats je later een kolom of voeg je er een in, dan blijft het kloppen, want er is geen kwetsbaar volgnummer meer dat naar de verkeerde plek kan wijzen. Dat maakt formules die maandenlang meegaan een stuk minder foutgevoelig, precies het soort werk waar VERT.ZOEKEN op den duur begint te kraken.

Er zit één voorwaarde aan. X.ZOEKEN is beschikbaar in Microsoft 365 en in Office 2021 en nieuwer. Werk je met een oudere Office-versie, dan is de functie er simpelweg niet en blijft VERT.ZOEKEN het gereedschap dat je hebt. Deel je een bestand met iemand die nog op zo’n oudere versie zit, dan is er nog een reden om bij VERT.ZOEKEN te blijven, want een X.ZOEKEN-formule geeft bij hen een foutmelding. Mijn advies is nuchter: heb je 365 of Office 2021, leer dan X.ZOEKEN en gebruik VERT.ZOEKEN alleen nog waar je bestanden anders niet compatibel blijven.

Welke Excel heb je hiervoor nodig?

Beide functies zitten in dezelfde Excel die je op twee manieren kunt hebben. De eerste is Microsoft 365, het abonnement dat je per jaar of per maand betaalt en dat altijd de nieuwste versie meelevert. De tweede is Office 2021, dat je eenmalig koopt en dat blijft werken zonder doorlopende betaling. In allebei zit een Excel die zowel VERT.ZOEKEN als X.ZOEKEN kent, dus voor deze functies hoef je niet per se voor het abonnement te kiezen.

Welke van de twee bij je past, hangt af van hoe je werkt, niet van welke functie je gebruikt. Wil je altijd de nieuwste functies en de clouddiensten eromheen, dan ligt het abonnement voor de hand; die keuze leg ik verder uit in wat is Microsoft 365. Heb je genoeg aan Excel zoals het nu is en betaal je liever één keer, dan is de losse aankoop op meerdere jaren gerekend meestal voordeliger. Het verschil tussen die twee routes zet ik naast elkaar in Office 365 versus Office 2021.

Zo houd je het werkbaar

Als je maar één gewoonte overneemt, maak het dan deze: zet standaard ONWAAR neer voor een exacte overeenkomst en behandel benaderen als de uitzondering, niet als de standaard. Daarmee voorkom je het gros van de stille fouten waarbij een formule wel een antwoord geeft, maar het verkeerde. Geef je bereiken bovendien een naam in plaats van een kale reeks cellen, dan lees je later terug wat een formule doet en verschuift er niets als je een kolom invoegt.

En als je merkt dat je VERT.ZOEKEN aan het buigen bent om het naar links te laten kijken of om op twee kenmerken te zoeken, neem dat dan als signaal. Het is geen teken dat je de functie niet snapt, maar dat je tegen haar grens aanloopt. Heb je een Excel met X.ZOEKEN, stap dan over voor dat ene geval; heb je die niet, herorden dan je tabel zodat de sleutel links komt te staan. Zo blijft de functie doen waar ze goed in is, en verspil je geen middag aan een formule die nooit had gekund.