Hoe VERT.ZOEKEN in Excel te gebruiken

hoe-te-gebruiken-vlookup-in-excel foto 1

VERT.ZOEKEN is een van de handigste functies van Excel en ook een van de minst begrepen. In dit artikel demystificeren we VERT.ZOEKEN aan de hand van een realistisch voorbeeld. We maken een bruikbare factuursjabloon voor een fictief bedrijf.

VERT.ZOEKEN is een Excel-functie. In dit artikel wordt ervan uitgegaan dat de lezer al een redelijk begrip heeft van Excel-functies en basisfuncties zoals SOM, GEMIDDELDE en VANDAAG kan gebruiken. In het meest voorkomende gebruik is VERT.ZOEKEN een databasefunctie, wat betekent dat het werkt met databasetabellen - of eenvoudiger, lijsten met dingen in een Excel-werkblad. Wat voor soort dingen? Nou ja, van alles. Mogelijk hebt u een werkblad met een lijst met werknemers, of producten, of klanten, of cd's in uw cd-verzameling, of sterren aan de nachtelijke hemel. Het maakt niet echt uit.



Hier is een voorbeeld van een lijst of database. In dit geval is het een lijst met producten die ons fictieve bedrijf verkoopt:

hoe-te-gebruiken-vlookup-in-excel foto 2

Meestal hebben lijsten zoals deze een soort unieke identificatie voor elk item in de lijst. In dit geval staat de unieke identificatie in de kolom Artikelcode. Opmerking: om de functie VERT.ZOEKEN te laten werken met een database/lijst, moet die lijstmoeteneen kolom hebben met de unieke identifier (of sleutel of ID), en die kolom moet de eerste kolom in de tabel zijn. Onze voorbeelddatabase hierboven voldoet aan dit criterium.

Het moeilijkste van het gebruik van VERT.ZOEKEN is precies te begrijpen waar het voor is. Dus laten we eerst kijken of we dat duidelijk kunnen krijgen:

VERT.ZOEKEN haalt informatie op uit een database/lijst op basis van een geleverd exemplaar van de unieke identifier.

In het bovenstaande voorbeeld zou u de functie VERT.ZOEKEN invoegen in een andere spreadsheet met een artikelcode, en het zou u ofwel de beschrijving van het corresponderende item, de prijs of de beschikbaarheid (de hoeveelheid op voorraad) teruggeven zoals beschreven in uw oorspronkelijke lijst. Welke van deze stukjes informatie zal het u teruggeven? Welnu, u mag dit beslissen wanneer u de formule maakt.

Als alles wat je nodig hebt één stukje informatie uit de database is, zou het veel moeite kosten om naar een formule te gaan met een VERT.ZOEKEN-functie erin. Normaal gesproken zou u dit soort functionaliteit gebruiken in een herbruikbare spreadsheet, zoals een sjabloon. Telkens wanneer iemand een geldige artikelcode invoert, haalt het systeem alle benodigde informatie over het bijbehorende artikel op.

Laten we hier een voorbeeld van maken: een factuursjabloon die we keer op keer kunnen hergebruiken in ons fictieve bedrijf.

Eerst starten we Excel, en maken we zelf een blanco factuur aan:

hoe-te-gebruiken-vlookup-in-excel foto 3

Dit is hoe het werkt: De persoon die het factuursjabloon gebruikt, vult een reeks artikelcodes in kolom A in en het systeem haalt de beschrijving en prijs van elk artikel op uit onze productdatabase. Die informatie wordt gebruikt om het regeltotaal voor elk artikel te berekenen (ervan uitgaande dat we een geldig aantal invoeren).

Om dit voorbeeld eenvoudig te houden, zoeken we de productdatabase op een apart blad in dezelfde werkmap:

In werkelijkheid is het waarschijnlijker dat de productdatabase zich in een aparte werkmap zou bevinden. Het maakt weinig uit voor de VERT.ZOEKEN-functie, die er niet echt om geeft of de database zich op hetzelfde blad, een ander blad of een geheel andere werkmap bevindt.

Daarom hebben we onze productdatabase gemaakt, die er als volgt uitziet:

hoe-te-gebruiken-vlookup-in-excel foto 5

Om de VERT.ZOEKEN-formule die we gaan schrijven te testen, voeren we eerst een geldige artikelcode in cel A11 van onze blanco factuur in:

hoe-te-gebruiken-vlookup-in-excel foto 6

