Door de functie VERT. ZOEKEN van Excel te combineren met de functie KOLOM, kunt u een opzoekformule maken die meerdere waarden retourneert uit een enkele rij van een database of tabel met gegevens. Leer hoe u een opzoekformule maakt die meerdere waarden retourneert uit één gegevensrecord.
Instructies in dit artikel zijn van toepassing op Excel 2019, 2016, 2013, 2010; en Excel voor Microsoft 365.
Bottom Line
De opzoekformule vereist dat de functie KOLOM genest is in VERT. ZOEKEN. Het nesten van een functie omvat het invoeren van de tweede functie als een van de argumenten voor de eerste functie.
Voer de lesgegevens in
In deze zelfstudie wordt de functie KOLOM ingevoerd als argument voor kolomindexnummer voor VERT. ZOEKEN. De laatste stap in de zelfstudie omvat het kopiëren van de opzoekformule naar extra kolommen om extra waarden voor het gekozen onderdeel op te halen.
De eerste stap in deze tutorial is om de gegevens in een Excel-werkblad in te voeren. Om de stappen in deze zelfstudie te volgen, voert u de gegevens in de onderstaande afbeelding in de volgende cellen in:
- Voer het bovenste gegevensbereik in de cellen D1 tot G1 in.
- Voer het tweede bereik in de cellen D4 tot G10 in.
De zoekcriteria en de opzoekformule die in deze zelfstudie zijn gemaakt, worden ingevoerd in rij 2 van het werkblad.
Deze tutorial bevat niet de standaard Excel-opmaak die in de afbeelding wordt getoond, maar dit heeft geen invloed op hoe de opzoekformule werkt.
Maak een benoemd bereik voor de gegevenstabel
Een benoemd bereik is een gemakkelijke manier om naar een gegevensbereik in een formule te verwijzen. Typ de naam van het bereik in plaats van de celverwijzingen voor gegevens te typen.
Een tweede voordeel van het gebruik van een benoemd bereik is dat de celverwijzingen voor dit bereik nooit veranderen, zelfs niet wanneer de formule naar andere cellen in het werkblad wordt gekopieerd. Bereiknamen zijn een alternatief voor het gebruik van absolute celverwijzingen om fouten te voorkomen bij het kopiëren van formules.
De bereiknaam bevat niet de koppen of veldnamen voor de gegevens (zoals weergegeven in rij 4), alleen de gegevens.
-
Markeer cellen D5 tot G10 in het werkblad.
Image -
Plaats de cursor in het naamvak boven kolom A, typ Table en druk vervolgens op Enter. Cellen D5 tot G10 hebben de bereiknaam Table.
Image - De bereiknaam voor het VLOOKUP-tabelarrayargument wordt later in deze zelfstudie gebruikt.
Open het dialoogvenster VERT. ZOEKEN
Hoewel het mogelijk is om de opzoekformule rechtstreeks in een cel in een werkblad te typen, vinden veel mensen het moeilijk om de syntaxis recht te houden - vooral voor een complexe formule zoals die in deze zelfstudie wordt gebruikt.
Gebruik als alternatief het dialoogvenster VERT. ZOEKEN Functieargumenten. Bijna alle Excel-functies hebben een dialoogvenster waarin elk van de argumenten van de functie op een aparte regel wordt ingevoerd.
-
Selecteer cel E2 van het werkblad. Dit is de locatie waar de resultaten van de tweedimensionale opzoekformule worden weergegeven.
Image -
Ga op het lint naar het tabblad Formules en selecteer Lookup & Reference.
Image -
Selecteer VLOOKUP om het dialoogvenster Functieargumenten te openen.
Image - In het dialoogvenster Functieargumenten worden de parameters van de functie VERT. ZOEKEN ingevoerd.
Voer het opzoekwaardeargument in
Normaal gesproken komt de opzoekwaarde overeen met een gegevensveld in de eerste kolom van de gegevenstabel. In dit voorbeeld verwijst de opzoekwaarde naar de naam van het onderdeel waarover u informatie wilt zoeken. De toegestane typen gegevens voor de opzoekwaarde zijn tekstgegevens, logische waarden, getallen en celverwijzingen.
Absolute celverwijzingen
Wanneer formules in Excel worden gekopieerd, veranderen celverwijzingen om de nieuwe locatie weer te geven. Als dit gebeurt, verandert D2, de celverwijzing voor de opzoekwaarde, en ontstaan er fouten in de cellen F2 en G2.
Absolute celverwijzingen veranderen niet wanneer formules worden gekopieerd.
Om fouten te voorkomen, converteert u de celverwijzing D2 naar een absolute celverwijzing. Druk op de F4-toets om een absolute celverwijzing te maken. Dit voegt dollartekens toe rond de celverwijzing, zoals $D$2.
-
Plaats in het dialoogvenster Functieargumenten de cursor in het lookup_value tekstvak. Selecteer vervolgens in het werkblad cell D2 om deze celverwijzing toe te voegen aan de lookup_value. In cel D2 wordt de onderdeelnaam ingevoerd.
Image -
Zonder het invoegpunt te verplaatsen, drukt u op de toets F4 om D2 om te zetten in de absolute celverwijzing $D$2.
Image - Laat het dialoogvenster VERT. ZOEKEN open staan voor de volgende stap in de zelfstudie.
Voer het tabelarrayargument in
Een tabelarray is de tabel met gegevens waarin de opzoekformule zoekt om de gewenste informatie te vinden. De tabelarray moet ten minste twee gegevenskolommen bevatten.
De eerste kolom bevat het opzoekwaardeargument (dat in de vorige sectie is ingesteld), terwijl de tweede kolom wordt doorzocht met de opzoekformule om de informatie te vinden die u opgeeft.
Het array-argument van de tabel moet worden ingevoerd als een bereik dat de celverwijzingen voor de gegevenstabel bevat, of als een bereiknaam.
Om de tabel met gegevens toe te voegen aan de functie VERT. ZOEKEN, plaatst u de cursor in het table_array tekstvak in het dialoogvenster en typt u Tableom de bereiknaam voor dit argument in te voeren.
Nest de COLUMN-functie
Normaal gesproken retourneert VERT. ZOEKEN alleen gegevens uit één kolom van een gegevenstabel. Deze kolom wordt ingesteld door het argument kolomindexnummer. In dit voorbeeld zijn er echter drie kolommen en moet het kolomindexnummer worden gewijzigd zonder de opzoekformule te bewerken. Om dit te bereiken, nest de functie KOLOM in de functie VERT. ZOEKEN als het argument Col_index_num.
Bij het nesten van functies opent Excel het dialoogvenster van de tweede functie niet om de argumenten in te voeren. De functie KOLOM moet handmatig worden ingevoerd. De functie KOLOM heeft slechts één argument, het argument Referentie, dat een celverwijzing is.
De functie COLUMN retourneert het nummer van de kolom die is opgegeven als het referentieargument. Het zet de kolomletter om in een getal.
Gebruik de gegevens in kolom 2 van de gegevenstabel om de prijs van een artikel te vinden. In dit voorbeeld wordt kolom B als referentie gebruikt om 2 in te voegen in het argument Col_index_num.
-
Plaats in het Functieargumenten dialoogvenster de cursor in het Col_index_num tekstvak en typ COLUMN(.(Zorg ervoor dat u de open ronde haakjes gebruikt.)
Image -
Selecteer in het werkblad cel B1 om die celverwijzing in te voeren als het referentieargument.
Image - Typ een rond haakje sluiten om de functie KOLOM te voltooien.
Voer het VLOOKUP Range Lookup-argument in
VLOOKUP's Range_lookup-argument is een logische waarde (TRUE of FALSE) die aangeeft of VERT. ZOEKEN een exacte of geschatte overeenkomst met de Lookup_value moet vinden.
- TRUE of Weggelaten: VERT. ZOEKEN retourneert een nauwe overeenkomst met de Lookup_value. Als er geen exacte overeenkomst wordt gevonden, retourneert VERT. ZOEKEN de volgende grootste waarde. De gegevens in de eerste kolom van Table_array moeten in oplopende volgorde worden gesorteerd.
- FALSE: VERT. ZOEKEN gebruikt een exacte overeenkomst met de Lookup_value. Als er twee of meer waarden in de eerste kolom van Table_array zijn die overeenkomen met de opzoekwaarde, wordt de eerste gevonden waarde gebruikt. Als er geen exacte overeenkomst wordt gevonden, wordt een N/B-fout geretourneerd.
In deze tutorial wordt specifieke informatie over een bepaald hardware-item opgezocht, dus de Range_lookup is ingesteld op FALSE.
Plaats in het dialoogvenster Functieargumenten de cursor in het tekstvak Range_lookup en typ False om VERT. ZOEKEN te laten weten een exacte overeenkomst voor de gegevens te retourneren.
Selecteer OK om de opzoekformule te voltooien en het dialoogvenster te sluiten. Cel E2 bevat een N/A-fout omdat de opzoekcriteria niet zijn ingevoerd in cel D2. Deze fout is tijdelijk. Het wordt gecorrigeerd wanneer de opzoekcriteria worden toegevoegd in de laatste stap van deze tutorial.
Kopieer de opzoekformule en voer criteria in
De opzoekformule ha alt gegevens uit meerdere kolommen van de gegevenstabel tegelijk op. Om dit te doen, moet de opzoekformule aanwezig zijn in alle velden waarvan u informatie wilt.
Als u gegevens wilt ophalen uit de kolommen 2, 3 en 4 van de gegevenstabel (de prijs, het onderdeelnummer en de naam van de leverancier), voert u een gedeeltelijke naam in als Lookup_value.
Aangezien de gegevens in een regelmatig patroon in het werkblad zijn ingedeeld, kopieert u de opzoekformule in cel E2 naar cellen F2 en G2 Terwijl de formule wordt gekopieerd, werkt Excel de relatieve celverwijzing bij in de functie KOLOM (cel B1) om de nieuwe locatie van de formule weer te geven. Excel verandert de absolute celverwijzing (zoals $ D $ 2) en het benoemde bereik (tabel) niet terwijl de formule wordt gekopieerd.
Er is meer dan één manier om gegevens in Excel te kopiëren, maar de gemakkelijkste manier is om de vulgreep te gebruiken.
-
Selecteer cell E2, waar de opzoekformule zich bevindt, om er de actieve cel van te maken.
Image -
Sleep de vulgreep naar cel G2. Cellen F2 en G2 geven de N/A-fout weer die aanwezig is in cel E2.
Image -
Als u de opzoekformules wilt gebruiken om informatie uit de gegevenstabel op te halen, selecteert u in het werkblad cell D2, typt u Widget en drukt u op Enter.
Image De volgende informatie wordt weergegeven in cellen E2 tot G2.
- E2: $ 14,76 - de prijs van een widget
- F2: PN-98769 - het onderdeelnummer voor een widget
- G2: Widgets Inc. - de naam van de leverancier voor widgets
-
Om de matrixformule VERT. ZOEKEN te testen, typt u de naam van andere delen in cel D2 en bekijkt u de resultaten in cellen E2 tot G2.
Image - Elke cel met de opzoekformule bevat een ander stuk gegevens over het hardware-item waarnaar u zocht.
De functie VERT. ZOEKEN met geneste functies zoals KOLOM biedt een krachtige methode om gegevens in een tabel op te zoeken, waarbij andere gegevens worden gebruikt als opzoekreferentie.
