20+ VERT.ZOEKEN-voorbeelden voor Excel-beginners en gevorderde gebruikers

De functie VERT.ZOEKEN is een van de meest populaire functies in Excel. In deze zelfstudie wordt stap voor stap uitgelegd hoe u de functie VERT.ZOEKEN in Excel kunt gebruiken met tientallen basis- en geavanceerde voorbeelden.

Introductie van de functie VERT.ZOEKEN - syntaxis en argumenten

In Excel is de functie VERT.ZOEKEN een krachtige functie voor de meeste Excel-gebruikers. Hiermee kunt u zoeken naar een waarde in het meest linkse deel van het gegevensbereik en een overeenkomende waarde retourneren in dezelfde rij uit een kolom die u hebt opgegeven, zoals in het volgende screenshot wordt getoond .

De syntaxis van de functie VERT.ZOEKEN:

Argumenten:

Functie-opmerkingen:

Eenvoudige voorbeelden van VERT.ZOEKEN

In dit gedeelte zullen we het hebben over enkele Vlookup-formules die u vaak hebt gebruikt.

2.1 Exacte overeenkomst en geschatte overeenkomst VERT.ZOEKEN

 2.1.1 Doe een exacte match VERT.ZOEKEN

Normaal gesproken hoeft u, als u op zoek bent naar een exacte overeenkomst met de functie VERT.ZOEKEN, alleen FALSE als laatste argument te gebruiken.

Om bijvoorbeeld de corresponderende wiskundige scores te krijgen op basis van de specifieke ID-nummers, gaat u als volgt te werk:

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

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

Hoe werkt de VLOOKUP-formule?

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

Zodra de waarde gevonden is, gaat het programma direct naar de derde kolom en haalt de waarde daaruit op.

U krijgt dus het resultaat zoals hieronder afgebeeld:

 2.1.2 Doe een match bij benadering VERT.ZOEKEN

De benaderende overeenkomst is handig voor het zoeken naar waarden tussen gegevensbereiken. Als de exacte overeenkomst niet wordt gevonden, retourneert VLOOKUP bij benadering de grootste waarde die kleiner is dan de opzoekwaarde.

Als u bijvoorbeeld het volgende gegevensbereik heeft en de opgegeven bestellingen niet in de kolom Bestellingen staan, hoe krijgt u dan de dichtstbijzijnde korting in kolom B?

Stap 1: Pas de VLOOKUP-formule toe en vul deze in andere cellen

Kopieer en plak de volgende formule in een cel waarin u 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 krijgt u de geschatte overeenkomsten op basis van de gegeven waarden, zie screenshot:

2.2 Voer een hoofdlettergevoelige VERT.ZOEKEN uit in Excel

De functie VERT.ZOEKEN voert standaard een niet-hoofdlettergevoelige zoekopdracht uit, wat betekent dat kleine letters en hoofdletters als identiek worden behandeld. Soms moet u mogelijk een hoofdlettergevoelige zoekopdracht uitvoeren in Excel, de normale VERT.ZOEKEN-functie lost dit mogelijk niet op. In dit geval kunt u alternatieve functies gebruiken, zoals INDEX en MATCH met de EXACT-functie, of de LOOKUP- en EXACT-functies.

Ik heb bijvoorbeeld het volgende gegevensbereik waarvan de ID-kolom een ​​tekstreeks met hoofdletters of kleine letters bevat, nu wil ik de overeenkomstige Math-score van het opgegeven ID-nummer retourneren.

Stap 1: Pas een formule toe en vul deze in andere cellen

Kopieer en plak een van de onderstaande formules in een lege cel waar u het resultaat wilt krijgen. Selecteer vervolgens de formulecel, sleep de vulgreep omlaag naar de cellen waarin u deze formule wilt vullen.

Formule 1: Nadat u de formule hebt geplakt, drukt u op de toetsen "Ctrl" + "Shift" + "Enter".

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

Formule 2: Nadat u de formule hebt geplakt, drukt u op de toets "Enter".

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

Resultaat:

Dan krijg je de juiste resultaten die je nodig hebt. Zie screenshot:

2.3 VERT.ZOEKEN waarden van rechts naar links in Excel

