Verticaal zoeken | Uitleg VERT.ZOEKEN formule, gebruik en #N/A’s

Met de vert.zoeken formule wordt er, zoals de naam eigenlijk al zegt, verticaal naar een waarde gezocht. Dit kan handig zijn als je een lange lijst met bijvoorbeeld producten hebt en je hierbij een prijs nodig hebt.

Het is dezelfde formule als bij horiz.zoeken, alleen de as is hierbij omgedraaid.

Vert.zoeken (of VLOOKUP in de Engelse versie) is een Excel-functie om gegevens op te zoeken en op te halen uit een specifieke kolom in een tabel die je in Excel hebt staan.

Het is een van de functies die ik het meeste gebruik wanneer ik in Excel werk.

Vert.zoeken ondersteunt opzoeken naar exacte overeenkomsten, maar zoeken naar benadering. De “vert.” staat voor “verticaal”.

Het is belangrijk om te onthouden dat je alleen maar naar rechts toe kunt zoeken. Zoekwaarden moeten in een eerdere kolom van de tabel worden weergegeven, met de kolom waar je de waarde van wilt tonen aan de rechterkant van die kolom.

Ik wil altijd gelijk met bruikbare informatie aan de slag gaan wanneer ik een artikel schrijf, dus hier direct hoe je verticaal zoeken gebruikt:

Hoe gebruik je verticaal zoeken?

In de excel sheet hierboven zie je direct de belangrijke waarden die nodig zijn voor de formule.

Hierboven zoek je de waarde blauw in de eerste kolom van de tabel (Kleur), en zodra die waarde gevonden is dan geef je als resultaat de waarde die in de 2e kolom (Code) staat.

In dit geval “blauw”, opzoeken in de Kleur kolom met als resultaat “3” uit de Code kolom.

De formule is:

=VERT.ZOEKEN(E2;D4:E9;2;ONWAAR)

Sommige mensen vinden het lastig om de volgorde te onthouden van wat je in moet vullen in de formule omdat het een soort van omdenken is.

De structuur van de verticaal zoeken formule is als volgt:

=vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal; [benaderen])

• Zoekwaarde: Van welk ding wil je iets opzoeken?
• Tabelmatrix: In welke tabel wil je dat ding opzoeken?
• Kolomindex_getal: uit welke kolom wil je een waarde ophalen wanneer de formule je zoekwaarde in de eerste kolom is tegen gekomen?
• [benaderen]: Wil je alleen iets opzoeken als het ding exact tot op de letter voorkomt in de tabel, of wil je ook iets opzoeken als het ding misschien niet altijd exact in de tabel voorkomt maar ook als het ongeveer voorkomt

Je kunt natuurlijk de functiewizard gebruiken om elke waarde in te vullen, maar als je excel wat vaker gebruikt zul je merken dat het veel sneller is om deze onderdelen gewoon in de formulebalk in te typen. Onthoud alleen dat elk onderdeel van de formule wordt gescheiden door een ;

Paar dingen die hierboven opvallen en belangrijk zijn om goed te begrijpen wat er precies gebeurt:

• Ondanks dat benaderen op “ONWAAR” staat vindt de formule met de zoekwaarde “blauw” toch ook “Blauw” in de kolom, de formule is niet hoofdlettergevoelig.
• de zoekwaarde “Blauw” komt 2x in de tabel voor, maar de formule geeft het resultaat van de eerste keer dat het woord wordt gevonden en dus krijg je als resultaat 3 en niet 5.
• De kolom waarvan je de waarde als resultaat wilt ophalen (ook wel kolominexgetal genaamd) is kolom 2, niet kolom 1. Dit komt omdat de kolom waarin je je zoekwaarde opzoekt altijd kolom 1 is en de 2e kolom daarmee 2 is. Klinkt heel logisch maar vergeten mensen ook vaak.

Tabelmatrix en de meest linkse kolom

Het is belangrijk te weten dat vert.zoeken een opzoektabel nodig heeft met de zoekwaarden in de meest linkse kolom. De gegevens die je wilt ophalen (resultaatwaarden) kunnen in elke kolom aan de rechterkant van de eerste kolom staan.

