VERT.ZOEKEN-functie (VLOOKUP) - Verticaal zoeken in Excel

Een van de meestgebruikte en nuttige functies, met een verbeterd alternatief...

Met de VERT.ZOEKEN-functie zoek je verticaal een waarde op in de eerste kolom, om een bijbehorende waarde uit een andere kolom te krijgen. Je kunt zoeken naar een exacte match, of een benadering.

VERT.ZOEKEN-functie voorbeeld

=VERT.ZOEKEN( C2; C5:E10; 3; ONWAAR)// cel D2, output: 7

In het voorbeeld hierboven zoeken we de waarde (student) DvW op uit cel C2.

We zoeken deze waarde in het gele bereik, C5:E10. Let erop dat de VERT.ZOEKEN-functie deze waarde ALTIJD in de eerste kolom zoekt.

We zijn geïnteresseerd in de waarde uit de derde kolom van het gele bereik (het cijfer van de student). Je kunt als input argument een "3" invoeren, of deze waarde uit een cel halen. (In dit geval staat de waarde ook in cel G5, maar de formule gebruikt "3").

In dit voorbeeld willen we een exacte match. We zoeken student DvW, en niets anders. Daarom moet het 4e input argument ONWAAR zijn.

Gebruik

De VERT.ZOEKEN-functie is populair en bijzonder nuttig, maar niet per se intuïtief in het gebruik.

Nog een voorbeeld, maar dit keer met gesorteerde data:

=VERT.ZOEKEN( C2; $C$6:$D$10; 2; ONWAAR)// cel D2, output: Gold

Een paar opmerkingen bij de VERT.ZOEKEN-functie:

• Het eerste input argument, de zoekwaarde, moet te vinden zijn in de meest linkerkolom van het tweede input argument, de tabelmatrix. In dit geval zoeken we dus de waarde van cel C2 in de meest linkerkolom van C6:D10. Als we bv. B6:D10 hadden gebruikt, dan wordt de waarde van C2 gezocht in kolom B, en dat willen we niet...
• Het derde input argument, kolomindex_getal, geeft aan uit de hoeveelste kolom van het tweede input argument de uiteindelijke output moet komen. In dit geval willen we het Level, dat in de tweede kolom van C6:D10 staat.
• Het vierde input argument geeft aan of je een benadering wilt (waarde WAAR, later meer), of een exacte match wilt (waarde ONWAAR). In dit voorbeeld willen we een exacte match.

Je ziet dat met name het tweede input argument, de tabelmatrix, extra gecontroleerd moet worden.

Laat je niet afleiden door de $-tekens in het tweede input argument, er had ook C6:D10 kunnen staan. De $-tekens maken van deze cellen een absolute verwijzing, wat handig is als de formule in andere cellen hergebruikt moet worden.

Exacte match vs (niet-exacte) benadering

Het laatste (optionele) input argument, benaderen, verdient extra aandacht, omdat het vaak fout gaat.

In de meeste gevallen wil je een exacte match, en geen benadering. Als je het laatste input argument niet definieert, gaat Excel er echter van uit dat je wilt benaderen... Let hier dus op.

Kijk naar het volgende voorbeeld:

=VERT.ZOEKEN( C2; $C$6:$D$10; 2; ONWAAR)// cel D2, output: #N/A
=VERT.ZOEKEN( C3; $C$6:$D$10; 2; WAAR)// cel D3, output: Silver

We hebben een variant met benaderen, en een zonder benaderen (oftewel een exacte match).

We zoeken de waarde 150 in de meest linkerkolom van de gele cellen. Deze waarde zit er niet tussen. Een exacte match bestaat dus niet.

Als we het laatste input argument, benaderen, op ONWAAR zetten, dan is de output #N/B (of #N/A in het Engels).

Als we echter benaderen op WAAR zetten, zoals in de tweede formule, dan krijgen we wel een waarde. In dit geval zoekt de functie net zo lang in de eerste kolom tot het een waarde tegenkomt die groter is dan 150. De functie neemt dan de rij met de hoogst mogeljke waarde die niet "te hoog" is. In dit geval dus de rij van 100 (Level "Silver"), want de rij erna is 180 en dit is hoger dan de waarde die we zoeken, 150.

VERT.ZOEKEN met benaderen werkt alleen goed als de data (oplopend) gesorteerd is. Als dit niet zo is, dan kunnen er onverwachte dingen uitkomen.

Verticaal zoeken (met benaderen) vs meerdere keer ALS

Kijk eens naar deze twee formules, die hetzelfde eindresultaat hebben:

=ALS( C2>=C10; D10; ALS( C2>=C9; D9; ALS( C2>=C8; D8; D7)))// cel D2, output: Silver
=VERT.ZOEKEN( C3; $C$6:$D$10; 2; WAAR)// cel D3, output: Silver

Beide varianten werken, maar de versie met verticaal zoeken is een stuk leesbaarder. En veel makkelijker te onderhouden, mocht je een keer een categorie wijzigen.

Meerdere keren ALS in elkaar nesten is eigenlijk nooit een goed idee; het is extreem foutgevoelig.

Zodra je merkt dat je de neiging krijgt 3 of meer ALS-functies in elkaar te nesten, kijk dan of de VERT.ZOEKEN-functie je kan redden. Dit zal niet in elke situatie zo zijn, maar vaak wel als je met gesorteerde tabellen te maken hebt.

Verticaal zoeken met meerdere criteria

Als je wilt zoeken met meerdere criteria, bijvoorbeeld uit meerdere kolommen, dan kan dit ook met VERT.ZOEKEN.

De truc is om dan een hulpkolom aan de linkerkant toe te voegen, en hierin te zoeken:

=VERT.ZOEKEN( D2& D3; B6:E10; 4; ONWAAR)// cel D4, output: 17

We hebben nu een hulpkolom toegevoegd die de twee kolommen ernaast "aan elkaar plakt". Het eerste input argument, zoekwaarde, passen we ook iets aan, door de twee zoekwaarden aan elkaar te plakken. We zoeken deze vervolgens op in de toegevoegde hulpkolom.

De oplossing met de hulpkolom is een beetje gekunsteld. Het kan veel eenvoudiger, door X.ZOEKEN te gebruiken.

Verticaal zoeken met X.ZOEKEN

Als je een nieuwere versie van Excel hebt, dan kun je als alternatief voor VERT.ZOEKEN in de meeste gevallen beter de X.ZOEKEN-functie gebruiken. De X.ZOEKEN-functie is intuïtiever, en minder foutgevoelig als je bv. kolommen toevoegt.

=X.ZOEKEN( C2; $C$7:$C$10; $D$7:$D$10)// cel D2, output: Gold

Deze formule is veel intuïtiever; Zoek C2 in C7:C10 en geef de bijbehorende match uit D7:D10.

Grootste voordeel van X.ZOEKEN is dat we geen kolomindex_getal hoeven te geven. Hierdoor kan de formule makkelijker horizontaal gekopieerd worden. Ook heeft X.ZOEKEN nog allerlei extra zoekopties via optionele input argumenten.

Veelgestelde vragen

Wat is de zoekwaarde bij verticaal zoeken?

De zoekwaarde is datgene dat je wilt zoeken. De zoekwaarde is het eerste input argument voor de VERT.ZOEKEN-functie. Het tweede input argument, de tabelmatrix, is waar je deze zoekwaarde wilt zoeken (zoekt altijd in de eerste kolom!)

Wat als verticaal zoeken niet lukt?

Er zijn een aantal veelvoorkomende redenen waar verticaal zoeken niet lukt, of onverwachte output geeft. In 98% van de gevallen is het een van deze:

• De zoekwaarde bestaat niet in de eerste kolom
• Verwijzing naar tabelmatrix (2e argument) is incorrect
• De input kolomindex_getal (input argument 3) is groter dan het aantal kolommen van de tabelmatrix
• De input kolomindex_getal klopt niet (meer)
• Je wilt een exacte match, maar hebt dit niet expliciet gezegd in het 4e input argument benaderen

Deze fouten ontstaan ook vaak na kopiëren van cellen met de functie erin, of wanneer er later een kolom wordt ingevoegd.

Wat is het verschil tussen VLOOKUP en XLOOKUP?

XLOOKUP (X.ZOEKEN in Nederlands) is een vernieuwd alternatief voor VLOOKUP (VERT.ZOEKEN in Nederlands). X.ZOEKEN heeft voorspelbaarder gedrag dan VERT.ZOEKEN, is makkelijker te kopiëren naar andere cellen, en heeft extra opties (bv van beneden naar boven zoeken, wat te doen als niets gevonden, etc.).

Waarom moet ik de VERT.ZOEKEN-functie kennen als ik ook de X.ZOEKEN-functie kan gebruiken?

X.ZOEKEN is een vrij nieuwe functie, die niet iedereen kent. Ook is deze versie pas beschikbaar in nieuwere versies van Excel.

Alle oudere Excelbestanden zullen daarom geen X.ZOEKEN bevatten, maar vaak wel VERT.ZOEKEN. Begrijpen hoe VERT.ZOEKEN werkt is dan wel essentieel.

Ook kan het zijn dat de mensen/collega's waar je bestanden mee deelt een oudere versie van Excel hebben die X.ZOEKEN niet ondersteunt.