Vervolgens verplaatsen we de actieve cel naar de cel waarin we informatie willen opslaan die door VERT.ZOEKEN uit de database is opgehaald. Interessant is dat dit de stap is die de meeste mensen verkeerd doen. Om het verder uit te leggen: we staan ​​op het punt een VERT.ZOEKEN-formule te maken die de beschrijving ophaalt die overeenkomt met de artikelcode in cel A11. Waar willen we deze beschrijving plaatsen als we hem krijgen? In cel B11 natuurlijk. Dus dat is waar we de VERT.ZOEKEN-formule schrijven: in cel B11. Selecteer nu cel B11.

hoe-te-gebruiken-vlookup-in-excel foto 7

We moeten de lijst vinden met alle beschikbare functies die Excel te bieden heeft, zodat we VERT.ZOEKEN kunnen kiezen en hulp kunnen krijgen bij het invullen van de formule. Dit vindt u door eerst op het tabblad Formules te klikken en vervolgens op Functie invoegen:

Er verschijnt een vak waarmee we een van de functies kunnen selecteren die beschikbaar zijn in Excel.

hoe-te-gebruiken-vlookup-in-excel foto 9

Om degene te vinden die we zoeken, kunnen we een zoekterm typen zoals lookup (omdat de functie waarin we geïnteresseerd zijn een lookup-functie is). Het systeem zou ons een lijst met alle opzoekgerelateerde functies in Excel terugsturen. VERT.ZOEKEN is de tweede in de lijst. Selecteer het en klik op OK.

hoe-te-gebruiken-vlookup-in-excel foto 10

Het vak Functieargumenten verschijnt en vraagt ​​ons om alle argumenten (of parameters) die nodig zijn om de functie VERT.ZOEKEN te voltooien. Je kunt deze box zien als de functie die ons de volgende vragen stelt:

  1. Welke unieke identificatie zoekt u op in de database?
  2. Waar is de databank?
  3. Welk stukje informatie uit de database, gekoppeld aan de unieke identifier, wilt u voor u laten ophalen?

De eerste drie argumenten worden vet weergegeven, wat aangeeft dat het verplichte argumenten zijn (de functie VERT.ZOEKEN is onvolledig zonder deze en zal geen geldige waarde retourneren). Het vierde argument is niet vet, wat betekent dat het optioneel is:

hoe-te-gebruiken-vlookup-in-excel foto 11

We zullen de argumenten in volgorde voltooien, van boven naar beneden.

Het eerste argument dat we moeten invullen, is het argument Lookup_value. De functie heeft ons nodig om te vertellen waar de unieke identifier (in dit geval de artikelcode) kan worden gevonden waarvan de beschrijving moet worden geretourneerd. We moeten de artikelcode selecteren die we eerder hebben ingevoerd (in A11).

Klik op het selector-pictogram rechts van het eerste argument:

hoe-te-gebruiken-vlookup-in-excel foto 12

Klik vervolgens eenmaal op de cel met de artikelcode (A11) en druk op Enter:

De waarde van A11 wordt ingevoegd in het eerste argument.

Nu moeten we een waarde invoeren voor het argument Table_array. Met andere woorden, we moeten VERT.ZOEKEN vertellen waar we de database/lijst kunnen vinden. Klik op het selector-pictogram naast het tweede argument:

hoe-te-gebruiken-vlookup-in-excel foto 14

Zoek nu de database / lijst en selecteer de volledige lijst -exclusief de kopregel. In ons voorbeeld staat de database op een apart werkblad, dus we klikken eerst op dat werkbladtabblad:

Vervolgens selecteren we de hele database, exclusief de kopregel:

...en druk op Enter. Het cellenbereik dat de database vertegenwoordigt (in dit geval ’Product Database’!A2:D7) wordt automatisch voor ons ingevoerd in het tweede argument.

Nu moeten we het derde argument invoeren, Col_index_num. We gebruiken dit argument om voor VERT.ZOEKEN op te geven welk stuk informatie uit de database, gekoppeld aan onze artikelcode in A11, we naar ons willen terugsturen. In dit specifieke voorbeeld willen we dat de beschrijving van het item naar ons wordt teruggestuurd. Als u op het databasewerkblad kijkt, ziet u dat de kolom Beschrijving de tweede kolom in de database is. Dit betekent dat we een waarde van 2 moeten invoeren in het vak Col_index_num:

hoe-te-gebruiken-vlookup-in-excel foto 17

Het is belangrijk op te merken dat we hier geen 2 invoeren omdat de kolom Beschrijving in de kolom B op dat werkblad staat. Als de database toevallig in kolom K van het werkblad zou beginnen, zouden we nog steeds een 2 in dit veld invoeren omdat de kolom Beschrijving de tweede kolom is in de reeks cellen die we hebben geselecteerd bij het specificeren van de Table_array.

Ten slotte moeten we beslissen of we een waarde moeten invoeren in het laatste VLOOKUP-argument, Range_lookup. Dit argument vereist een waarde waar of onwaar, of het moet leeg worden gelaten. Bij gebruik van VERT.ZOEKEN met databases (zoals 90% van de tijd het geval is), kan de manier om te beslissen wat in dit argument moet worden geplaatst als volgt worden beschouwd:

Als de eerste kolom van de database (de kolom die de unieke identifiers bevat) alfabetisch/numeriek in oplopende volgorde is gesorteerd, dan is het mogelijk om de waarde true in dit argument in te voeren, of het leeg te laten.

Als de eerste kolom van de database niet is gesorteerd, of in aflopende volgorde is gesorteerd, moet u de waarde false in dit argument invoeren

Omdat de eerste kolom van onze database niet is gesorteerd, voeren we false in dit argument in:

hoe-te-gebruiken-vlookup-in-excel foto 18

Dat is het! We hebben alle informatie ingevoerd die nodig is voor VERT.ZOEKEN om de waarde te retourneren die we nodig hebben. Klik op de knop OK en merk op dat de beschrijving die overeenkomt met artikelcode R99245 correct is ingevoerd in cel B11:

hoe-te-gebruiken-vlookup-in-excel foto 19

De formule die voor ons is gemaakt, ziet er als volgt uit:

hoe-te-gebruiken-vlookup-in-excel foto 20

Als we een andere artikelcode in cel A11 invoeren, zullen we de kracht van de VERT.ZOEKEN-functie gaan zien: De beschrijvingscel verandert om overeen te komen met de nieuwe artikelcode:

hoe-te-gebruiken-vlookup-in-excel foto 21

We kunnen een vergelijkbare reeks stappen uitvoeren om de prijs van het artikel terug te krijgen in cel E11. Merk op dat de nieuwe formule moet worden gemaakt in cel E11. Het resultaat zal er als volgt uitzien:

hoe-te-gebruiken-vlookup-in-excel foto 22

...en de formule ziet er als volgt uit:

hoe-te-gebruiken-vlookup-in-excel foto 23

Merk op dat het enige verschil tussen de twee formules is dat het derde argument (Col_index_num) is veranderd van een 2 in een 3 (omdat we gegevens willen ophalen uit de 3e kolom in de database).

Als we zouden besluiten om 2 van deze items te kopen, zouden we een 2 invoeren in cel D11. We zouden dan een eenvoudige formule in cel F11 invoeren om het regeltotaal te krijgen:

=D11*E11

... wat er zo uitziet ...

hoe-te-gebruiken-vlookup-in-excel foto 24

Het factuursjabloon invullen

We hebben tot nu toe veel geleerd over VERT.ZOEKEN. In feite hebben we alles geleerd wat we in dit artikel gaan leren. Het is belangrijk op te merken dat VERT.ZOEKEN behalve in databases ook in andere omstandigheden kan worden gebruikt. Dit komt minder vaak voor en wordt mogelijk behandeld in toekomstige How-To Geek-artikelen.

Ons factuursjabloon is nog niet compleet. Om het te voltooien, zouden we het volgende doen:

  1. We zouden de voorbeeldartikelcode uit cel A11 verwijderen en de 2 uit cel D11. Hierdoor zullen onze nieuw gemaakte VERT.ZOEKEN-formules foutmeldingen weergeven:
    hoe-te-gebruiken-vlookup-in-excel foto 25
    We kunnen dit verhelpen door verstandig gebruik te maken van de Excel-functies IF() en ISBLANK(). We veranderen onze formule van dit... =VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)...naar dit...=IF(ISBLANK(A11),,VLOOKUP(A11,'Product Database'!A2:D7 ,2,FALSE))
  2. We kopieerden de formules in de cellen B11, E11 en F11 naar de rest van de artikelrijen van de factuur. Merk op dat als we dit doen, de resulterende formules niet langer correct verwijzen naar de databasetabel. We kunnen dit oplossen door de celverwijzingen voor de database te wijzigen in absolute celverwijzingen. Als alternatief - en zelfs beter - kunnen we een bereiknaam maken voor de hele productdatabase (zoals Producten) en deze bereiknaam gebruiken in plaats van de celverwijzingen. De formule verandert van dit... =IF(ISBLANK(A11),,VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE))...naar dit... =IF(ISBLANK(A11),,VLOOKUP(A11) ,Producten,2,FALSE))...en kopieer vervolgens de formules naar de rest van de factuuritemrijen.
  3. We zouden waarschijnlijk de cellen vergrendelen die onze formules bevatten (of liever de andere cellen ontgrendelen), en vervolgens het werkblad beschermen, om ervoor te zorgen dat onze zorgvuldig opgebouwde formules niet per ongeluk worden overschreven wanneer iemand de factuur komt invullen.
  4. We zouden het bestand opslaan als een sjabloon, zodat het door iedereen in ons bedrijf opnieuw kan worden gebruikt

