20+ VERT.ZOEKEN-voorbeelden voor Excel-beginners en gevorderden

De VERT.ZOEKEN-functie is een van de meest gebruikte functies in Excel. In deze handleiding leer je stap voor stap hoe je de VERT.ZOEKEN-functie in Excel gebruikt, met tientallen basis- en geavanceerde voorbeelden.

Introductie van de VERT.ZOEKEN-functie – syntaxis en argumenten

In Excel is de VERT.ZOEKEN-functie een krachtige functie voor de meeste Excel-gebruikers. Hiermee kun je een waarde zoeken in de meest linkse kolom van het gegevensbereik en een overeenkomende waarde retourneren uit een door jou opgegeven kolom in dezelfde rij, zoals te zien is in de onderstaande schermafbeelding.

De syntaxis van de VERT.ZOEKEN-functie:

Argumenten:

Functie-opmerkingen:

Basisvoorbeelden van VERT.ZOEKEN

In dit gedeelte bespreken we enkele VERT.ZOEKEN-formules die je vaak gebruikt.

2.1 Exacte overeenkomst en benaderende overeenkomst met VERT.ZOEKEN

 2.1.1 Een exacte overeenkomst met VERT.ZOEKEN uitvoeren

Normaal gesproken, als je een exacte overeenkomst zoekt met de VERT.ZOEKEN-functie, hoef je alleen ONWAAR als laatste argument te gebruiken.

Stel dat je de bijbehorende Wiskunde-scores wilt ophalen op basis van specifieke ID-nummers, doe dan het volgende:

Kopieer en plak de onderstaande formule in een lege cel (hier kies ik G2) en druk op de "Enter"-toets om het resultaat te krijgen:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Hoe werkt de VERT.ZOEKEN-formule?

Eerst zoekt de formule naar het ID - C1005 in de meest linkse kolom van de tabel. Hij gaat van boven naar beneden en vindt de waarde in cel A6.

Zodra de waarde is gevonden, gaat de formule naar rechts naar de derde kolom en haalt daar de waarde op.

Je krijgt dan het resultaat zoals hieronder weergegeven:

 2.1.2 Een benaderende overeenkomst met VERT.ZOEKEN uitvoeren

De benaderende overeenkomst is handig voor het zoeken naar waarden tussen gegevensbereiken. Als er geen exacte overeenkomst wordt gevonden, retourneert de benaderende VERT.ZOEKEN de grootste waarde die kleiner is dan de zoekwaarde.

Stel dat je het volgende gegevensbereik hebt en de opgegeven bestellingen staan niet in de kolom Bestellingen. Hoe kun je dan de dichtstbijzijnde korting in kolom B vinden?

Stap1: Pas de VERT.ZOEKEN-formule toe en vul deze in andere cellen in

Kopieer en plak de onderstaande formule in een cel waar je het resultaat wilt plaatsen en sleep vervolgens de vulgreep naar beneden om deze formule toe te passen op andere cellen.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Resultaat:

Nu krijg je de benaderende overeenkomsten op basis van de opgegeven waarden, zie schermafbeelding:

2.2 Een hoofdlettergevoelige VERT.ZOEKEN uitvoeren in Excel

Standaard voert de VERT.ZOEKEN-functie een niet-hoofdlettergevoelige zoekopdracht uit, wat betekent dat kleine en hoofdletters als identiek worden behandeld. Soms wil je echter een hoofdlettergevoelige zoekopdracht uitvoeren in Excel, maar de normale VERT.ZOEKEN-functie kan dit niet oplossen. In dat geval kun je alternatieve functies gebruiken, zoals INDEX en VERGELIJKEN met de EXACT-functie, of de ZOEKEN- en EXACT-functies.

Stel dat ik het volgende gegevensbereik heb waarbij de ID-kolom tekst bevat met hoofdletters of kleine letters. Nu wil ik de bijbehorende Wiskunde-score van het opgegeven ID-nummer retourneren.

Stap1: Pas een van de formules toe en vul deze in andere cellen in