Sommige mensen denken dat je zoekwaarde in de eerste kolom van je gehele tabel moet staan, maar dit hoeft niet zo te zijn, je kunt namelijk ook het bereik van je selectie aanpassen zodat je opzoekwaarde in de eerste kolom van je selectie staat.

Dit kan bijvoorbeeld ook:

De eerste kolom is kolom “Gebruik” maar het tabelbereik van je formule is kolom B t/m C. Je kunt nu prima een zoekwaarde hebben in kolom B en de waarde uit kolom C opzoeken.

Je kunt alleen niet meer de waarde uit kolom A opzoeken, je kunt niet naar links zoeken, alleen naar rechts toe.

Verticaal zoeken kan alleen naar rechts kijken.

Daarnaast haalt het gegevens op op basis van kolomnummer. Wil je waarden weergeven uit kolom D, dan is je kolomindex_getal in het geval van dit voorbeeld 3, kolom E is 4, enz.

Wanneer je vert.zoeken gebruikt, stel je dan voor dat elke kolom in de tabel genummerd is, beginnend vanaf de linkerkant.

Om een ​​waarde uit een bepaalde kolom te krijgen, geef je het juiste nummer op als de “kolomindex”.

Exact en bij benadering (benaderen WAAR of ONWAAR)

Vert.zoeken heeft twee instellingen om op te zoeken, exact en bij benadering, die worden geselecteerd door het 4e argument, [benaderen] genaamd.

Stel benaderen in op ONWAAR om exacte matching te forceren en WAAR voor een zo dicht mogelijke benadering. Ook kun je 0 of 1 gebruiken in plaats van ONWAAR en WAAR respectievelijk.

Belangrijk: benaderen staat standaard op WAAR, je hoeft dat gedeelte van de formule dus niet per se in te vullen, maar vul je niets in dan gebruikt de formule dus standaard bij benadering:

• =vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal) geeft overeenkomst bij benadering
• =vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal; WAAR) geeft overeenkomst bij benadering
• =vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal;1) geeft overeenkomst bij benadering
• =vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal;ONWAAR) geeft alleen exacte overeenkomsten
• =vert.zoeken(zoekwaarde; tabelmatrix; kolomindex_getal;0) geeft alleen exacte overeenkomsten

Voorbeeld formule met verticaal zoeken #1: Exacte overeenkomst

Eigenlijk gebruik je verticaal zoeken bijna altijd met een exacte match, dus ONWAAR of 0 in het laatste veld van de formule. Mocht je de verschillen nog niet helemaal goed snappen onthoud dan maar dat je altijd ONWAAR gebruikt.

Wanneer je iets wilt opzoeken heb je namelijk bijna altijd een unieke sleutel om als opzoekwaarde te gebruiken, bijvoorbeeld om de kleurcode te vinden in een lijst met kleuren:

De formule in F2 om de Code te vinden op basis van een exacte overeenkomst met de Kleur is:

=VERT.ZOEKEN(E2;B1:C6;2;ONWAAR)

• Zoek E2;
• in tabelbereik B1:C6;
• Geef dan als resultaat de waarde uit de 2e kolom (C);
• En alleen als de waarde exact wordt gevonden (ONWAAR)

Zwart komt niet voor in de lijst en dus geeft de formule een fout weer (#N/B).

Voorbeeld formule met verticaal zoeken #2: Match bij benadering

In gevallen waarin je de beste overeenkomst wilt, ongeacht of er een exacte overeenkomst is, wil je de optie bij benadering gebruiken.

In ons voorbeeld van de kleurcode is het niet zo zinvol:

Het antwoord 5 bij Zwart kunnen we niet zoveel mee, we willen exact weten wat de code is bij een bepaalde kleur.

Het kan bijvoorbeeld wel zinvol zijn bij het opzoeken van een waarde in een commissiestructuur waarbij commissiepercentages wijzigen naargelang de hoogte van een bedrag hoger wordt.

Hieronder willen we bijvoorbeeld een commissiepercentage opzoeken in de tabel. De opzoekwaarden staan in kolom R. In dit voorbeeld moeten we VERT.ZOEKEN gebruiken met bij benaderen op WAAR, omdat in de meeste gevallen nooit een exacte overeenkomst wordt gevonden met het exacte salesbedrag:

De VLOOKUP-formule in W2 is geconfigureerd om een ​​geschatte overeenkomst uit te voeren door het laatste argument in te stellen op WAAR:

=VERT.ZOEKEN(V2;$R$1:$S$6;2;WAAR)

Pro tip: let op de $ tekens in de formule, dit is om de tabelmatrix vast te zetten en het gemakkelijk te maken de formule naar beneden toe te kopieren naar de overige velden in kolom W waar je de formule ook wilt gebruiken.

VERT.ZOEKEN scant waarden in kolom R voor de opzoekwaarde. Als een exacte overeenkomst wordt gevonden, zal de formule deze gebruiken. Zo niet, dan zal VERT.ZOEKEN “een stap terug doen” en de waarde uit de vorige rij tonen.

Om dit goed te laten werken moeten de gegevens in de tabel in oplopende volgorde worden gesorteerd op kolom R met de opzoekwaarde:

Nu krijg je in kolom W de juiste waarden. Let er op dat bij het niet exact vinden de voorgaande waarde wordt opgezocht.

Zo is €150.000 dichter bij €189.420, toch is het commissie percentage dat is opgezocht 6% van de vorige waarde bij €57.480 en niet 9% ondanks dat de hoogte van het bedrag daar dichter bij ligt.

VERT.ZOEKEN en # N/A fouten

Wanneer je vert.zoeken gebruikt zul je onvermijdelijk de fout #N/A tegenkomen. De fout #N/A betekent gewoon “niet gevonden”. In de praktijk zijn er veel redenen waarom je deze fout kunt zien en hierboven heb ik er al eentje langs laten komen.

Enkele van de meest voorkomende fouten:

• De opzoekwaarde bestaat niet in de tabel (zoals hierboven besproken)
• De opzoekwaarde is verkeerd gespeld of bevat extra spatie (let goed op spaties die je per ongeluk typt)
• Matching staat op exact, maar zou bij benadering moeten zijn
• Het tabelbereik is niet correct ingevuld
• Je kopieert de formule van de ene naar de andere cel, maar de tabelverwijzing is niet vergrendeld met $ tekens (zoals in de pro tip hierboven besproken)

Een manier om de NA-fout te “vangen” is door de als.fout-functie te gebruiken:

Met die formule kun je aangeven wat er moet gebeuren als de formule een fout geeft. Dit kun je gebruiken bij vert.zoeken zoals ik ook heel vaak doe, maar is natuurlijk voor elk soort formule te gebruiken.

Je komt nu wel in het domein van de geneste formules (oeh, spannend! kun je dat op kantoor ook weer vertellen!)

De formule in F4 is:

=ALS.FOUT(VERT.ZOEKEN(E4;B1:C6;2;ONWAAR);”Onbekend”)

Nu staat er Onbekend in plaats van #N/A in het resultatenveld bij Zwart, omdat de waarde Zwart niet werd gevonden. Het bericht dat je wilt tonen kun je zelf aanpassen naar wens, en je kunt er zelfs weer een nieuwe formule in gebruiken.

Om niets terug te geven (d.w.z. om een ​​leeg resultaat weer te geven) wanneer vert.zoeken een fout teruggeeft, kunt je een lege string als volgt gebruiken:

=ALS.FOUT(VERT.ZOEKEN(E4;B1:C6;2;ONWAAR);””)

Opmerking: in veel gevallen is de #N/A nuttig omdat deze aangeeft dat er iets mis is. Zeker in het begin wil je hem dus laten staan en de fout oplossen in plaats van weg te werken.

Stappen voor het gebruik van verticaal zoeken

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

• Zoekwaarde: Hier wordt de waarde gekozen waar op gezocht moet worden
• Tabelmatrix: Dit is de plek waar je het bereik aan kan geven waarin gezocht wordt. Let op: De zoekwaarde moet wel in de eerste kolom staan
• Kolomindex_getal: Het cijfer in welke kolom het bevind
• Benaderen: Bij de benadering WAAR wordt er gezocht naar de meest overeenkomende waarde. Bij de benadering ONWAAR wordt er gezocht naar een exacte overeenkomst met de zoekwaarde. Dit is optioneel, geef je geen waarde op dan pakt hij automatisch benaderen WAAR

Voor de Peer wil je de prijs weten. Volg dan deze stappen in een lege cel:

1. Stap 1: Typ in een lege cel =VERT.ZOEKEN(
2. Stap 2: Selecteer de gewenste zoekwaarde, dit is B3. De formule is nu =VERT.ZOEKEN(B3
3. Stap 3: Plaats een puntkomma om naar het volgende argument te gaan. De formule is nu =VERT.ZOEKEN(B3;
4. Stap 4: Selecteer hier de tabelmatrix, oftewel het bereik waarin gezocht moet worden, dit is A1:C3. De formule is nu =VERT.ZOEKEN(B3;A1:C3
5. Stap 5: Plaats een puntkomma om naar het volgende argument te gaan. De formule is nu =VERT.ZOEKEN(B3;A1:C3;
6. Stap 6: Selecteer nu het kolomindex_getal, de kolom waarin de waarde staat die weergegeven dient te worden, dit is 3 (C). De formule is nu =VERT.ZOEKEN(B3;A1:C3;3
7. Stap 7: Plaats een puntkomma om naar het volgende argument te gaan. De formule is nu =VERT.ZOEKEN(B3;A1:C3;3;
8. Stap 8: Geef aan dat de waarde exact overeen moet komen, kies dus voor ONWAAR. De formule is nu =VERT.ZOEKEN(B3;A1:C3;3;ONWAAR
9. Stap 9: Sluit de formule af met een haakje. Dit is de uiteindelijke formule met uitkomst €2. =VERT.ZOEKEN(B3;A1:C3;3;ONWAAR)

Opmerking

• Als je bij benaderen voor WAAR kiest, dan dient de eerste kolom van de tabel in oplopende volgorde gesorteerd te zijn.
• Bij benaderen WAAR wordt, als de zoekwaarde niet gevonden wordt, de grootste waarde die lijkt op de zoekwaarde als resultaat getoond.
• Als de zoekwaarde een waarde is die niet gevonden kan worden, wordt de foutmelding #N/B getoond.
• Als je bij benaderen hebt gekozen voor ONWAAR, kun je het vraagteken (?) of sterretje (*) gebruiken in je zoekwaarde. Een sterretje (*) vervangt een willekeurige tekenreeks, terwijl een vraagteken (?) één willekeurig teken vervangt.

Waar komt vert.zoeken vandaan?

Vert.zoeken is de afkorting van verticaal zoeken. Je hebt ook een horiz.zoeken, die horizontaal zoekt.

Het resultaat wordt niet altijd correct weergegeven, omdat Excel standaard uitgaat van een niet exacte uitkomst, dit moet dus zelf veranderd worden. Hieronder vindt je een voorbeeld.

Handige weetjes bij VERT.ZOEKEN

• Vert.zoeken zoekt alleen rechts vanaf de zoekwaarde. De zoekwaarde moet dus altijd voor de opgezochte kolom staan.
• Het eerste gevonden resultaat wordt weergegeven.
• Het maakt niet uit of je hoofdletters of kleine letters gebruikt, hier wordt geen rekening mee gehouden.
• Let op als je een kolom invoegt, je vert.zoeken formule klopt dan niet meer.
• Bij een exacte overeenkomst kan de fout #N/B opspelen, als de zoekwaarde niet gevonden wordt. Gebruik ALS.FOUT of ALS.NB om de waarde te verbergen.
• Als er getallen zijn die worden opgemaakt als tekst kan dit ook leiden tot #N/B. Gebruik ALS.FOUT of ALS.NB om de waarde te verbergen.