De functie VLOOKUP van Excel wordt gebruikt om informatie uit een gegevenstabel op te zoeken en terug te sturen op basis van een opzoekwaarde die u kiest.
Normaal gesproken vereist VERT.ZOEKEN dat de opzoekwaarde in de meest linkse kolom van de gegevenstabel staat en retourneert de functie een ander gegevensveld dat zich in dezelfde rij rechts van deze waarde bevindt.
Door echter VERT.ZOEKEN te combineren met de functie KIEZEN, kan een formule voor links opzoeken worden gemaakt die:
- Staat de opzoekwaarde toe van elke kolom in de gegevenstabel
- Retourneert informatie in elke kolom links van de opzoekwaarde
Notitie: Deze instructies zijn van toepassing op Excel-versies 2019, 2016, 2013, 2010 en Excel voor Office 365.
01 van 05Zelfstudie: de functies VERT.ZOEKEN en KIEZEN gebruiken in een formule voor links opzoeken
Gebruik de formule om de linker lookup-formule te maken die u in de voorbeeldafbeelding ziet:
= VERT.ZOEKEN ($ D $ 2, KIES ({1,2}, $ F: $ F $ D: $ D), 2, ONWAAR)
In dit voorbeeld maakt de formule het mogelijk het onderdeel te vinden dat wordt geleverd door de verschillende bedrijven die worden vermeld in kolom 3 van de gegevenstabel.
De taak van de functie KIEZEN in de formule is om VERT.ZOEKEN ertoe te brengen te geloven dat kolom 3 kolom 1 is. Als gevolg hiervan kan de bedrijfsnaam worden gebruikt als opzoekwaarde om de naam te vinden van het onderdeel dat door elk bedrijf wordt geleverd.
Voer de zelfstudiegegevens in
- Voer de kop in Leverancier in de cel D1.
- Voer de kop in Een deel in cel E1.
- Voer de tabel met gegevens in de bovenstaande afbeelding in cellen in D4 tot F9
- rijen 2 en 3 worden niet ingevuld om te voldoen aan de zoekcriteria en de formule voor de linker lookup die tijdens deze zelfstudie is gemaakt
Open het dialoogvenster VLOOKUP
Hoewel het mogelijk is om de formule rechtstreeks in cel F1 in het werkblad te typen, hebben veel mensen moeite met de syntaxis van de formule.
In dit geval is het veiliger om het dialoogvenster VERT.ZOEKEN te gebruiken. Bijna alle functies van Excel hebben een dialoogvenster waarin u elk van de argumenten van de functie op een afzonderlijke regel kunt invoeren.
- Klik op cel E2 van het werkblad. E2 is de locatie waar de resultaten van de linker lookup-formule worden weergegeven.
- Klik op de formules tabblad van het lint.
- Klik op de Lookup & Reference optie in het lint om de vervolgkeuzelijst met functies te openen.
- Klik op VLOOKUP in de lijst om het dialoogvenster van de functie te openen.
Argumenten invoeren in het dialoogvenster VLOOKUP
De argumenten van een functie zijn de waarden die door de functie worden gebruikt om een resultaat te berekenen.
In het dialoogvenster van een functie staat de naam van elk argument op een aparte regel gevolgd door een veld waarin een waarde moet worden ingevoerd.
Voer de volgende waarden in voor elk van de argumenten van VERT.ZOEKEN op de juiste regel van het dialoogvenster zoals weergegeven in de bijbehorende afbeelding.
De opzoekwaarde
De lookup-waarde is het veld met informatie dat wordt gebruikt om in de tabelarray te zoeken. VLOOKUP retourneert een ander gegevensveld uit dezelfde rij als de opzoekwaarde.
In dit voorbeeld wordt een celverwijzing gebruikt naar de locatie waar de bedrijfsnaam in het werkblad wordt ingevoerd. Het voordeel hiervan is dat het eenvoudig is om de bedrijfsnaam te wijzigen zonder de formule te hoeven bewerken.
- Klik op de opzoekwaarde regel in het dialoogvenster.
- Klik op cel D2 om deze celverwijzing toe te voegen aan de opzoekwaarde lijn.
- druk de F4 toets op het toetsenbord om de celverwijzing absoluut te maken - $ D $ 2.
Absolute celverwijzingen
Absolute celverwijzingen worden gebruikt voor de waarden voor opzoekwaarde en tabelarray om fouten te voorkomen als de opzoekformule wordt gekopieerd naar andere cellen in het werkblad.
De functie KIEZEN invoeren
Het argument tabelarray is het blok met aangrenzende gegevens waaruit specifieke informatie wordt opgehaald.
Meestal zoekt VLOOKUP alleen naar de rechterkant van het zoekwaardeargumentargument om gegevens in de tabelmatrix te vinden. Om ervoor te zorgen dat het er naar links uitziet, moet VERT.ZOEKEN worden misleid door de kolommen in de tabelmatrix te herschikken met de functie KIEZEN.
In deze formule voert de functie KIEZEN twee taken uit:
- Het maakt een tabelmatrix die slechts twee kolommen breed is (kolommen D en F).
- Het wijzigt de volgorde van rechts naar links van de kolommen in de tabelreeks, zodat kolom F eerst komt en kolom D de tweede is.
Functies invoeren
Bij het handmatig invoeren van functies moet elk van de argumenten van de functie worden gescheiden door een komma.
- Klik in het dialoogvenster VERT.ZOEKEN op de table_array lijn.
- Vul het volgende in KIEZEN functie:KIES ({1,2}, $ F: $ F $ D: $ D)
Kolomindexnummer
Normaal gesproken geeft het kolomindexnummer aan welke kolom van de tabelreeks de gegevens bevat die u zoekt. In deze formule verwijst dit echter naar de volgorde van kolommen die is ingesteld met de functie KIEZEN.
Met de functie KIEZEN wordt een tabelmatrix gemaakt met twee kolommen breed met eerst kolom F gevolgd door kolom D. Omdat de gezochte informatie -de naam van het onderdeel- zich in kolom D bevindt, moet de waarde van het kolomindexargument op 2 worden ingesteld.
- Klik op de kolomindex_getal regel in het dialoogvenster.
- Type A 2 in deze regel.
Bereik opzoeken
Het bereik Range_lookup van VLOOKUP is een logische waarde (alleen TRUE of FALSE) die aangeeft of u met VERT.ZOEKEN een exacte of een geschatte overeenkomst wilt zoeken met de opzoekwaarde.
- Als TRUE is of als dit argument is weggelaten, retourneert VLOOKUP een exacte overeenkomst met de zoekwaarde of, als er geen exacte overeenkomst wordt gevonden, retourneert VLOOKUP de op een na grootste waarde. Voor de formule om dit te doen, moeten de gegevens in de eerste kolom van Table_array in oplopende volgorde worden gesorteerd.
- Als FALSE wordt gebruikt, gebruikt VLOOKUP alleen een exacte overeenkomst met de zoekwaarde. Als er twee of meer waarden in de eerste kolom van Table_array zijn die overeenkomen met de zoekwaarde, wordt de eerste gevonden waarde gebruikt. Als er geen exacte overeenkomst wordt gevonden, wordt een # N / A-fout geretourneerd.
In deze zelfstudie, omdat we naar een bepaalde onderdeelnaam op zoek zijn, wordt Range_lookup ingesteld op vals zodat alleen exacte overeenkomsten worden geretourneerd door de formule.
- Klik op de range_lookup regel in het dialoogvenster.
- Typ het woord vals in deze regel om aan te geven dat we willen dat VLOOKUP een exacte overeenkomst retourneert voor de gegevens die we zoeken.
- Klik OK om de formule voor de linkerzoekformule in te vullen en het dialoogvenster te sluiten.
- Omdat we de bedrijfsnaam nog niet in cel D2 hebben ingevoerd, verschijnt er een # N / A-fout in cel E2.
Retourneren van gegevens met de formule voor links opzoeken
Om te achterhalen welke bedrijven welke onderdelen leveren, typt u de naam van een bedrijf in in de cel D2 en druk op de ENTER toets op het toetsenbord.
De naam van het onderdeel wordt weergegeven in cel E2.
- Klik op cel D2 in je werkblad.
- Type Gadgets Plus in de cel D2 en druk op de ENTER toets op het toetsenbord.
- De tekst "Gadgets" - het gedeelte dat wordt geleverd door het bedrijf Gadgets Plus - moet worden weergegeven in cel E2.
Test de opzoekformule verder door andere bedrijfsnamen in cel D2 te typen en de overeenkomende naam van het onderdeel moet in cel E2 verschijnen.
Als er een foutmelding zoals # N / A in cel E2 verschijnt, controleer dan op spelfouten in cel D2.
04 van 05Een matrix met twee kolommen maken
De syntaxis voor de functie KIEZEN is:
= CHOOSE (Index_number, Value1, Value2, … Value254)
De functie KIEZEN retourneert meestal één waarde uit de lijst met waarden (Waarde1 tot Waarde254) op basis van het ingevoerde indexnummer.
Als het indexnummer 1 is, geeft de functie Waarde1 uit de lijst; als het indexnummer 2 is, retourneert de functie Value2 uit de lijst enzovoort.
Wanneer er meerdere indexnummers worden ingevoerd, retourneert de functie meerdere waarden in elke gewenste volgorde. KIEZEN om meerdere waarden te retourneren, wordt gedaan door een array te maken.
Om een array in te voeren, omringt u de ingevoerde cijfers met accolades of haakjes. Voor het indexnummer worden twee cijfers ingevoerd:{ 1,2 }.
Opgemerkt moet worden dat KIEZEN niet beperkt is tot het creëren van een tabel met twee kolommen. Door een extra nummer in de array op te nemen, zoals {1,2,3}, en een extra bereik in het waardeargument, maakt CHOOSE een tabel met drie kolommen.
Met extra kolommen kunt u verschillende informatie retourneren met de formule voor de linker lookup door het kolomindexnummerargument van VERT.ZOEKEN te wijzigen in het nummer van de kolom met de gewenste informatie.
05 of 05De volgorde van kolommen wijzigen met de CHOOSE-functie
In de functie KIEZEN die in deze formule wordt gebruikt:
KIES ({1,2}, $ F: $ F $ D: $ D)
het bereik voor kolom F wordt vermeld vóór kolom D.
Omdat de CHOOSE-functie de tabelmatrix van VERT.ZOEKEN (de gegevensbron voor die functie) inschakelt, wordt de volgorde van de kolommen in de functie KIEZEN doorgegeven aan VERT.ZOEKEN.
Wat VERT.ZOEKEN betreft, is de tabelmatrix slechts twee kolommen breed met kolom F aan de linkerkant en kolom D aan de rechterkant. Aangezien kolom F de naam bevat van het bedrijf dat we willen zoeken en omdat kolom D de namen van onderdelen bevat, kan VLOOKUP zijn reguliere opzoektaken uitvoeren door gegevens te vinden die zich links van de opzoekwaarde bevinden.
Als gevolg hiervan kan VLOOKUP de bedrijfsnaam gebruiken om het onderdeel te vinden dat ze leveren.