Kopieer en plak een van de onderstaande formules in een lege cel waar je het resultaat wilt krijgen. Selecteer vervolgens de formulecel en sleep de vulgreep naar beneden naar de cellen waar je deze formule wilt invullen.

Formule1: Druk na het plakken van de formule op "Ctrl" + "Shift" + "Enter".

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formule2: Druk na het plakken van de formule op "Enter".

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Resultaat:

Daarna krijg je de juiste resultaten die je nodig hebt. Zie schermafbeelding:

2.3 VERT.ZOEKEN-waarden van rechts naar links in Excel

De VERT.ZOEKEN-functie zoekt altijd een waarde in de meest linkse kolom van een gegevensbereik en retourneert de bijbehorende waarde uit een kolom rechts daarvan. Als je een omgekeerde VERT.ZOEKEN wilt uitvoeren, dus een waarde opzoeken in de rechterkolom en de bijbehorende waarde uit de linkerkolom retourneren, zoals in de onderstaande schermafbeelding:

Klik hier voor een stapsgewijze uitleg over deze taak…

2.4 VERT.ZOEKEN van de tweede, n-de of laatste overeenkomende waarde in Excel

Normaal gesproken, als er meerdere overeenkomende waarden worden gevonden met de VERT.ZOEKEN-functie, wordt alleen het eerste overeenkomende record geretourneerd. In dit gedeelte bespreek ik hoe je de tweede, n-de of laatste overeenkomende waarde in een gegevensbereik kunt ophalen.

 2.4.1 VERT.ZOEKEN en de2e of n-de overeenkomende waarde retourneren

Stel dat je een lijst met namen hebt in kolom A en de trainingscursus die ze hebben aangeschaft in kolom B. Nu wil je de2e of n-de trainingscursus vinden die door de opgegeven klant is gekocht. Zie schermafbeelding:

Hier kan de VERT.ZOEKEN-functie deze taak niet direct oplossen. Maar je kunt de INDEX-functie als alternatief gebruiken.

Stap1: Pas de formule toe en vul deze in andere cellen in

Om bijvoorbeeld de tweede overeenkomende waarde op te halen op basis van het opgegeven criterium, voer je de volgende formule in een lege cel in en druk je tegelijkertijd op "Ctrl" + "Shift" + "Enter" om het eerste resultaat te krijgen. Selecteer vervolgens de formulecel en sleep de vulgreep naar beneden naar de cellen waar je deze formule wilt invullen.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Resultaat:

Nu zijn alle tweede overeenkomende waarden op basis van de opgegeven namen direct weergegeven.

 2.4.2 VERT.ZOEKEN en de laatste overeenkomende waarde retourneren

Als je met VERT.ZOEKEN de laatste overeenkomende waarde wilt retourneren zoals hieronder weergegeven, kan deze handleiding VERT.ZOEKEN en de laatste overeenkomende waarde retourneren je helpen om dit in detail te doen.

2.5 VERT.ZOEKEN van overeenkomende waarden tussen twee opgegeven waarden of datums

Soms wil je waarden opzoeken tussen twee waarden of datums en de bijbehorende resultaten retourneren, zoals te zien is in de onderstaande schermafbeelding. In zo'n geval kun je de ZOEKEN-functie gebruiken in plaats van de VERT.ZOEKEN-functie met een gesorteerde tabel.

 2.5.1 VERT.ZOEKEN van overeenkomende waarden tussen twee opgegeven waarden of datums met een formule

Stap1: Orden de gegevens en pas de volgende formule toe

Je oorspronkelijke tabel moet een gesorteerd gegevensbereik zijn. Kopieer of voer vervolgens de volgende formule in een lege cel in. Sleep daarna de vulgreep om deze formule in andere benodigde cellen in te vullen.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Resultaat:

Nu krijg je alle overeenkomende records op basis van de opgegeven waarde, zie schermafbeelding:

 2.5.2 VERT.ZOEKEN van overeenkomende waarden tussen twee opgegeven waarden of datums met een handige functie

Als je het lastig vindt om de bovenstaande formule te onthouden en te begrijpen, stel ik hier een handig hulpmiddel voor – "Kutools voor Excel". Met de functie "Zoek gegevens tussen twee waarden" kun je eenvoudig het bijbehorende item retourneren op basis van een specifieke waarde of datum tussen twee waarden of datums.

1. Klik op "Kutools" > "Super ZOEKEN" > "Zoek gegevens tussen twee waarden" om deze functie te activeren.
2. Geef vervolgens de bewerkingen op in het dialoogvenster op basis van je gegevens.

2.6 Wildcards gebruiken voor gedeeltelijke overeenkomsten in de VERT.ZOEKEN-functie

In Excel kun je wildcards gebruiken binnen de VERT.ZOEKEN-functie, waarmee je een gedeeltelijke overeenkomst op een zoekwaarde kunt uitvoeren. Je kunt bijvoorbeeld VERT.ZOEKEN gebruiken om een overeenkomende waarde uit een tabel te retourneren op basis van een deel van een zoekwaarde.

Stel dat ik een gegevensbereik heb zoals hieronder weergegeven en nu wil ik de score ophalen op basis van de voornaam (niet de volledige naam). Hoe kun je deze taak in Excel oplossen?

Stap1: Pas de formule toe en vul deze in andere cellen in

Kopieer of voer de volgende formule in een lege cel in en sleep vervolgens de vulgreep om deze formule in andere benodigde cellen in te vullen:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Resultaat:

Alle overeenkomende scores zijn nu geretourneerd zoals hieronder weergegeven:

2.7 VERT.ZOEKEN-waarden uit een ander werkblad

Vaak werk je met meer dan één werkblad. De VERT.ZOEKEN-functie kan worden gebruikt om gegevens uit een ander werkblad op te zoeken, net zoals op één werkblad.

Stel dat je twee werkbladen hebt zoals hieronder weergegeven. Om de bijbehorende gegevens uit het opgegeven werkblad op te zoeken en te retourneren, volg je deze stappen:

Stap1: Pas de formule toe en vul deze in andere cellen in

Voer of kopieer de onderstaande formule in een lege cel waar je de overeenkomende items wilt krijgen. Sleep vervolgens de vulgreep naar beneden naar de cellen waarop je deze formule wilt toepassen.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Resultaat:

Je krijgt de bijbehorende resultaten zoals je nodig hebt, zie schermafbeelding:

2.8 VERT.ZOEKEN-waarden uit een ander werkboek

In dit gedeelte bespreken we hoe je overeenkomende waarden uit een ander werkboek kunt opzoeken en retourneren met de VERT.ZOEKEN-functie.

Stel dat je twee werkboeken hebt. Het eerste werkboek bevat een lijst met producten en hun bijbehorende kosten. In het tweede werkboek wil je de bijbehorende kosten voor elk productitem ophalen, zoals hieronder weergegeven.

Stap1: Pas de formule toe

Open beide werkboeken die je wilt gebruiken en pas vervolgens de volgende formule toe in een cel waar je het resultaat in het tweede werkboek wilt plaatsen. Sleep en kopieer deze formule vervolgens naar andere benodigde cellen.

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Resultaat:

2.9 Leeg of specifieke tekst retourneren in plaats van0 of #N/B-fout

Meestal, wanneer je de VERT.ZOEKEN-functie gebruikt om een bijbehorende waarde te retourneren, wordt0 weergegeven als de overeenkomende cel leeg is. En als de overeenkomende waarde niet wordt gevonden, krijg je een foutwaarde #N/B, zoals hieronder weergegeven. Als je liever een lege cel of een specifieke waarde wilt weergeven in plaats van0 of #N/B, kan deze handleiding VERT.ZOEKEN om leeg of specifieke waarde te retourneren in plaats van0 of N/B je helpen.

Geavanceerde VERT.ZOEKEN-voorbeelden

3.1 Twee-dimensionale zoekopdracht (VERT.ZOEKEN in rij en kolom)

Soms moet je een tweedimensionale zoekopdracht uitvoeren, waarbij je tegelijkertijd zoekt in zowel een rij als een kolom. Stel dat je het volgende gegevensbereik hebt en je wilt de waarde voor een bepaald product in een opgegeven kwartaal ophalen. In dit gedeelte wordt een formule geïntroduceerd om deze taak in Excel uit te voeren.