Als we ons echt slim zouden voelen, zouden we een database van al onze klanten in een ander werkblad maken en vervolgens de klant-ID gebruiken die in cel F5 is ingevoerd om automatisch de naam en het adres van de klant in de cellen B6, B7 en B8 in te vullen.

hoe-te-gebruiken-vlookup-in-excel foto 26

Als u wilt oefenen met VERT.ZOEKEN, of gewoon onze resulterende factuursjabloon wilt zien, kunt u deze hier downloaden.

Meer verhalen

Schakel YouTube-opmerkingen uit tijdens het gebruik van Chrome

Ben je het zat dat de reacties op YouTube vol godslastering of irritant zijn om naar te kijken? Nu kun je video's minus de reacties bekijken met de No YouTube Comments-extensie voor Google Chrome.

Kies de teksteditor die wordt gebruikt om de broncode in Internet Explorer te bekijken

Iedereen heeft een favoriete teksteditor die ze graag gebruiken bij het bekijken van of werken met broncode. Als u niet tevreden bent met de standaardkeuze in Internet Explorer 8, sluit u dan bij ons aan terwijl we u laten zien hoe u toegang tot uw favoriete teksteditor kunt instellen.

Aan de slag met Boxee

Boxee is een gratis Media PC-applicatie die draait op Windows, Mac en Ubuntu Linux. Met Boxee kunt u online video, muziek en foto's integreren met uw eigen lokale media en sociale netwerken. Vandaag gaan we Boxee en enkele van zijn functies nader bekijken.

Afbeeldingen en andere objecten centreren in Office 2007 en 2010

Soms kan het moeilijk zijn om een ​​afbeelding in een document te centreren door deze gewoon te slepen en te slepen. Vandaag laten we u zien hoe u afbeeldingen, afbeeldingen en andere objecten perfect centreert in Word en PowerPoint.

Het stemscherm van de Windows-browser biedt een webbrowserkeuze voor Europese gebruikers

Sinds maart mogen onze vrienden aan de andere kant van de vijver in Europa beslissen welke browser ze willen installeren met hun Windows-besturingssysteem. Vandaag dachten we dat we eens zouden kijken naar de stemkeuzes, sommige zijn bekend en van andere heeft u misschien nog nooit gehoord.

doubleTwist is een iTunes-alternatief dat meerdere apparaten ondersteunt

Er zijn veel iTunes-gebruikers, maar helaas kun je het niet met al je draagbare apparaten gebruiken. Vandaag bekijken we doubleTwist, waarmee u uw media kunt synchroniseren met een groot aantal draagbare apparaten en deze ook gemakkelijk kunt delen.

Extensiebestanden hacken om ze versie-compatibel te maken voor Firefox

Een bekend nadeel bij het gebruik van Firefox is het probleem met de compatibiliteit van extensies wanneer een nieuwe hoofdversie wordt uitgebracht. Of het nu gaat om een ​​nieuwe extensie die u voor het eerst probeert of een oude favoriet, we hebben een manier om die extensies weer voor u te laten werken.

Toegang tot uw favoriete RSS-feeds in Windows Media Center

Er zijn veel apps die u helpen bij het organiseren en bekijken van uw favoriete RSS-feeds. Als je veel abonnees hebt, kan het overweldigend zijn om achter een computer te zitten om ze allemaal te bekijken. Vandaag bekijken we hoe u ze vanaf de bank kunt openen met WMC.

Autoruns gebruiken om een ​​geïnfecteerde pc handmatig op te schonen

Er zijn veel anti-malwareprogramma's die je systeem van vervelende dingen zullen opschonen, maar wat gebeurt er als je zo'n programma niet kunt gebruiken? Autoruns, van SysInternals (recent overgenomen door Microsoft), is onmisbaar bij het handmatig verwijderen van malware.

Pre-paid Zune Card-punten inwisselen voor Zune Marketplace Media

Als je de maandelijkse kosten voor een Zune Pass niet wilt betalen, kun je een prepaid Zune-kaart kopen. Hier bekijken we hoe u de Zune-kaartpunten kunt inwisselen, zodat u muziek kunt krijgen voor uw Zune of Zune HD.