Share
EEN VLOOKUP, afkorting van "vertical lookup" is een formule in Microsoft Excel om gegevens uit twee lijsten met elkaar te matchen. In plaats van te springen tussen spreadsheets en uw overeenkomende gegevens in te tikken, kunt u een VLOOKUP-formule schrijven om het proces te automatiseren.
Het kammen van twee lijsten is een perfecte situatie om een VERT.ZOEKEN te gebruiken. Aan de linkerkant (in de bovenstaande afbeelding) hebben we werknemersverschuivingsinformatie. We willen de functie van de werknemer toevoegen aan de ploegendata. Met een afzonderlijke lijst van medewerkers en hun functietitels kunnen we een VLOOKUP-formule schrijven om de titel in een opzoeklijst in te voeren.
Een succesvolle VLOOKUP heeft drie dingen nodig:
VLOOKUP is een Microsoft Excel-formule die essentieel is voor het werken met meerdere gegevenssets. In deze zelfstudie leer ik je hoe je deze kunt beheersen en gebruiken.
Voorbeeld VLOOKUP-formule gebruikt om personeelsgegevens op te zoeken.Met behulp van het bovenstaande voorbeeld heb ik nu een VLOOKUP-formule geschreven die de ID van de werknemer opzoekt en de functienaam in de ploegendata invoegt. Omdat beide werkbladen een werknemer-ID hebben, kan Excel de overeenkomende functienaam opzoeken. Het beste deel van VERT.ZOEKEN is dat ik nu dezelfde formule kan slepen en elke unieke functienaam opzoekt.
Voordat we verdergaan, moet je ervoor zorgen Download de gratis bijlage om mee te volgen. Het is een voorbeeld van een spreadsheetwerkmap die ik heb gemaakt en die we gebruiken om door deze zelfstudie te lopen.
Raadpleeg de onderstaande screencast voor de snelste manier om de basisprincipes van de VLOOKUP-formule te leren. De video doorloopt verschillende voorbeelden van de VLOOKUP-formule, met behulp van de voorbeeldwerkmap.
Blijf lezen om door de geschreven instructies te lopen en leer enkele aanvullende technieken die niet in de screencast worden behandeld.
Gebruik de tabbladen "Ingrediëntenorders" en "Leverancierslijst" voor dit Excel VLOOKUP-voorbeeld.
Laten we zeggen dat we een restaurant beheren en onze wekelijkse bestellingen plaatsen bij leveranciers. De koks hebben ons een lijst met ingrediënten te bestellen gegeven, en we moeten informatie over de leverancier invoegen.
Er zijn drie onderdelen die we voor elke bestelling moeten toevoegen:
In deze werkmap zijn er twee tabbladen:
Bovenaan staan onze bestelgegevens, die u vindt op het tabblad 'Ingrediëntenorders'. Hieronder vindt u de "Leverancierslijst", die zal fungeren als onze opzoeklijst.Het gemeenschappelijke veld tussen de twee tabellen is de Ingrediënt tab. Laten we het gebruiken om elk van de drie velden op te zoeken en het aan de bestellijst toe te voegen.
Op de Bestelling van ingrediënten tab, laten we in de eerste lege leverancierscel klikken, F5, en druk op het gelijkteken om de VLOOKUP-formule te starten. Typ vervolgens " VLOOKUP ( " om de formule te starten.
= VERT.ZOEKEN (
Onthoud dat onze primaire sleutel - het stuk gegevens dat in beide lijsten voorkomt - het ingrediënt, dus we gebruiken het voor de lookup. Klik op een cel B5, of typ het in de formule. Voeg vervolgens een komma toe na "B5" zodat we het volgende deel van de formule kunnen invoeren.
= VERT.ZOEKEN (B5,
Nu moeten we de formule onze opzoeklijst geven. Klik met de formule nog steeds op de Leverancierslijst tab. Klik nu op cel A3 en klik en sleep om te markeren en te selecteren A3 naar G13, de hele opzoektabel. Zorg ervoor en druk op de F4-toets op uw toetsenbord om de formule een absolute referentie te maken (hierover later meer). Voer ten slotte nog een komma in.
Wijs Excel naar de opzoeklijst.Nadat u de komma voor de opzoekcel heeft ingevoerd, wisselt u van tab en wijst Excel naar de opzoeklijst. Klik en sleep tussen cellen A3 en G3 om de gegevens te selecteren om op te zoeken. Zorg ervoor en druk op F4 op je toetsenbord tijdens deze stap om een absolute referentie te maken, die de cellen vergrendelt om te gebruiken voor de lookup.
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,
Vervolgens moeten we Excel laten weten uit welke kolom we moeten kiezen. Onthoud dat ons eerste item om in te voegen het Leverancier naam, die zich in de tweede kolom van de opzoeklijst bevindt. Voeg het nummer toe 2 naar de formule om te trekken uit de tweede kolom van de lookup en een andere komma.
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,2,
Ten slotte voegen we FALSE toe voor een exacte opzoeking en sluiten dan de haakjes:
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,2, FALSE)
Onze formule werkt perfect en trekt de leverancier voor de aardappelen aan. Het goede nieuws is dat we deze formule niet steeds opnieuw hoeven te schrijven - dubbelklik gewoon in de rechter benedenhoek van de cel F5 (je ziet het kleine groene vak op de hoek van de cel) Om de formule uit te breiden (zoals hierboven getoond).
De formule werkt perfect! Oké, laten we nu verdergaan met het ophalen van de gegevens voor de andere twee velden: het telefoonnummer van de leverancier en de bezorgdag.
Omdat we een absolute referentie gebruikten, kunnen we in principe dezelfde formule opnieuw gebruiken die we met een kleine aanpassing hebben geschreven. Laten we het volgende telefoonnummer van de leverancier toevoegen.
We beginnen met het kopiëren en plakken van de cel F5 (onze leverancierscel) naar cel G5. Ik gebruik meestal alleen de sneltoetsen op het toetsenbord Ctrl + C en Ctrl + V om de hele cel te kopiëren en te plakken.In eerste instantie zal dit niet werken, en je ziet een N / A in de cel.
Lookup kopiëren en plakken met aanpassingen in Excel.Het is veel gemakkelijker om onze formule in een andere cel te kopiëren en te plakken, maar het vereist wat aanpassingen. In eerste instantie kijkt Excel naar cel C5 voor de zoekopdracht, maar we moeten dit aanpassen aan "B5" in de formulebalk. Zodra we dat doen, zal de lookup werken - bijna.
We moeten naar de formulebalk gaan en het eerste deel van de formule wijzigen van C5 terug naar B5. Toen we de Formule voor één kolom, Excel heeft andere delen van de formule bijgewerkt. We kregen een "N / A" omdat Excel probeerde de hoeveelheid (kolom C) aan onze opzoeklijst te koppelen, maar onze opzoeklijst bevat niet de hoeveelheid.
Tot nu toe zou onze formule dus moeten zijn:
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,2, FALSE)
Merk echter op in de bovenstaande schermafbeelding dat we de verkeerde bit gegevens in de kolom "Telefoon" trekken. We trekken nog steeds de tweede kolom in de opzoeklijst met de "2" in de formule. We moeten dit wijzigen naar het kolomnummer met de telefoon van onze leverancier.
Laten we teruggaan en de opzoeklijst bekijken. U zult zien dat het telefoonnummer in de 7e kolom in de opzoeklijst staat. Laten we teruggaan naar onze Excel-formule en de lookup bijwerken om uit de 7e kolom te halen.
We moeten de "2" in onze Excel-formule bijwerken naar een "7" om uit de 7e kolom in onze opzoeklijst te komen.Alles wat we moeten doen is het kolomgedeelte van onze opzoeklijst bijwerken van een "2" naar een "7" en het werkt nu geweldig!
Merk op dat de telefoon van de leverancier goed werkt nu we onze formule hebben bijgewerkt om de 7e kolom uit de opzoeklijst te gebruiken. Sleep de formule nu naar beneden om deze voor alle cellen bij te werken.Onze definitieve formule voor het opzoeken van het telefoonnummer van de leverancier:
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,7, FALSE)
Laten we tot slot werken in de kolom Leveranciersleveringsdag. Kopieer en plak de cel G5 naar cel H5. Nogmaals, we moeten onze formule verbeteren door te veranderen C5 terug naar B5 om het ingrediënt als de primaire sleutel te gebruiken. Werk vervolgens de "7" bij naar een "5" om de vijfde kolom uit onze opzoektabel te gebruiken.
Uiteindelijke opzoekformule voor eindleveranciers toegepast.Onze definitieve formule voor de leveringsdag van de leverancier:
= VLOOKUP (B5, 'Leverancierslijst'! $ A $ 3: $ G $ 13,5, FALSE)
Dat is het! We hebben in feite één formule geschreven en geknepen om alle gegevens in te voeren die we nodig hebben om onze volgende bestelling te plaatsen.
Dus je hebt de VLOOKUP-formule geschreven volgens de instructies stap voor stap, maar het werkt nog steeds niet. Excel neemt de dingen vrij letterlijk, dus we moeten voorzichtig zijn met de gegevens en de VLOOKUP-formule. Laten we een paar ideeën bekijken voor het corrigeren van een VLOOKUP-formule die gewoon niet werkt.
Een van de meest voorkomende problemen met VERT.ZOEKEN is wanneer er meerdere overeenkomsten in uw opzoeklijst staan. Wanneer u een VERT.ZOEKEN gebruikt, komt deze overeen met het eerste item in de lijst.
Onze VLOOKUP zegt dat Carrie Richard onze Director of Marketing is, maar we weten dat ze onlangs gepromoveerd is tot de bedrijfsvoorzitter. Waarom werkt onze lookup niet?
Oeps, het lijkt erop dat er twee lijsten waren voor Carrie Richard in de opzoeklijst - één voor marketingdirecteur en één voor president. Zodra we de regel 'Director of Marketing' uit de opzoeklijst verwijderen, zijn onze gegevens correct.Het punt van VERT.ZOEKEN is om een overeenkomend item op te zoeken in een lijst. De opzoeklijst mag geen duplicaten bevatten voor de primaire sleutel, het item dat u gebruikt om overeen te komen. Anders raakt Excel in de war en laat alleen de eerste overeenkomst zien.
Een ander veelvoorkomend probleem is dat onze gegevens in de ogen van Excel mogelijk niet overeenkomen. Gegevens met een spatie voordat deze een "voorloop" -ruimte zou hebben, terwijl gegevens met een spatie erna een "achterliggende ruimte" hebben.
Deze VLOOKUP is perfect geschreven, maar het werkt niet. Je ziet duidelijk dat de naam in de opzoeklijst staat, maar Excel levert geen overeenkomst op. Lees verder om erachter te komen waarom.Als uw overeenkomende stuk gegevens een spatie ervoor of erna bevat, ziet Excel deze twee gegevens als totaal verschillend en keert geen overeenkomst terug. Excel-weergaven '_Andrew', 'Andrew_' en 'Andrew' als drie unieke stukjes gegevens die niet worden geëvenaard in VERT.ZOEKEN.
Blijkt dat er een "trailing space" is, of een enkele spatie achter de naam in de cel. Het is onmogelijk om te zien, maar het kan een VERT.ZOEKEN verbreken, omdat Excel "Alyssa Reddall" en "Alyssa Reddall (spatie)" anders behandelt. Nadat u de spatie hebt verwijderd, werkt VERT.KLOK normaal.Als uw gegevens leidende of volgspaties hebben, gebruikt u de TRIM functie in Excel om het op te schonen. VERT.ZOEKEN zou geweldig moeten werken na het gebruiken om voorloop- en volgspaties te verwijderen.
Zoals u wellicht weet, kunt u een cel naar beneden slepen aan het handvat om de formule in andere cellen te plakken. Dit breekt echter soms onze VLOOKUP omdat de celverwijzingen veranderen.
In deze schermafbeelding sleepte ik de VERT.ZOEKEN door de lijst om deze toe te passen op andere cellen. Merk echter op dat de lookup-referentie veranderde van A2 naar B15, naar A16 naar B29, vandaar dat de formule niet werkt. Mijn opzoeklijst is eigenlijk in A2 tot B15, dus de lookup is nu verbroken.Terwijl u de formules naar beneden trekt, wordt de referentie voor de opzoektabel niet meer uitgelijnd. Plots mist de opzoektabel rijen uit de opzoektabel en werkt onze VERT.ZOEKEN niet.
De oplossing is om de formule te maken absolute referentie, zodat wanneer u de formule naar beneden sleept, de lijst waarnaar wordt verwezen niet verandert. Klik in de cel waar je je VERT.ZOEKEN hebt geschreven en klik vervolgens ergens in de opzoeklijstverwijzing. Druk vervolgens op F4 . U zult merken dat de formule verandert in dollartekens.
Merk op dat de formule (bovenaan deze schermafbeelding in de formulebalk weergegeven) nu dollartekens bevat in de naslaglijstverwijzing, die laat zien dat er een absolute referentie. Nu, wanneer ik de formule naar beneden sleep, vergrendelt het de formule voor de opzoeklijst en werkt het perfect.VLOOKUP is een van die essentiële formules om een productieve Excel-gebruiker te zijn. Er is simpelweg niet genoeg tijd om gegevens handmatig op te zoeken en opnieuw in te voeren, dus het is belangrijk om formules als VERT.ZOEKEN te leren.
Als u problemen ondervindt bij het gebruik van de VLOOKUP-formule, kunt u hieronder een opmerking achterlaten voor het oplossen van problemen.
Accentsconagua