In Excel kun je een combinatie van de VERT.ZOEKEN- en VERGELIJKEN-functies gebruiken om een tweedimensionale zoekopdracht uit te voeren.

Voer de volgende formule in een lege cel in en druk vervolgens op de "Enter"-toets om het resultaat te krijgen.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 VERT.ZOEKEN op basis van twee of meer criteria

Het is eenvoudig om een overeenkomende waarde op basis van één criterium op te zoeken, maar wat doe je als je twee of meer criteria hebt?

 3.2.1 VERT.ZOEKEN op basis van twee of meer criteria met formules

In dit geval kunnen de ZOEKEN- of VERGELIJKEN- en INDEX-functies in Excel je snel en eenvoudig helpen om deze taak op te lossen.

Stel dat ik de onderstaande datatabel heb. Om de overeenkomende prijs op te halen op basis van het specifieke product en de maat, kunnen de volgende formules je helpen.

Stap1: Pas een van de onderstaande formules toe

Formule1: Voer de volgende formule in en druk op "Enter".

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formule2: Voer de volgende formule in en druk op "Ctrl" + "Shift" + "Enter".

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Resultaat:

 3.2.2 VERT.ZOEKEN op basis van twee of meer criteria met Kutools voor Excel

Het kan lastig zijn om de bovenstaande complexe formules te onthouden die je herhaaldelijk moet toepassen, wat je efficiëntie kan vertragen. Met "Kutools voor Excel" kun je echter met de functie "Meervoudige voorwaarden zoeken" het bijbehorende resultaat ophalen op basis van één of meer voorwaarden met slechts enkele klikken.

1. Klik op "Kutools" > "Super ZOEKEN" > "Meervoudige voorwaarden zoeken" om deze functie te activeren.
2. Geef vervolgens de bewerkingen op in het dialoogvenster op basis van je gegevens.

3.3 VERT.ZOEKEN om meerdere waarden met één of meer criteria te retourneren

In Excel zoekt de VERT.ZOEKEN-functie naar een waarde en retourneert alleen de eerste overeenkomende waarde als er meerdere overeenkomende waarden zijn gevonden. Soms wil je echter alle bijbehorende waarden in een rij, kolom of in één cel retourneren. In dit gedeelte bespreken we hoe je meerdere overeenkomende waarden met één of meer voorwaarden in een werkboek kunt retourneren.

 3.3.1 VERT.ZOEKEN van alle overeenkomende waarden op basis van één of meer voorwaarden horizontaal

Stel dat je een tabel met gegevens hebt met land, stad en namen in het bereik A1:C14, en nu wil je alle namen horizontaal retourneren die uit "US" komen, zoals hieronder weergegeven. Om deze taak op te lossen, klik hier voor een stapsgewijze uitleg.

 3.3.2 VERT.ZOEKEN van alle overeenkomende waarden op basis van één of meer voorwaarden verticaal

Als je alle overeenkomende waarden verticaal wilt retourneren op basis van specifieke criteria, zoals hieronder weergegeven, klik dan hier voor de gedetailleerde oplossing.

 3.3.3 VERT.ZOEKEN van alle overeenkomende waarden op basis van één of meer voorwaarden in één cel

Als je meerdere overeenkomende waarden in één cel wilt retourneren met een opgegeven scheidingsteken, kan de nieuwe functie TEXTJOIN je snel en eenvoudig helpen.

3.4 VERT.ZOEKEN om een volledige rij van een overeenkomende cel te retourneren

In dit gedeelte bespreek ik hoe je met de VERT.ZOEKEN-functie de volledige rij van een overeenkomende waarde kunt ophalen.

Stap1: Pas de volgende formule toe

Kopieer of typ de onderstaande formule in een lege cel waar je het resultaat wilt weergeven en druk op de "Enter"-toets om de eerste waarde te krijgen. Sleep vervolgens de formulecel naar rechts totdat de gegevens van de volledige rij worden weergegeven.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Resultaat:

Nu zie je dat de volledige rijgegevens zijn geretourneerd. Zie schermafbeelding:

3.5 Geneste VERT.ZOEKEN in Excel

Soms moet je waarden opzoeken die met elkaar verbonden zijn over meerdere tabellen. In dat geval kun je meerdere VERT.ZOEKEN-functies nesten om de uiteindelijke waarde te krijgen.

Stel dat ik een werkblad heb met twee aparte tabellen. De eerste tabel bevat alle productnamen met hun bijbehorende verkoper. De tweede tabel bevat de totale verkoop per verkoper. Als je nu de verkoop van elk product wilt vinden, zoals in de onderstaande schermafbeelding, kun je de VERT.ZOEKEN-functie nesten om deze taak uit te voeren.

De algemene formule voor een geneste VERT.ZOEKEN-functie is:

Stap1: Pas en vul de volgende formule toe

Voer de volgende formule in een lege cel in en sleep vervolgens de vulgreep naar beneden naar de cellen waarop je deze formule wilt toepassen.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Resultaat:

Nu krijg je het resultaat zoals weergegeven in de onderstaande schermafbeelding:

3.6 Controleren of een waarde bestaat op basis van een lijst in een andere kolom

Met de VERT.ZOEKEN-functie kun je ook controleren of waarden bestaan op basis van een gegevenslijst in een andere kolom. Stel dat je de namen in kolom C wilt opzoeken en alleen Ja of Nee wilt retourneren als de naam wel of niet in kolom A voorkomt, zoals hieronder weergegeven.

Stap1: Pas de volgende formule toe

Voer de volgende formule in een lege cel in en sleep vervolgens de vulgreep naar beneden naar de cellen waarin je deze formule wilt invullen.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Resultaat:

En je krijgt het resultaat zoals je nodig hebt, zie schermafbeelding:

3.7 VERT.ZOEKEN en alle overeenkomende waarden optellen in rijen of kolommen

Bij het werken met numerieke gegevens moet je mogelijk overeenkomende waarden uit een tabel halen en de getallen in meerdere kolommen of rijen optellen. In dit gedeelte worden enkele formules geïntroduceerd die je hierbij kunnen helpen.

 3.7.1 VERT.ZOEKEN en alle overeenkomende waarden optellen in een rij of meerdere rijen

Stel dat je een productlijst hebt met verkopen over meerdere maanden, zoals hieronder weergegeven. Nu moet je alle bestellingen in alle maanden optellen op basis van de opgegeven producten.

Stap1: Pas de volgende formule toe

Kopieer of voer de volgende formule in een lege cel in en druk vervolgens tegelijkertijd op "Ctrl" + "Shift" + "Enter" om het eerste resultaat te krijgen. Sleep daarna de vulgreep naar beneden om deze formule naar andere benodigde cellen te kopiëren.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Resultaat:

Alle waarden in een rij van de eerste overeenkomende waarde zijn bij elkaar opgeteld, zie schermafbeelding:

 3.7.2 VERT.ZOEKEN en alle overeenkomende waarden optellen in een kolom of meerdere kolommen

Als je de totale waarde voor specifieke maanden wilt optellen, zoals hieronder weergegeven, kan de normale VERT.ZOEKEN-functie je niet helpen. Gebruik hier de functies SOM, INDEX en VERGELIJKEN samen om een formule te maken.

Stap1: Pas de volgende formule toe

Voer de onderstaande formule in een lege cel in en sleep vervolgens de vulgreep naar beneden om deze formule naar andere benodigde cellen te kopiëren.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Resultaat:

Nu zijn de eerste overeenkomende waarden op basis van de specifieke maand in een kolom bij elkaar opgeteld, zie schermafbeelding:

 3.7.3 VERT.ZOEKEN en de eerste of alle overeenkomende waarden optellen met Kutools voor Excel

Misschien zijn de bovenstaande formules lastig te onthouden. In dat geval raad ik een krachtige functie aan - "Zoek en som" van "Kutools voor Excel". Met deze functie kun je eenvoudig de eerste of alle overeenkomende waarden in rijen of kolommen optellen.

1. Klik op "Kutools" > "Super ZOEKEN" > "Zoek en som" om deze functie te activeren.
2. Geef vervolgens de bewerkingen op in het dialoogvenster op basis van je behoefte.

 3.7.4 VERT.ZOEKEN en alle overeenkomende waarden optellen in zowel rijen als kolommen

Als je de waarden wilt optellen waarbij je zowel op kolom als rij moet matchen, bijvoorbeeld om de totale waarde van het product Trui in de maand maart te krijgen, zoals hieronder weergegeven.

Hier kun je de SOMPRODUCT-functie gebruiken om deze taak uit te voeren.

Voer de volgende formule in een cel in en druk vervolgens op de "Enter"-toets om het resultaat te krijgen, zie schermafbeelding:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VERT.ZOEKEN om twee tabellen samen te voegen op basis van sleutelkolommen

In je dagelijkse werk, bij het analyseren van gegevens, moet je mogelijk alle benodigde informatie samenbrengen in één tabel op basis van één of meer sleutelkolommen. Om deze taak uit te voeren, kun je de INDEX- en VERGELIJKEN-functies gebruiken in plaats van de VERT.ZOEKEN-functie.

 3.8.1 VERT.ZOEKEN om twee tabellen samen te voegen op basis van één sleutelkolom

Stel dat je twee tabellen hebt: de eerste tabel bevat de producten en namen, en de tweede tabel bevat de producten en bestellingen. Nu wil je deze twee tabellen combineren door de gemeenschappelijke productkolom samen te voegen tot één tabel.

Stap1: Pas de volgende formule toe

Voer de volgende formule in een lege cel in. Sleep vervolgens de vulgreep naar beneden naar de cellen waarop je deze formule wilt toepassen.

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Resultaat:

Nu krijg je een samengevoegde tabel waarbij de kolom Bestellingen aan de eerste tabel is toegevoegd op basis van de sleutelkolomgegevens.

 3.8.2 VERT.ZOEKEN om twee tabellen samen te voegen op basis van meerdere sleutelkolommen

Als de twee tabellen die je wilt samenvoegen meerdere sleutelkolommen hebben, volg dan de onderstaande stappen om de tabellen samen te voegen op basis van deze gemeenschappelijke kolommen.

De algemene formule is:

Stap1: Pas de volgende formule toe

Voer de onderstaande formule in een lege cel in waar je het resultaat wilt plaatsen en druk vervolgens tegelijkertijd op "Ctrl" + "Shift" + "Enter" om de eerste overeenkomende waarde te krijgen, zie schermafbeelding:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Stap2: Vul de formule in andere cellen in

Selecteer vervolgens de eerste formulecel en sleep de vulgreep om deze formule naar andere benodigde cellen te kopiëren:

3.9 VERT.ZOEKEN van overeenkomende waarden over meerdere werkbladen

Heb je ooit een VERT.ZOEKEN moeten uitvoeren over meerdere werkbladen in Excel? Stel dat je drie werkbladen hebt met gegevensbereiken en je wilt specifieke waarden ophalen op basis van criteria uit deze bladen. Volg dan de stapsgewijze handleiding VERT.ZOEKEN-waarden over meerdere werkbladen om deze taak uit te voeren.

VERT.ZOEKEN met behoud van celopmaak

Bij het opzoeken van overeenkomende waarden wordt de oorspronkelijke celopmaak, zoals letterkleur, achtergrondkleur, gegevensopmaak, enzovoort, niet behouden. In dit gedeelte worden enkele trucs besproken om de opmaak van cellen of gegevens te behouden.

4.1 VERT.ZOEKEN van overeenkomende waarde en behoud van celkleur, lettertype-opmaak

Zoals bekend kan de normale VERT.ZOEKEN-functie alleen de overeenkomende waarde uit een ander gegevensbereik ophalen. Soms wil je echter de bijbehorende waarde samen met de celopmaak ophalen, zoals vulkleur, letterkleur en letterstijl. In dit gedeelte bespreken we hoe je overeenkomende waarden kunt ophalen met behoud van de bronopmaak in Excel.

Volg de onderstaande stappen om de bijbehorende waarde samen met de celopmaak op te zoeken en te retourneren:

Stap1: Kopieer code1 in het werkbladcodevenster

1. Klik met de rechtermuisknop op het werkblad met de gegevens die je wilt opzoeken, kies "Code weergeven" in het contextmenu. Zie schermafbeelding:
   
2. Kopieer in het geopende venster "Microsoft Visual Basic for Applications" de onderstaande VBA-code in het codevenster.
3. VBA-code1: VERT.ZOEKEN om celopmaak samen met de zoekwaarde op te halen

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Stap2: Kopieer code2 in het modulevenster

1. Blijf in het venster "Microsoft Visual Basic for Applications", klik op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code2 in het "Module"-venster.
2. VBA-code2: VERT.ZOEKEN om celopmaak samen met de zoekwaarde op te halen

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Stap3: Selecteer de optie voor VBAproject

1. Na het invoegen van bovenstaande codes, klik op "Extra" > "Verwijzingen" in het venster "Microsoft Visual Basic for Applications". Vink vervolgens het selectievakje "Microsoft Scripting Runtime" aan in het dialoogvenster "Verwijzingen – VBAProject". Zie schermafbeeldingen:
2. Klik vervolgens op "OK" om het dialoogvenster te sluiten en sla het codevenster op en sluit het.

Stap4: Typ de formule om het resultaat te krijgen

1. Ga nu terug naar het werkblad, pas de volgende formule toe. Sleep vervolgens de vulgreep naar beneden om alle resultaten samen met hun opmaak op te halen. Zie schermafbeelding:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 De datumopmaak behouden van een VERT.ZOEKEN-resultaat

Wanneer je de VERT.ZOEKEN-functie gebruikt om een waarde met datumopmaak op te zoeken en te retourneren, kan het resultaat als een getal worden weergegeven. Om de datumopmaak in het resultaat te behouden, moet je de VERT.ZOEKEN-functie binnen de TEKST-functie plaatsen.

Stap1: Pas de volgende formule toe

Voer de onderstaande formule in een lege cel in. Sleep vervolgens de vulgreep om deze formule naar andere benodigde cellen te kopiëren.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Resultaat:

Alle overeenkomende datums zijn nu geretourneerd zoals hieronder weergegeven:

4.3 Celopmerking retourneren met VERT.ZOEKEN

Heb je ooit zowel de overeenkomende celgegevens als de bijbehorende opmerking willen ophalen met VERT.ZOEKEN in Excel, zoals hieronder weergegeven? In dat geval kan de onderstaande User Defined Function je helpen deze taak uit te voeren.

Stap1: Kopieer de code in een module

1. Houd de toetsen "ALT" + "F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.
2. Klik op "Invoegen" > "Module" en kopieer en plak vervolgens de onderstaande code in het "Module"-venster.
   VBA-code: VERT.ZOEKEN en overeenkomende waarde met celopmerking retourneren:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Sla vervolgens het codevenster op en sluit het.

Stap2: Typ de formule om het resultaat te krijgen

1. Voer nu de onderstaande formule in en sleep de vulgreep om deze formule naar andere benodigde cellen te kopiëren. Hiermee worden zowel de overeenkomende waarden als opmerkingen tegelijkertijd geretourneerd, zie schermafbeelding:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 VERT.ZOEKEN van getallen die als tekst zijn opgeslagen

Stel dat ik een gegevensbereik heb waarbij het ID-nummer in de oorspronkelijke tabel als getal is opgeslagen en het ID-nummer in de opzoekcellen als tekst is opgeslagen. Je kunt dan een #N/B-fout krijgen bij het gebruik van de normale VERT.ZOEKEN-functie. Om in dit geval de juiste informatie op te halen, kun je de TEKST- en WAARDE-functies binnen de VERT.ZOEKEN-functie gebruiken. Hieronder vind je de formule om dit te bereiken:

Stap1: Pas en vul de volgende formule toe

Voer de volgende formule in een lege cel in en sleep vervolgens de vulgreep om deze formule te kopiëren.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Resultaat:

Nu krijg je de juiste resultaten zoals hieronder weergegeven: