Verticaal zoeken (vLookup)

Excel | Verticaal zoeken (vLookup)

Elke dag weer, ontvang ik vragen over hoe de functie “Verticaal zoeken”, in het engels “vLookup”, nou precies werkt. Ik kan deze formule in Excel inmiddels wel dromen (doe ik niet gelukkig). Ik kan mij voorstellen dat de mensen die niet elke dag met Excel werken ‘Verticaal zoeken’ toch nog wat lastig vinden. Daarom help ik jullie er nog even doorheen door middel van een aantal stappen. Voor het gemak zet ik de Engelse vertaling voor de Excel termen tussen haakjes. (“Term”)

 


Voorbeeld

Je hebt in een Excel overzicht 5 klanten staan, waarvan je uit een ander Excel overzicht bijhorende informatie wilt zoeken zonder alles over te tikken of te filteren. Bij een aantal regels is het werk nog wel te overzien, het wordt een ander verhaal zodra je bij honderden of duizenden records informatie moet verzamelen. Is dat niet makkelijker te doen? Ja zeker, via “verticaal zoeken” of ook wel “vLookup” genoemd.Excel 2007 : vLookup | Verticaal zoeken

Stel..  je hebt alle NAW gegevens maar je zou ook graag alle bijhorende email adressen van deze klanten in je bestand willen hebben. Hoe doen we dit dan?  In mijn voorbeeld gebruik ik een 5-tal fictieve klanten en een 8-tal fictieve email adressen. In het eerste tabblad (“sheet”) genaamd: “Klanten”, staan 5 klanten waarvan ik het email adres zou willen weten. Deze staan vermeld in een ander bestand (“file”) of tabblad (“sheet”).

  • Tabblad (“sheet”) Klanten is als volgt opgebouwd:
    • Kolom A : Klant nr.
    • Kolom B : Naam
    • Kolom C : Postcode
    • Kolom D : Plaats

Niet geheel toevallig staan in het 2de tabblad (“sheet”) genaamd: “Email adres”, de email adressen en de bijhorende klantnummers. Zoals je ziet staan de email adressen door elkaar net als de Nr’s uit tabblad (“sheet”) Klanten.

  • Tabblad (“sheet”) .Email adres is als volgt opgebouwd :
    • Kolom A : Klant nr.
    • Kolom B : Email adressen

 

Daar gaan we..

  • Zorg ervoor dat je met een soort gelijke situatie start.
  • Maak in tabblad (“sheet”) Klanten, een extra kolom (E) aan en geef deze de naam “email”.
  • Selecteer cel E2 en voeg een functie toe. (“Insert function”)

Excel 2007 : Insert Function

  • Zoek en kies “Verticaal zoeken” (“vLookup”).

excel2007_vlookup

  • Vul het eerste veld in genaamd: Zoekwaarde (“Lookup Value”). In dit veld vul je in welke waarde je wilt zoeken.
    • Dit kan je op 2 manieren doen:
      • Hard intikken zoals: “1008”
      • Cel verwijzing zoals: $A2
  • Wij gaan hier natuurlijk voor de cel verwijzing, selecteer cel A2.
    De reden dat wij cel A2 selecteren is omdat dit het enigste veld is dat in beide tabbladen (“sheets”) aanwezig is. Zonder een unieke waarde, is Verticaal zoeken (“vLookup”) niet mogelijk.

Geheugensteuntje “Zoekwaarde = Wat zoek je ?”

 

  • Vul het tweede veld in genaamd: Tabelmatrix (“Table Array”). Vul hier het zoekgebied in. In dit voorbeeld is dat tabblad “Email adres” kolom A tot en met B. Selecteer het zoekgebied

Let op dat je in gevallen met meerdere kolommen, alle kolommen selecteert. Voorbeeld: Nr. staat in Kolom B en het email adres in kolom G, dan selecteer je dus B tot en met G. Niet alleen kolom B en G, ook de tussenliggende.

Geheugensteuntje “Tabelmatrix = Waar moet ik zoeken?”

 

  • Vul het derde veld in genaamd: kolomindex getal (“Col Index Number”). Vul hier het aantal kolommen in.

Met aantal kolommen wordt bedoeld: Het kolomnummer in de tabelmatrix waaruit de overeenkomstige zoekwaarde moet worden geleverd.
Voorbeeld: Nr. staat in Kolom B en het email adres in kolom G. Dit zijn vanaf B tot en met G, 6 kolommen.

 

  • Vul het vierde veld in genaamd: Benaderen (“Range Lookup”). Hier vul je een logische waarde is. WAAR (“True”) of ONWAAR (“False”). Vul hier ONWAAR in.
    • Als benaderen WAAR (“True”) is of word weggelaten, word er een exact of een niet-exact overeenkomende waarde gevonden. Wanneer er geen exacte overeenkomst word gevonden, word de volgende hoogste waarde die kleiner is dan zoekwaarde als resultaat gegeven.
    • Als benaderen ONWAAR (“False”) is, word er alleen naar een exacte overeenkomst gezocht. In dit geval hoeft u de waarden in de eerste kolom van tabelmatrix niet te sorteren. Wanneer er twee of meer waarden in de eerste kolom van tabelmatrix overeenkomen met de zoekwaarde, word de eerst gevonden waarde gebruikt. Wanneer er geen exacte overeenkomst wordt gevonden, resulteert de functie in de foutwaarde #N/B.
  • Als je het goed invult ziet het er zoals hieronder uit. Druk vervolgens op OK.

Excel 2007 : vLookup | Verticaal zoekenExcel 2007 : vLookup | Verticaal zoeken

 

  • Sleep de formule uit cel E2 naar beneden zodat deze ook daaronder wordt toegepast. Hieronder een voorbeeld van hoe het er uiteindelijk uit komt te zien.

Excel 2007 : vLookup | Verticaal zoeken

 

Gefeliciteerd je hebt nu Verticaal zoeken (“vLookup”) toegepast!

Zijn er toch nog problemen? Laat dan hieronder een reactie achter of plaats een bericht in het forum.


37 Reacties

  1. Cees Wijn
    | Beantwoorden

    Beste Danny,
    Ik heb een bestand opgebouwd in een aparte sheet. Dit bestand bestaat uit twee kolommen (A en B) en zal uiteindelijk een groot aantal artikelen gaan bevatten. In kolom A staat het artikelnummer en in kolom B de artikelomschrijving. In een andere sheet heb ik een factuur gebouwd. Met welke formule kan ik zorgen dat zowel het artikelnummer als de artikelomschrijving vanuit het bestand worden overgenomen in de sheet van de factuur..??

    • Danny
      | Beantwoorden

      Hallo Cees, ik doe even de aanname dat je de factuur in zijn geheel in excel hebt gemaakt en deze dus zelf invult. Als je de artikel omschrijving wilt overnemen uit de lijst met artikel gegevens, dan kan je dit doen met Verticaal Zoeken. Je hebt hier voor wel iets nodig om op te zoeken. In dit geval kan je daar het artikelnummer voor gebruiken. Deze dien je op de factuur of buiten het printbereik van de factuur te plaatsen zodat er iig gezocht kan worden naar ‘iets’. Je kan, om t mooi te maken, ook een dropdown menu maken die is gebaseerd op de artikelnummers. Hierdoor hoef je dus nooit iets over te typen. Hoe je een dropdown menu maakt heb ik beschreven op deze site (heb de url zo even niet bij de hand).

      Heb ik jouw vraag zo beantwoord?

  2. Elias
    | Beantwoorden

    Hoi Danny,

    Het lukt me niet. ik doe blijkbaar toch iets verkeerds.
    waar kan ik het naar toe mailen?

  3. Elias
    | Beantwoorden

    Hoi Danny, ik heb op beide tabbladen dezelfde namen vermeld zodat ik verticaal kan zoeken.
    ik doe blijkbaar nog steeds iets verkeerds, wat ik krijg steeds een ongeldige melding te zien.
    kan je mij helpen?

  4. Esthel
    | Beantwoorden

    Dank! Je legt het perfect uit. Is in 1 keer gelukt (en dan werk ik met Office 2013…). Ik heb gelijk even een pdf van de pagina gemaakt als naslagwerk.

    Nog een aanvulling: soms is het niet wenselijk dat je beide tabbladen laat staan. Als je echter het tabblad ‘Email adres’ zou verwijderen dan ben je ook alle data (de e-mailadressen) kwijt in het tabblad ‘Klanten’. Dit is echter simpel op te lossen:
    – voeg op de pagina van het tabblad ‘Klanten’ een nieuwe kolom toe (kolom F).
    – Selecteer de kolom met de e-mailadressen (kolom E) en kopieer deze.
    – Selecteer vervolgens de nieuw ingevoegde kolom (kolom F) en kies voor Paste – special.
    – Kies voor Values en klik op enter.
    – Verwijder vervolgens kolom E. Als het goed is blijft de data in Kolom F staan.
    – Vervolgens kun je ook het tabblad ‘Email adres’ verwijderen.

    • Danny
      | Beantwoorden

      Ja dat klopt inderdaad. Soms is het nodig om de berekende waarde als een ‘harde waarde’ op te slaan.
      Bedankt voor je bijdrage Esthel. (:

  5. Gerard
    | Beantwoorden

    Ik gebruik Vlookup vaak, maar heb toch in vraagje. Ik markeer in de bronbestanden vaak cellen met een bepaalde kleur. Na het gebruik van V-lookup zijn de kleuren uit deze cellen weg. Is er een mogelijkheid dat dit meegenomen wordt in het nieuwe bestand?

    • Danny
      | Beantwoorden

      Ja kan de kleuren laten staan door alleen de formule te plakken / te kopiëren en niet de waarde / format van de cel. Een andere optie zou zijn om met conditionele opmaak cellen automatisch een kleur te geven.

  6. sharella sambre
    | Beantwoorden

    ik heb ook alles geprobeert maar er komt niks uit ….begon mijn keel uit te hangen….

  7. Theo
    | Beantwoorden

    Ik krijg met Verticaal zoeken niet een waarde in de cel te staan maar de formule. Waar kan dat aan liggen?

    • Danny
      | Beantwoorden

      Dan is waarschijnlijk de cell geformatteerd als tekst. Als je dit omzet naar algemeen of iets anders dan tekst moet het vast goedkomen. (:

      • Theo
        | Beantwoorden

        Danny, dat had ik al geprobeerd maar het probleem blijft.

        • Danny
          | Beantwoorden

          En als je nu in de formulebalk aan het einde van de formule een enter geeft? 😉
          Dat wilt nog wel eens helpen als het format wijzigt nadat je er iets hebt getypt.

  8. Marion
    | Beantwoorden

    Toevoeging aan bovenstaande – error message zegt dat “Cannot find the value in the VLOOKUP valuation”…terwijl de value er wel is en in de aangegeven array zit……

  9. Marion
    | Beantwoorden

    Het lukt me om de formule in te vullen en dan gaat het een hele poos goed. Maar dan ineens pakt hij de waarde van de voorgaande cel en blijft die in mijn kolom invoeren al staat er in de array waar ik naar refereer een heel andere waarde?? Ik snap niet waarom het niet consequent goed gaat. Enig idee???

    • Danny
      | Beantwoorden

      Dat kan ik zo niet beoordelen. Dan heb ik meer informatie van je nodig. Je kan het bestand waar het om gaat uploaden (zie menu). Dan kijk ik er even voor je naar. Je hoeft niet hele het bestand te sturen, graag alleen het deel waar het omgaat. (:

  10. Wen
    | Beantwoorden

    Het is gelukt, bedankt!

  11. Ivy
    | Beantwoorden

    Hi Danny,

    Het lukt mijn ook niet, en geloof me heb op alle manieren geprobeerd, of ik begrijp het niet het ;(

    Laat zeggen ik wil in 2 sheet werken, allebei sheets hebben verschillende info’s van referentie nummers, style, kleur, maat ean# etc. ik wil een EAN # zoeken van ene sheet naar de andere. Hoe doe ik dat?
    Moet ik trouwens ook een insert colom doen om mijn autenthiek ean# te krijgen?

    Ik hoor het heel graag,

    Groetjes

    Ivy

    • Danny
      | Beantwoorden

      Hallo Ivy,

      Ik begrijp niet zo goed wat je bedoelt. Als je wilt kan je een voorbeeld uploaden, dan kijk ik waar het mis gaat.

      Uploaden kan je met deze link: ‘Uploaden

      Gr Danny

  12. wen
    | Beantwoorden

    Danny, mogelijk kan je mij ook helpen, verticaal zoeken gaat goed als ik de info uit 1 ander bestand haal.
    nu zou ik graag hetzelfde willen zoeken maar dan uit 2 andere bestanden.
    dus dat hij zelf zoekt in welk van de 2 bestanden het staat en dit overneemt.

    kun je me hierbij helpen

    • Danny
      | Beantwoorden

      Hallo Wen,

      In theorie zou dat moeten kunnen met de functie IF (ALS).

      ALs waarde is 1 dan zoeken in bestand X
      Als waarde is 2 dan zoeken in bestand Y.

      Ik denk wel dat beide bestanden open moeten staan om het goed te laten werken. Dat zou je even moeten testen.

  13. May
    | Beantwoorden

    Heel goed en duidelijk uitgelegd, maar bij mij lukt het nog niet.
    Ik denk dat het niet kan met de excel bestanden waar ik mee werk 🙁

    Ik zal het zo duidelijk mogelijk proberen uit te leggen..
    In het ene bestand staan zogenoemde “personeelskaarten”, met daarin alle gegevens van de medewerkers.
    In het andere bestand moeten de namen, geboorteplaats, geboorteland en geboortedatum komen van personen die meedoen cursus X. (alle medewerkers uit bestand 1 doen mee).

    Omdat je schreef: “Zorg dat je met een soortgelijke situatie start”, denk ik dat het niet lukt. De bestanden zijn totaal verschillend.
    Als je toch nog een tip heb of een voorbeeld wil hoor ik het graag.

    • Danny
      | Beantwoorden

      Hallo May, de belangrijkste vereiste van verticaal zoeken is dat je in beide bestanden een unieke kenmerk hebt waarop je kan zoeken. Als dat ontbreekt dan werkt Verticaal zoeken inderdaad niet.

      • May
        | Beantwoorden

        In beide bestanden komt “naam”, “geboortedatum”, en “geboorteplaats” voor.
        Verder staat er in het eerste document ook informatie die niet nodig is, zoals geslacht, leeftijd en adres. De indeling van het eerste bestand is dus heel anders de simpele tabel uit bestand 2. Voor het ene personeelslid zijn meerdere rijen gebruikt dan voor de ander, waardoor bijvoorbeeld ook niet te zeggen is dat na iedere 30ste regel de Naam van iemand staat.

        Ik probeer het steeds maar krijg foutmeldingen: #VERW! , #N/B , #WAARDE!

  14. Erica
    | Beantwoorden

    Dank je wel, eindelijk, na vele experts te hebben geraadpleegd, is het dan toch nog gelukt!!

  15. noora
    | Beantwoorden

    heel duidelijke uitleg, eindelijk gelukt! dank je wel.

  16. Babs
    | Beantwoorden

    beste Danny, ik werk al met vlookup maar stuit op een probleem. Ik verlies gegevens bij het gebruik van deze functie,
    bijv. ik heb 700 studenten die zich ingeschreven hebben voor een tentamen, vanuit die lijst werk ik. dan komen er allerlei deelcijfers, als ik die in ga voegen, en er zijn meer studenten die uiteindelijk toch het tentamen gedaan hebben, kan ik dat nergens meer terugvinden, dus krijgen ze geen cijfer. hoe kan ik die “verloren” gegevens tevoorschijn halen?

    • Danny
      | Beantwoorden

      Hoi babs, dat klinkt niet goed nee! Ik weet niet of dit met verticaal zoeken te maken heeft. Ik moet daarvoor precies weten wat je doet. Miss een idee om een voorbeeld bestand naar mij te mailen? Dan kijk ik er morgen even naar! Ik heb ons gesprek even naar het forum geplaatst dan kunnen wij daar even verder kijken. Gr. Danny

      http://www.dannybood.nl/Forum/T/verlies-van-gegevens/

  17. Brian
    | Beantwoorden

    Hoi Danny,

    Ik ben momenteel bezig met vlookup in 2 excel bestanden heb je daar toevallig ook een uitleg/formule voor ?

    gr Brian

    • Danny
      | Beantwoorden

      Nee maar inprincipe moet dit wel kunnen met de standaard formule zolang je beiden bestanden maar eerst opslaat.

  18. D. Verheek Vonk
    | Beantwoorden

    Het eerste deel kan in prima volgen en ik begrijp ook het waarom. Dus dat is al een plus. Echter ik heb 2 tabs: tab 1 die wil ik vullen met data vanuit tab 2. Tab 2 heeft meerdere kolommen met informatie welke ik naar tab 1 wil overbrengen. Echter de zoekwaarde staat niet in tab 1.
    Voorbeeld: Artikelnummers met artikelgegevens in tab 1.
    Tab 2: Artikelnummer (terug te vinden in tab 1), artikel ouderdom kolom B, C, D (niet terug te vinden in tab 1).
    Hoe krijg ik die data nu in tab 1?

    • Danny
      | Beantwoorden

      Het is niet helemaal duidelijk of er nu wel of niet een unieke waarde aanwezig is. Als ik het goed begrijp dan is de unieke waarde in jouw bestand het “Artikelnummer”. Je kan alleen verticaal zoeken in verschillende tabbladen zolang er een unieke waarde (artikelnummer) in beide tabbladen aanwezig is. Als er in het eerste tabblad wel een uniek waarde (artikelnummer) aanwezig is maar in het tweede tabblad niet, dan zal het niet werken.

      Ik heb jouw situatie nagebootst als er wel in beide tabbladen een uniek waarde (artikelnummer) aanwezig is.
      De formule in tab1 B2 is:
      =VLOOKUP(A2;’TAB2′!A:B;2;FALSE)
      =VERT.ZOEKEN(A2;’TAB2′!A:B;2;ONWAAR)

      Voorbeeld

      Kan je hier iets mee?

  19. Monique
    | Beantwoorden

    Eindelijk gelukt!!

Heb je nog opmerkingen of vragen? Je kunt ze in de box hieronder of op het forum kwijt.