De functie VERT.ZOEKEN zoekt altijd naar een waarde in de meest linkse kolom van een gegevensbereik en retourneert de corresponderende waarde uit een kolom aan de rechterkant. Als u een omgekeerde VERT.ZOEKEN wilt uitvoeren, wat betekent dat u een specifieke waarde in de rechterkolom opzoekt en de overeenkomstige waarde in de linkerkolom retourneert, zoals onderstaand screenshot laat zien:

Klik om de details stap voor stap over deze taak te kennen ...

2.4 VLOOKUP de tweede, n-de of laatste overeenkomende waarde in Excel

Als er meerdere overeenkomende waarden worden gevonden bij gebruik van de Vlookup-functie, wordt normaal gesproken alleen het eerste overeenkomende record geretourneerd. In dit gedeelte zal ik het hebben over het verkrijgen van de tweede, n-de of laatste overeenkomende waarde in een gegevensbereik.

 2.4.1 VLOOKUP en retourneer de 2e of n-de overeenkomende waarde

Stel dat je een lijst met namen hebt in kolom A, de training die ze hebben gekocht in kolom B. Nu ben je op zoek naar de 2e of nde training die door de gegeven klant is gekocht. Zie screenshot:

Hier lost de VERT.ZOEKEN-functie deze taak mogelijk niet direct op. Maar u kunt de INDEX-functie als alternatief gebruiken.

Stap 1: Pas de formule toe en vul deze in andere cellen

Om bijvoorbeeld de tweede overeenkomende waarde te krijgen op basis van de gegeven criteria, past u de volgende formule toe in een lege cel en drukt u tegelijkertijd op de toetsen "Ctrl" + "Shift" + "Enter" om het eerste resultaat te krijgen. Selecteer vervolgens de formulecel en sleep de vulgreep naar beneden naar de cellen waar u deze formule wilt vullen.

=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 in één keer weergegeven.

 2.4.2 VERT.ZOEKEN en retourneer de laatste overeenkomende waarde

Als u wilt opvullen en de laatste overeenkomende waarde wilt retourneren zoals onderstaand screenshot, dit VERT.ZOEKEN en retourneer de laatste overeenkomende waarde zelfstudie kan u helpen om de laatste overeenkomende waarde in details te krijgen.

2.5 VERT.ZOEKEN overeenkomende waarden tussen twee gegeven waarden of datums

Soms wilt u waarden tussen twee waarden of datums opzoeken en de overeenkomstige resultaten retourneren, zoals weergegeven in de onderstaande schermafbeelding. In dat geval kunt u de functie ZOEKEN gebruiken in plaats van de functie VERT.ZOEKEN met een gesorteerde tabel.

 2.5.1 VERT.ZOEKEN overeenkomende waarden tussen twee gegeven waarden of datums met formule

Stap 1: Rangschik de gegevens en pas de volgende formule toe

Uw oorspronkelijke tabel moet een gesorteerd gegevensbereik zijn. En kopieer of voer vervolgens de volgende formule in een lege cel in. Sleep vervolgens de vulgreep om deze formule te vullen met andere cellen die u nodig hebt.

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

Resultaat:

En nu krijgt u alle overeenkomende records op basis van de opgegeven waarde, zie screenshot:

 2.5.2 VLOOKUP-matchingwaarden tussen twee gegeven waarden of datums met een handige functie

Als u het moeilijk vindt om de bovenstaande formule te onthouden en te begrijpen, zal ik hier een eenvoudig hulpmiddel introduceren: "Kutools voor Excel". Met de functie "ZOEKEN tussen twee waarden" kunt u eenvoudig het overeenkomstige item retourneren op basis van de specifieke waarde of datum tussen twee waarden of datums.

1. Klik op "Kutools" > "Super LOOKUP" > "LOOKUP tussen twee waarden" om deze functie in te schakelen.
2. Specificeer vervolgens de bewerkingen in het dialoogvenster op basis van uw gegevens.

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

In Excel kunnen de jokertekens worden gebruikt binnen de functie VERT.ZOEKEN, waarmee u een gedeeltelijke overeenkomst op een opzoekwaarde kunt uitvoeren. U kunt bijvoorbeeld VERT.ZOEKEN gebruiken om overeenkomende waarden uit een tabel te retourneren op basis van een deel van een opzoekwaarde.

Stel dat ik een reeks gegevens heb zoals onderstaand screenshot laat zien, nu wil ik de score extraheren op basis van de voornaam (niet de volledige naam). Hoe kan deze taak in Excel worden opgelost?

Stap 1: Pas de formule toe en vul deze in op andere cellen

Kopieer of voer de volgende formule in een lege cel in en sleep vervolgens de vulgreep om deze formule te vullen met andere cellen die u nodig hebt:

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

Resultaat:

En alle overeenkomende scores zijn geretourneerd zoals onderstaand screenshot getoond:

2.7 VLOOKUP-waarden van een ander werkblad

Meestal moet u met meer dan één werkblad werken. De functie VERT.ZOEKEN kan worden gebruikt om gegevens van een ander blad op te zoeken, net als op één werkblad.

U hebt bijvoorbeeld twee werkbladen zoals onderstaand screenshot getoond, om de corresponderende gegevens op te zoeken en te retourneren uit het werkblad dat u hebt opgegeven, voert u de volgende stappen uit:

Stap 1: Pas de formule toe en vul deze in op andere cellen

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

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

Resultaat:

U krijgt de overeenkomstige resultaten zoals u nodig heeft, zie screenshot:

2.8 VLOOKUP-waarden uit een andere werkmap

In dit gedeelte wordt gesproken over het opzoeken en retourneren van de overeenkomende waarden uit een andere werkmap met behulp van de functie VERT.ZOEKEN.

Stel dat u twee werkmappen hebt. De eerste werkmap bevat een lijst met producten en hun respectieve kosten. In de tweede werkmap wilt u de bijbehorende kosten voor elk productitem extraheren, zoals onderstaand screenshot.

Stap 1: Pas de formule toe

Open beide werkmappen die u wilt gebruiken en pas vervolgens de volgende formule toe in een cel waar u het resultaat in de tweede werkmap wilt plaatsen. Sleep en kopieer vervolgens deze formule naar andere cellen die u nodig hebt

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

Resultaat:

2.9 Blanco of specifieke tekst retourneren in plaats van 0 of #N/A-fout

Wanneer u de functie VERT.ZOEKEN gebruikt om een ​​overeenkomstige waarde te retourneren, zal de overeenkomende cel, als deze leeg is, meestal 0 retourneren. En als de overeenkomende waarde niet wordt gevonden, krijgt u een foutwaarde van #N/A, zoals weergegeven in de schermafbeelding hieronder. Als u een lege cel of een specifieke waarde wilt weergeven in plaats van 0 of #N/A, dit VERT.ZOEKEN Om blanco of specifieke waarde te retourneren in plaats van 0 of n.v.t tutorial kan je een plezier doen.

Geavanceerde VLOOKUP-voorbeelden

3.1 Opzoeken in twee richtingen (VERT.ZOEKEN in rij en kolom)

Soms moet u een tweedimensionale zoekopdracht uitvoeren, wat betekent dat u tegelijkertijd naar een waarde in zowel rij als kolom moet zoeken. Als u bijvoorbeeld het volgende gegevensbereik heeft en u mogelijk de waarde voor een bepaald product in een bepaald kwartaal wilt weten. In dit gedeelte wordt een formule geïntroduceerd voor het afhandelen van deze taak in Excel.

In Excel kunt u een combinatie van VLOOKUP en MATCH-functies gebruiken om in twee richtingen te zoeken.

Voer de volgende formule uit in een lege cel en druk 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-overeenkomstige waarde op basis van twee of meer criteria

U kunt de matchingswaarde gemakkelijk opzoeken op basis van één criterium, maar als u twee of meer criteria heeft, wat kunt u dan doen?

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

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

Ik heb bijvoorbeeld de onderstaande gegevenstabel, om de overeenkomende prijs te retourneren op basis van het specifieke product en de maat, kunnen de volgende formules u helpen.

Stap 1: Pas een van de onderstaande formules toe

Formule 1: 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))

Formule 2: 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 VLOOKUP-matchingwaarde op basis van twee of meer criteria met Kutools voor Excel

Het kan lastig zijn om de bovenstaande complexe formules te onthouden die herhaaldelijk moeten worden toegepast, wat uw werkefficiëntie kan vertragen. "Kutools for Excel" biedt echter een "Multi-condition Lookup"-functie waarmee u het overeenkomstige resultaat kunt retourneren op basis van een of meer voorwaarden met slechts enkele klikken.

1. Klik op "Kutools" > "Super LOOKUP" > "Multi-condition Lookup" om deze functie in te schakelen.
2. Specificeer vervolgens de bewerkingen in het dialoogvenster op basis van uw gegevens.

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

In Excel zoekt de functie VERT.ZOEKEN naar een waarde en retourneert alleen de eerste overeenkomende waarde als er meerdere overeenkomende waarden zijn gevonden. Soms wilt u misschien alle corresponderende waarden in een rij, kolom of enkele cel retourneren. In dit gedeelte wordt besproken hoe u meerdere overeenkomende waarden kunt retourneren met een of meer voorwaarden in een werkmap.

 3.3.1 VLOOKUP alle overeenkomende waarden op basis van een of meer voorwaarden horizontaal

Ervan uitgaande dat u een gegevenstabel heeft met land, stad en namen in het bereik A1: C14, en nu wilt u alle namen horizontaal retourneren die uit "VS" komen, zoals onderstaand screenshot. Om deze taak op te lossen, alstublieft klik hier om stap voor stap het resultaat te zien.

 3.3.2 VLOOKUP alle overeenkomende waarden op basis van een of meer voorwaarden verticaal

Als u alle overeenkomende waarden verticaal moet opzoeken en retourneren op basis van specifieke criteria, zoals onderstaand screenshot, klik hier voor de oplossing in detail.

 3.3.3 VLOOKUP alle overeenkomende waarden op basis van een of meer voorwaarden in één cel

Als u wilt Vlookup en meerdere overeenkomende waarden wilt retourneren in een enkele cel met opgegeven scheidingsteken, de nieuwe functie van TEXTJOIN kan je helpen om deze klus snel en gemakkelijk op te lossen.

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

In dit gedeelte zal ik het hebben over het ophalen van de volledige rij van een overeenkomende waarde met behulp van de functie VERT.ZOEKEN.

Stap 1: Pas de volgende formule toe

Kopieer of typ de onderstaande formule in een lege cel waar u 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 hele rij worden weergegeven.

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

Resultaat:

Nu kunt u zien dat de volledige rijgegevens worden geretourneerd. Zie screenshot:

3.5 Geneste VERT.ZOEKEN in Excel

Soms moet u waarden opzoeken die in meerdere tabellen met elkaar zijn verbonden. In dit geval kunt u meerdere VERT.ZOEKEN-functies samen nesten om de uiteindelijke waarde te krijgen.

Ik heb bijvoorbeeld een werkblad met twee afzonderlijke tabellen. De eerste tabel bevat alle productnamen samen met hun corresponderende verkoper. De tweede tabel geeft een overzicht van de totale verkopen van elke verkoper. Als u nu de verkopen van elk product wilt vinden, zoals weergegeven in de volgende schermafbeelding, kunt u de functie VERT.ZOEKEN nesten om deze taak uit te voeren.

De generieke formule voor geneste VLOOKUP-functie is:

Stap 1: Pas de volgende formule toe en vul deze in

Pas de volgende formule toe in een lege cel en sleep de vulgreep vervolgens naar de cellen waarop u deze formule wilt toepassen.

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

Resultaat:

Nu krijgt u het resultaat zoals weergegeven in de volgende schermafbeelding:

3.6 Controleer of er een waarde bestaat op basis van een lijst met gegevens in een andere kolom

De functie VERT.ZOEKEN kan u ook helpen om te controleren of er waarden bestaan ​​op basis van de gegevenslijst in een andere kolom. Als u bijvoorbeeld naar de namen in kolom C wilt zoeken en gewoon Ja of Nee wilt retourneren als de naam wel of niet in kolom A wordt gevonden, zoals onderstaand screenshot.

Stap 1: Pas de volgende formule toe

Pas de volgende formule toe in een lege cel en sleep vervolgens de vulgreep naar de cellen waarmee u deze formule wilt vullen.

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

Resultaat:

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

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

Wanneer u met numerieke gegevens werkt, moet u mogelijk overeenkomende waarden uit een tabel extraheren en de getallen in meerdere kolommen of rijen optellen. In dit gedeelte worden enkele formules geïntroduceerd die u kunnen helpen deze taak te volbrengen.

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

Stel dat u een productlijst met verkopen voor meerdere maanden heeft, zoals weergegeven in de volgende schermafbeelding. Nu moet u alle bestellingen in alle maanden optellen op basis van de gegeven producten.

Stap 1: Pas de volgende formule toe

Kopieer of typ de volgende formule in een lege cel en druk vervolgens op de toetsen "Ctrl" + "Shift" + "Enter" om het eerste resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden om deze formule te kopiëren naar andere cellen die u nodig hebt.

=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 screenshot:

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

Als u de totale waarde voor de specifieke maanden wilt optellen, zoals weergegeven in de onderstaande schermafbeelding. De normale VERT.ZOEKEN-functie helpt u misschien niet, hier moet u de SOM-, INDEX- en VERGELIJKEN-functies samen toepassen om een ​​formule te maken.

Stap 1: Pas de volgende formule toe

Pas de onderstaande formule toe in een lege cel en sleep de vulgreep naar beneden om deze formule naar andere 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 screenshot:

 3.7.3 VLOOKUP en som de eerste overeenkomende of alle overeenkomende waarden op met Kutools voor Excel

Misschien vindt u de bovenstaande formules moeilijk te onthouden. In dat geval raad ik u een krachtige functie aan: 'Opzoeken en optellen' van 'Kutools voor Excel'. Met deze functie kunt u de eerste overeenkomende of alle overeenkomende waarden in rijen of kolommen zo eenvoudig mogelijk vergelijken en optellen.

1. Klik op "Kutools" > "Super LOOKUP" > "LOOKUP and Sum" om deze functie in te schakelen.
2. Specificeer vervolgens de bewerkingen in het dialoogvenster op basis van uw behoefte.

 3.7.4 VERT.ZOEKEN en som alle overeenkomende waarden op, zowel in rijen als in kolommen

Als u de waarden wilt optellen wanneer u zowel kolom als rij moet matchen, bijvoorbeeld om de totale waarde van het product Trui in maand maart te krijgen, zoals onderstaand screenshot.

Hier kunt u de SUMPRODCT-functie gebruiken om deze taak uit te voeren.

Pas de volgende formule toe op een cel 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 uw dagelijkse werk, bij het analyseren van gegevens, moet u mogelijk alle benodigde informatie verzamelen in een enkele tabel op basis van een of meer sleutelkolommen. Om deze taak te volbrengen, kunt u de INDEX- en MATCH-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

U hebt bijvoorbeeld twee tabellen, de eerste tabel bevat de gegevens over producten en namen en de tweede tabel bevat de gegevens over producten en bestellingen. Nu wilt u deze twee tabellen combineren door de gemeenschappelijke productkolom in één tabel te plaatsen.

Stap 1: Pas de volgende formule toe

Pas de volgende formule toe in een lege cel. Sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen

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

Resultaat:

Nu krijgt u een samengevoegde tabel met de volgordekolom die aansluit op de eerste tabel 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 u wilt samenvoegen meerdere sleutelkolommen hebben, volgt u de onderstaande stappen om de tabellen samen te voegen op basis van deze gemeenschappelijke kolommen.

De generieke formule is:

Stap 1: Pas de volgende formule toe

Pas de onderstaande formule toe in een lege cel waar u het resultaat wilt plaatsen en druk vervolgens tegelijkertijd op de toetsen "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)

Stap 2: Vul de formule in voor andere cellen

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

3.9 VLOOKUP overeenkomende waarden over meerdere werkbladen

Heeft u ooit een VERT.ZOEKEN moeten uitvoeren op meerdere werkbladen in Excel? Als u bijvoorbeeld drie werkbladen met gegevensbereiken hebt en u wilt specifieke waarden ophalen op basis van criteria uit deze bladen, kunt u de stapsgewijze zelfstudie volgen VLOOKUP Waarden over meerdere werkbladen om deze taak te volbrengen.

VERT.ZOEKEN overeenkomende waarden behouden de celopmaak

Bij het opzoeken van overeenkomende waarden wordt de originele celopmaak zoals lettertypekleur, achtergrondkleur, gegevensindeling, enz. niet bewaard. Om de opmaak van cellen of gegevens te behouden, introduceert dit gedeelte enkele trucs voor het oplossen van de taken.

4.1 VLOOKUP-overeenkomstige waarde en behoud celkleur, lettertype-opmaak

Zoals we allemaal weten, kan de normale VLOOKUP-functie alleen de overeenkomende waarde uit een ander gegevensbereik ophalen. Er kunnen echter gevallen zijn waarin u de overeenkomstige waarde samen met de celopmaak wilt hebben, zoals de vulkleur, letterkleur en letterstijl. In deze sectie bespreken we hoe u overeenkomende waarden kunt ophalen met behoud van de bronopmaak in Excel.

Voer de volgende stappen uit om de bijbehorende waarde op te zoeken en te retourneren, samen met de celopmaak:

Stap 1: Kopieer de code 1 in de Sheet Code Module

1. In het werkblad staan ​​de gegevens die u wilt VLOOKUP, klik met de rechtermuisknop op het tabblad van het blad en selecteer "View Code" in het contextmenu. Zie screenshot:
   
2. Kopieer in het geopende venster "Microsoft Visual Basic for Applications" de onderstaande VBA-code naar het codevenster.
3. VBA-code 1: VERT.ZOEKEN om celopmaak samen met opzoekwaarde te krijgen

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. 

Stap 2: Kopieer de code 2 naar het modulevenster

1. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code 2 naar het venster "Module".
2. VBA-code 2: VERT.ZOEKEN om celopmaak samen met opzoekwaarde te krijgen

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. 

Stap 3: Selecteer de optie voor VBAproject

1. Nadat u de bovenstaande codes hebt ingevoegd, klikt u 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 screenshots:
2. Klik vervolgens op 'OK' om het dialoogvenster te sluiten. Sla de code op en sluit het venster.

Stap 4: Typ de formule om het resultaat te krijgen

1. Ga nu terug naar het werkblad en pas de volgende formule toe. Sleep vervolgens de vulgreep naar beneden om alle resultaten samen met hun opmaak te krijgen. Zie screenshot:
   

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

   
   

4.2 Bewaar de datumnotatie van een VLOOKUP geretourneerde waarde

Wanneer u de functie VERT.ZOEKEN gebruikt om een ​​waarde met datumnotatie op te zoeken en te retourneren, kan het geretourneerde resultaat als een getal worden weergegeven. Om de datumnotatie in het geretourneerde resultaat te behouden, moet u de functie VERT.ZOEKEN insluiten in de functie TEKST.

Stap 1: Pas de volgende formule toe

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

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

Resultaat:

Alle overeenkomende datums zijn geretourneerd zoals onderstaand screenshot getoond:

4.3 Retourneer celcommentaar van VERT.ZOEKEN

Heeft u ooit zowel de overeenkomende celgegevens als de bijbehorende opmerking moeten ophalen met VLOOKUP in Excel, zoals weergegeven in de volgende schermafbeelding? Als dat het geval is, kan de onderstaande door de gebruiker gedefinieerde functie u helpen deze taak te volbrengen.

Stap 1: Kopieer de code naar een module

1. Houd de toetsen "ALT" + "F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.
2. Klik op "Invoegen" > "Module", kopieer en plak de volgende code in het venster "Module".
   VBA-code: Vlookup en retourneer overeenkomende waarde met celcommentaar:

   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 op en sluit het codevenster.

Stap 2: Typ de formule om het resultaat te krijgen

1. Voer nu de volgende formule in en sleep de vulgreep om deze formule naar andere cellen te kopiëren. Het zal zowel de overeenkomende waarden als de opmerkingen tegelijkertijd retourneren, zie screenshot:
   

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

   
   

4.4 VERT.ZOEKEN nummers opgeslagen als tekst

Ik heb bijvoorbeeld een reeks gegevens waarbij het ID-nummer in de originele tabel in getalnotatie is en het ID-nummer in de opzoekcellen wordt opgeslagen als tekst, u kunt een #N/A-fout tegenkomen bij het gebruik van de normale VERT.ZOEKEN-functie. In dit geval kunt u, om de juiste informatie op te halen, de functies TEKST en WAARDE binnen de functie VERT.ZOEKEN plaatsen. Hieronder vindt u de formule om dit te bereiken:

Stap 1: Pas de volgende formule toe en vul deze in

Pas de volgende formule toe in een lege cel en sleep de vulgreep naar beneden 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 krijgt u de juiste resultaten zoals hieronder afgebeeld: