Hoe een VERT.ZOEKEN-formule te gebruiken om gegevens uit een Excel-lijst op te halen. Los problemen met VERT.ZOEKEN op als er iets misgaat. Stapsgewijze videos en Excel-werkmappen met VERT.ZOEKEN-voorbeelden.
VERT.ZOEKEN-video
Bekijk de stappen voor het maken van een VERT.ZOEKEN-formule in de Productprijs-opzoekvideo, hieronder weergegeven. De schriftelijke instructies staan onder de video. Download het voorbeeldbestand voor het opzoeken van productprijzen om samen met de video te volgen (downloadbestand nr. 4).
Selecteer een locatie voor een opzoektabel
Het is een goed idee om elke zoekactie op te slaan tabel op een afzonderlijk werkblad in de werkmap. Als u vervolgens rijen toevoegt en verwijdert in de opzoektabellen, zult u niet per ongeluk rijen in een andere tabel toevoegen of verwijderen.
In dit voorbeeld is de opzoektabel bevindt zich op een blad met de naam Producten.
Maak een opzoektabel
Opzoekformules kunnen verticaal werken, waarbij naar waarden in een kolom wordt gezocht, of ze kunnen horizontaal werken, op zoek naar waarden in een rij . In dit voorbeeld wordt de informatie verticaal opgeslagen, met waarden in een kolom, en later “gebruiken we een VLookup-formule om verticaal te zoeken.
- Voer de koppen in de eerste rij in
- De eerste kolom moet de unieke sleutelwaarden bevatten waarop u de zoekopdracht wilt baseren. In dit voorbeeld kunt u de prijs voor een specifieke productcode.
- Als u andere gegevens op het werkblad heeft, laat dan minstens één lege rij onder aan de tabel en één lege kolom aan de rechterkant van de tabel om de opzoektabel uit de andere gegevens.
Opmerking: om het gemakkelijker te maken om naar de tabel te verwijzen, kunt u het bereik een naam geven. Hier vindt u instructies: Een bereik een naam geven ▲ TOP
VLOOKUP-functieargumenten
De VERT.ZOEKEN-functie heeft vier argumenten:
- lookup_value : Welke waarde wilt u opzoeken? In dit voorbeeld staat de productcode in cel A7 en wilt u de pr oductnaam.
- table_array: waar is de opzoektabel? Als u een absolute verwijzing ($ A $ 2: $ C $ 5) gebruikt in plaats van een relatieve verwijzing (A2: C5), is het gemakkelijker om naar een formule naar andere cellen te kopiëren. Of geef de opzoektabel een naam en verwijs ernaar met de naam.
- col_index_num: welke kolom heeft de waarde die u wilt retourneren? In dit voorbeeld staan de productnamen in de tweede kolom van de opzoektabel.
- : Wilt u een exacte overeenkomst? Is een match bij benadering oké?
Als u WAAR als laatste argument gebruikt, of het laatste argument weglaat, kan een geschatte overeenkomst worden geretourneerd. Dit voorbeeld heeft FALSE als het laatste argument, dus als de productcode niet wordt gevonden, is het resultaat # N / A. (Opmerking: Excel is nogal vergevingsgezind en accepteert 0 in plaats van FALSE en 1 in plaats van TRUE.) ▲ TOP
Maak een VLookup-formule
Nadat u de opzoektabel heeft gemaakt, kunt u andere formules in de werkmap maken en informatie uit de productlijst halen. Om bijvoorbeeld een factuur op te stellen, kunt u een productcode invoeren en formules halen de productnaam of prijs uit de producttabel.
In dit voorbeeld wordt de factuur aangemaakt op een blad met de naam Factuur. De VERT.ZOEKEN-formule moet een exacte overeenkomst vinden voor de productcode en de productnaam retourneren.
Volg deze stappen om de VERT.ZOEKEN-formule te maken die de productprijs berekent:
- Selecteer het factuurblad
- Voer productcode A23 in cel A7 in
- Start in cel B7 de formule VERT.ZOEKEN:
= VERT.ZOEKEN ( - Klik op cel A7 om zijn referentie in te voeren.
- Typ een komma om het eerste argument te beëindigen
- Selecteer het productblad
- Selecteer de cellen A2: C5 (de productlijst )
- Druk op de F4-toets om de celverwijzingen naar absoluut te wijzigen. De formule zou er nu als volgt uit moeten zien:
= VERT.ZOEKEN (A7, Producten! $ A $ 2: $ C $ 5 - Typ een komma om het tweede argument te beëindigen.
- Typ een 2, dit is de kolom in de opzoektabel die de productnaam bevat.
- Typ een komma om de derde argument.
- Typ FALSE, om aan te geven dat een exacte overeenkomst voor de productcode wordt gevonden, en voeg de sluitende haak toe.
- De formule ziet er nu uit als l ike dit:
= VERT.ZOEKEN (A7, Producten! $ A $ 2: $ C $ 5,2, ONWAAR) - Druk op Enter om de formule te voltooien. De productnaam wordt weergegeven.
Opmerking: om de productprijs te retourneren, maakt u een VERT.ZOEKEN-formule die verwijst naar kolom 3 van de opzoektabel. Voer bijvoorbeeld de volgende formule in cel C7 in:
= VERT.ZOEKEN (A7, Producten! $ A $ 2: $ C $ 5,3, ONWAAR) ▲ BOVEN
Formule VERT.ZOEKEN voor waardenbereik
In sommige situaties heeft een geschatte overeenkomst de voorkeur, dus verschillende waarden zullen hetzelfde resultaat opleveren. Bij het beoordelen van werkstukken van studenten moeten bijvoorbeeld alle werkstukken met een cijfer van 85 of hoger een A-cijfer krijgen.
Bekijk de onderstaande video VERT.ZOEKEN om de stappen voor het maken van deze formule te bekijken. De schriftelijke instructies staan onder de video.
In dit voorbeeld wordt de opzoektabel gemaakt op een blad met de naam Grades. Om de opzoektabel te maken, voert u de minimumscore voor elk cijfer in kolom A in. Voer het overeenkomende cijfer in kolom B in. Sorteer de scores in oplopende volgorde.
Cellen A2: B6 kregen de naam GradeList.
De scores worden ingevoerd op een blad met de naam Rapportkaart, waar een VERT.ZOEKEN-formule het cijfer berekent.
In de screenshot hieronder, de formule is gekopieerd naar rij 6, en je kunt de formule zien in cel C6.
Combineer VERT.ZOEKEN met VERGELIJKEN
Gebruik in plaats van het kolomnummer in een VERT.ZOEKEN-formule te typen, de functie VERGELIJKEN om de juiste kolom in de opzoektabel te vinden. Dit heeft een aantal voordelen:
- Maakt de formule flexibel, zodat het gemakkelijker is om de formule over een werkblad te kopiëren.
- Kan problemen voorkomen als er nieuwe kolommen worden toegevoegd in de opzoektabel, of als de opzoekkolommen opnieuw worden gerangschikt.
Deze video toont de stappen en er zijn geschreven instructies voor een ander voorbeeld, onder de video.
Uw browser kan dit frame niet weergeven. Hier is een link naar de pagina
VERT.ZOEKEN met VERGELIJKEN voor bestelgegevens
In dit voorbeeld retourneert een VERT.ZOEKEN-formule de bestelgegevens uit een opzoektabel, op basis van het bestel-ID-nummer . Hier is de opzoektabel, genaamd tblOrders.
OPMERKING: dit voorbeeld is in voorbeeldwerkboek # 1, op het blad met de naam OrdersMATCH.
Hier is het werkblad met de VERT.ZOEKEN-formules. We willen de regio, de besteldatum en het bestelbedrag voor elke bestelling, dus er zijn 3 VERT.ZOEKEN-formules nodig.
Als de kolomnummers in de formule worden getypt, is in elke kolom een andere formule nodig:
- Regio: = VERT.ZOEKEN ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VERT.ZOEKEN ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VERT.ZOEKEN ($ B6, tblOrdersALL, 4,0)
De MATCH-functie
In plaats van het kolomnummer in de VERT.ZOEKEN-formule te typen, kunnen we de MATCH-functie gebruiken. De MATCH-functie zoekt de positie van een item in een lijst en retourneert het positienummer.
In de onderstaande schermafbeelding retourneert de MATCH-formule 2 als de positie van “Regio”, in de kopcellen (A1: D1) voor de opzoektabel.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
OPMERKING: om deze techniek correct te laten werken, moet de koppen op het blad VERT.ZOEKEN moeten exact overeenkomen met de koppen van de opzoektabel. Om een exacte overeenkomst te garanderen, zijn de cellen in de koptekst VERT.ZOEKEN gekoppeld aan de koptekstcellen van de opzoektabel.
VERGELIJKEN aan VERT.ZOEKEN toevoegen
Om de functie VERGELIJKEN aan de formule VERT.ZOEKEN toe te voegen, vervangt u gewoon getypt kolomnummer
= VERT.ZOEKEN ($ B6, tblOrdersALL, 2,0)
met de MATCH-formule:
= VERT.ZOEKEN ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Kopieer de VERT.ZOEKEN-formule naar
Nu, in plaats van een andere formule in elke kolom nodig te hebben, kunt u de formule kopiëren en dezelfde formule in alle kolommen gebruiken. In elke kolom verwijst het naar de kopcel in die kolom en vindt het zijn positie in de opzoektabel.
OPMERKING: als u de formule over kolommen met verschillende opmaak vult, volgt u deze stappen:
- Selecteer de cel met de formule die je wilt kopiëren
- Wijs naar de vulgreep op de geselecteerde cel (klein vierkantje in de rechter benedenhoek)
- Druk op de RECHTER muisknop en sleep naar de laatste cel die de formule nodig heeft.
- Laat de muisknop los en klik op Invullen zonder opmaak
Combineer ALS en VERT.ZOEKEN
U kunt een ALS-formule gebruiken met een VLookup-formule om exacte waarden te retourneren indien gevonden, en een lege tekenreeks indien niet gevonden.
Om de stappen te zien voor het instellen van de IF- en VERT.ZOEKEN-formule, kun je deze korte video bekijken. De schriftelijke instructies staan onder de video.
Om fouten te verbergen door IF te combineren met VERT.ZOEKEN, volgt u deze stappen:
- Voer op het factuurblad in cel A8 het product in code A28. Als de VLookup-formule in cel B8 FALSE als vierde argument heeft, is het resultaat # N / A, omdat er geen exacte overeenkomst is voor de productcode in de opzoektabel.
- Wikkel de VLookup-formule in met een IF-formule (in dit voorbeeld heeft de productlijst een naam gekregen), waarbij de ISNA-functie wordt gebruikt om te controleren op een # N / A-fout:
- Druk op de Enter-toets en de cel wordt leeg weergegeven. Omdat er geen exacte overeenkomst is gevonden, retourneert de VLookup-formule een # N / A, dus het resultaat van de ISNA-functie is WAAR. De IF-formule heeft dit omgezet in een lege string.
= IF (ISNA (VERT.ZOEKEN (A8, ProductList, 2, FALSE) ), “”, VERT.ZOEKEN (A8, ProductList, 2, FALSE))
Combineer IFERROR en VERT.ZOEKEN
Dank aan Chip Pearson voor het suggereren van deze formule.
In Excel 2007 werd een nieuwe functie, IFERROR, geïntroduceerd . U kunt een IFERROR-formule gebruiken met VLookup om verschillende tabellen op een waarde te controleren en de gerelateerde informatie te retourneren wanneer deze wordt gevonden. In dit voorbeeld hebben drie regios, West, Oost en Centraal, bestelbonnen. Op elk blad staat een benoemd bereik – OrdersW, OrdersE en OrdersC.
Op een blad met de naam Orders kunt u een order-ID invoeren en vervolgens VERT.ZOEKEN met IFERROR gebruiken om elk benoemd bereik te controleren en bekijk de informatie over de geselecteerde bestelling.
- Voer op het bestelformulier in cel B6 een 4 in als de OrderID. Die bestelling is in de regio Centraal geplaatst.
- Om eenvoudig de tabel van de regio Oost te controleren, zou de VERT.ZOEKEN-formule in cel C6 zijn:
= VERT.ZOEKEN (B6, OrdersE, 2, FALSE) - Druk op de Enter-toets en de VERT.ZOEKEN-formule retourneert een # N / A, omdat Order ID 4 niet in de ordertabel van de regios Oost staat.
- Omdat een order in een van de drie regios is geplaatst, hebt u een formule nodig die elke tabel controleert.
Als de bestellings-ID niet in de eerste tabel staat, moet de formule de tweede tabel controleren. Als de bestellings-ID niet in de tweede tabel, zou het de derde tabel moeten controleren. Als het order-ID niet “niet in de derde tabel” staat, dan zou het bericht “Niet gevonden” in de cel moeten verschijnen. - Met de IFERROR-formule kun je een waarde controleren, specificeer vervolgens wat u moet doen als er een fout wordt gevonden. Als u IFERROR gebruikt met de bestaande formule, kunt u “Not Found” weergeven in plaats van de # N / A-fout:
= IFERROR (VERT.ZOEKEN (B6, OrdersE, 2, FALSE), “Not Found”) - Om alle drie de tabellen te controleren, kun je de volgende IFERROR- en VLOOKUP-formules gebruiken:
Dit controleert de OrdersE-tabel en als er een fout wordt gevonden, controleert de OrdersW-tabel en vervolgens OrdersC. Als de OrderID niet in een van de drie tabellen wordt gevonden, wordt het bericht Niet gevonden in de cel weergegeven.
U kunt ook meerdere tabellen controleren in oudere versies van Excel, waar IFERROR niet beschikbaar is, met behulp van een langere formule:
VERT.ZOEKEN voor gecombineerde waarden
In sommige tabellen zijn er mogelijk geen unieke waarden een willekeurige kolom in de opzoektabel. In de onderstaande tabel wordt Jacket bijvoorbeeld twee keer vermeld in kolom A. Er is echter maar één record voor elke combinatie van jas en maat: jas medium in rij 4 en jas groot in rij 5.
Als u de prijs voor een grote jas moet vinden, zou een VERT.ZOEKEN alleen gebaseerd op kolom A de prijs voor de eerste vermelde jas (gemiddeld ). U zou de jas te laag prijzen – door hem te verkopen voor 60,00 in plaats van 65,00.
Om unieke opzoekwaarden te creëren, moet u kan een nieuwe kolom aan de linkerkant van de tabel invoegen en een formule gebruiken om het product en de maat te combineren. In cel A2 combineert de formule de waarde in B2 en het pipe-teken en de waarde in C2.
= B2 & “|” & C2
Kopieer die formule naar de laatste rij met gegevens, zodat elke rij een unieke waarde heeft in kolom A.
Opmerking : In plaats van het pipe-teken, kunt u een ander teken gebruiken dat “niet in uw gegevens is opgenomen.
Vervolgens, in een VERT.ZOEKEN-formule, combineer het product en de grootte als de Opzoekwaarde. In cel H1 combineert de formule de waarde in F1 en het pipe-teken en de waarde in G1.
= VERT.ZOEKEN (F1 &” | “& G1, $ A $ 2: $ D $ 5,4, FALSE)
Opmerking: de prijs is nu in kolom 4, in plaats van kolom 3. ▲ TOP
Los problemen met de VERT.ZOEKEN-formule op
Uw VERT.ZOEKEN-formule kan een # N / A retourneren, ook al lijkt de waarde die u zoekt in de opzoektabel te staan. Veelvoorkomende oorzaken hiervoor zijn:
Tekst versus nummer
Spatie-tekens
Andere tekens
Tekst versus nummer
Een veelvoorkomende oorzaak van deze fout is dat een van de waarden een getal is en de andere tekst. De opzoektabel kan bijvoorbeeld 123 (tekst) bevatten en de op te zoeken waarde is 123 (een getal).
- Converteer de tekst indien mogelijk naar getallen met een van de volgende methoden die hier worden weergegeven: Converteer tekst naar getallen
- Als u de gegevens niet kunt converteren, kunt u de opzoekwaarde in de VERT.ZOEKEN-formule converteren met een van de onderstaande oplossingen:
- Waarden opzoeken zijn tekst, tabel heeft getallen
- opzoekwaarden zijn getallen, tabel heeft tekst
- Om erachter te komen welke waarden tekst zijn en welke getallen, zie de gedetailleerde stappen voor het oplossen van problemen met aantal of tekst
1. Opzoekwaarden zijn tekst en de tabel bevat getallen
Als de opzoektabel getallen bevat en de op te zoeken waarde tekst is, gebruik dan een formule die lijkt op de volgende:
= VERT.ZOEKEN (–A7, Producten!$ A $ 2: $ C $ 5,3, FALSE)
De dubbele unaire (-) converteert tekst naar een getal, en zal correct werken, zelfs als de opzoekwaarden getallen zijn.
2. Opzoekwaarden zijn getallen en de tabel bevat tekst
Als de opzoektabel tekst bevat en de op te zoeken waarde numeriek is, gebruik dan een formule die lijkt op de volgende:
= VERT.ZOEKEN (A7 & “”), Producten! $ A $ 2: $ C $ 5,3, FALSE)
OF
= VERT.ZOEKEN (TEKST (A7; “00000”) , Producten! $ A $ 2: $ C $ 5,3, FALSE)
De TEXT-functie converteert een getal naar tekst en zal correct werken, zelfs als de opzoekwaarden tekst zijn. In het eerste voorbeeld maakt de operator & een tekstreeks aan van een niet-opgemaakt nummer. In het tweede voorbeeld zou een getal dat is opgemaakt met voorloopnullen (bijv. 00123) overeenkomen met een tekst “getal” met voorloopnullen.
Om de stappen te zien voor het oplossen van het VERT.ZOEKEN-probleem wanneer de opzoektabel tekstwaarden heeft, bekijk deze korte video-tutorial.
Spaties in de ene waarde, en niet in de andere
Een andere mogelijke oorzaak dat er geen overeenkomende waarde wordt gevonden, is een verschil in spaties. Een van de waarden kan voorloopspaties (of volgspaties of ingesloten spaties) bevatten, en de andere niet. Om de waarden te testen, kunt u de LEN-functie gebruiken om de lengte van elke waarde te controleren.
Bijvoorbeeld: = LEN (A7) retourneert het aantal tekens in cel A7. Het moet gelijk zijn aan het aantal tekens in de overeenkomende cel in de opzoektabel.
Verwijder indien mogelijk de overbodige spaties, en de VERT.ZOEKEN-formule zou correct moeten werken. Als u de spaties niet kunt verwijderen, gebruik dan de TRIM-functie in VERT.ZOEKEN om voorloop-, volg- of dubbele spaties te verwijderen. Bijvoorbeeld:
= VERT.ZOEKEN (TRIM (A7), ProductList, 2, FALSE)
Andere tekens
Als de TRIM-functie alleen het probleem niet oplost , kunt u een van de volgende suggesties proberen:
- SUBSTITUTE-functie
- CLEAN-functie
- Macro om webtekens te verwijderen
SUBSTITUTE-functie
Gebruik de SUBSTITUTE-functie om ongewenste tekens te verwijderen. Er is een voorbeeld op de Contextures-blog: Clean Excel-gegevens met TRIM en SUBSTITUTE
CLEAN-functie
Een andere manier om VLOOKUP-problemen op te lossen, is met de CLEAN-functie, die enkele ongewenste tekens uit de tekst kan verwijderen. Er is meer informatie over de CLEAN-functie in dit Contextures-blogbericht: 30 Excel-functies in 30 dagen: 29 – CLEAN
Webtekens in de ene waarde, en niet in de andere
Als u gegevens van een webpagina heeft gekopieerd, kan deze een niet-afbrekende HTML-spatie bevatten (& nbsp) tekens. David McRitchie heeft een macro geschreven om ze te verwijderen, samen met o andere spaties tekens
Problemen bij het sorteren van VERT.ZOEKEN-formule
Een VERT.ZOEKEN-formule kan in eerste instantie de juiste resultaten opleveren, maar geeft vervolgens onjuiste resultaten weer als de lijst met items is gesorteerd. Dit kan gebeuren als de verwijzing naar de Lookup-waarde een bladnaam bevat. Bijvoorbeeld:
= VERT.ZOEKEN (“Bestelformulier”! B5, Producten! $ B $ 2: $ C $ 6,2, FALSE)
OPMERKING: dit probleem kan optreden bij andere functies ook, zoals een INDEX / MATCH-opzoekformule.
Bekijk deze video om de stappen voor het oplossen van het probleem te zien, en download het voorbeeldbestand VERT.ZOEKEN Sorteerprobleem om verder te volgen. De schriftelijke instructies staan onder de video.
Bladnamen in verwijzing
Dit type verwijzing wordt gemaakt als u op een ander blad klikt tijdens het samenstellen van de formule. Zodra u dat doet, voegt Excel de bladnaam toe aan eventuele volgende verwijzingen in de formule.
In de schermafbeelding hierboven staat Jurk in cel B9 en cel C9 toont de juiste prijs van $ 30.
Na het sorteren van de producten AZ gaat de jurk echter omhoog naar cel B5, maar de formule in cel C5 blijft verwijzen naar cel B9. Vanwege de bladnamen in de verwijzingen, behoudt Excel de originele verwijzingen, in plaats van een verwijzing naar de huidige rij. Cell C5 toont de prijs voor een trui, in plaats van een jurk. ▲ TOP
Los het probleem op
Om het probleem op te lossen, verwijdert u alle onnodige bladnamen uit de VERT.ZOEKEN celverwijzingen. Hier is de herziene formule voor cel C5:
= VERT.ZOEKEN (B5, Producten! $ B $ 2: $ C $ 6,2, FALSE)
Nadat de onnodige bladnamen zijn verwijderd, de lijst kan veilig worden gesorteerd en de juiste resultaten worden voor elk item weergegeven.
Download de voorbeeldbestanden
1. Download de VLOOKUP-voorbeeldwerkmap (Excel 2007 en hoger). Het gecomprimeerde bestand is in xlsx-indeling en bevat geen macros.
2. Download voor Excel 2003 deze versie van het voorbeeldbestand VERT.ZOEKEN. Het gecomprimeerde bestand heeft de indeling xls en bevat geen macros.
3. Om het probleem te zien dat kan optreden bij het sorteren met VERT.ZOEKEN, haalt u dit VLOOKUP Sorteerprobleem voorbeeldbestand op. Het gecomprimeerde bestand is in xlsx-formaat en bevat geen macros.
4. Download de werkmap voor het opzoeken van productprijzen, die wordt gebruikt in de videozelfstudie.Het gecomprimeerde bestand is in xlsx-indeling en bevat geen macros. ▲ TOP
Meer tutorials
VERT.ZOEKEN Nummer / tekst probleemoplossing
VERT.ZOEKEN vanuit een ander werkboek
HORIZ.ZOEKEN
KIES Functie
Opzoeken – 2 criteria
LOOKUP
INDEX / MATCH
Opzoekfuncties vergelijken
Functielijst