Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

VERT.ZOEKEN-functie met enkele eenvoudige en geavanceerde voorbeelden in Excel

In Excel is de functie VERT.ZOEKEN een krachtige functie voor de meeste Excel-gebruikers, die wordt gebruikt om te zoeken naar een waarde in het meest linkse van het gegevensbereik en een overeenkomende waarde in dezelfde rij te retourneren vanuit een kolom die u hebt opgegeven, zoals hieronder wordt getoond . In deze zelfstudie wordt gesproken over het gebruik van de functie VERT.ZOEKEN met enkele basisvoorbeelden en geavanceerde voorbeelden in Excel.

Inhoudsopgave:

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

2. Basisvoorbeelden van VERT.ZOEKEN

3. Geavanceerde VLOOKUP-voorbeelden

4. VERT.ZOEKEN overeenkomende waarden behouden de celopmaak

5. Download VLOOKUP-voorbeeldbestanden


Introductie van de functie VERT.ZOEKEN - syntaxis en argumenten

De syntaxis van de functie VERT.ZOEKEN:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argumenten:

Opzoekwaarde: De waarde die u wilt zoeken. Het moet in de eerste kolom van het table_array-bereik staan.

Tabel_array: Het gegevensbereik of de tabel waarin de opzoekwaardekolom en de resultaatwaarde-kolom zich bevinden.

Col_index_num: Het aantal kolommen waaruit de overeenkomende waarde wordt geretourneerd. Het begint met 1 vanaf de meest linkse kolom in de tabelmatrix.

Range_lookup: Een logische waarde die bepaalt of deze functie VERT.ZOEKEN een exacte overeenkomst of een geschatte overeenkomst retourneert.

  • Geschatte overeenkomst - 1 / WAAR: Als er geen exacte overeenkomst wordt gevonden, zoekt de formule naar de beste overeenkomst: de grootste waarde die kleiner is dan de opzoekwaarde. In dat geval moet u de opzoekkolom in oplopende volgorde sorteren.
    = VERT.ZOEKEN (zoekwaarde, tabelmatrix, kolomindex, TRUE)
    = VERT.ZOEKEN (lookup_value, table_array, col_index, 1)
  • Exacte overeenkomst - 0 / ONWAAR: Dit wordt gebruikt om te zoeken naar een waarde die exact gelijk is aan de opzoekwaarde. Als er geen exacte match wordt gevonden, wordt de foutwaarde # N / A geretourneerd.
    = VERT.ZOEKEN (lookup_value, table_array, col_index, FALSE)
    = VERT.ZOEKEN (lookup_value, table_array, col_index, 0)

Opmerkingen:

  • 1. De functie Vlookup zoekt alleen naar waarde van links naar rechts.
  • 2. Als er meerdere overeenkomende waarden zijn op basis van de opzoekwaarde, wordt alleen de eerste overeenkomende geretourneerd door de functie Vlookup te gebruiken.
  • 3. Het retourneert de foutwaarde # N / B als de opzoekwaarde niet kan worden gevonden.

Eenvoudige voorbeelden van VERT.ZOEKEN

1. Doe een exacte match Vlookup en geschatte match Vlookup

Doe een exacte match Vlookup in Excel

Normaal gesproken, als u op zoek bent naar een exacte match met de Vlookup-functie, hoeft u alleen FALSE te gebruiken in het laatste argument.

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

1. Pas de onderstaande formule toe in een lege cel waar u het resultaat wilt krijgen:

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

2. En sleep vervolgens de vulgreep naar de cellen waarin u deze formule wilt vullen, en u krijgt de resultaten die u nodig hebt. Zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, F2 is de waarde waarvan u de overeenkomende waarde wilt retourneren, A2: D7 is de tabelmatrix, het getal 3 is het kolomnummer waaruit uw overeenkomende waarde wordt geretourneerd en de Juist verwijst naar de exacte overeenkomst.
  • 2. Als uw criteriumwaarde niet in het gegevensbereik wordt gevonden, wordt een foutwaarde # N / A weergegeven.

Doe een geschatte match Vlookup in Excel

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

Als u bijvoorbeeld de volgende bereikgegevens heeft, staan ​​de opgegeven orders niet in de kolom Orders, hoe krijgt u de dichtstbijzijnde korting in kolom B?

1. Voer de volgende formule in een cel in waar u het resultaat wilt plaatsen:

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

2. Sleep vervolgens de vulgreep naar de cellen om deze formule toe te passen, en u krijgt de geschatte overeenkomsten op basis van de gegeven waarden, zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, D2 is de waarde waarvan u de relatieve informatie wilt retourneren, A2: B9 is het gegevensbereik, het nummer 2 geeft het kolomnummer aan dat uw overeenkomende waarde wordt geretourneerd en de TRUE verwijst naar de geschatte overeenkomst.
  • 2. De geschatte overeenkomst retourneert de grootste waarde die kleiner is dan uw specifieke opzoekwaarde.
  • 3. Om de functie Vlookup te gebruiken om een ​​geschatte overeenkomstwaarde te krijgen, moet u de meest linkse kolom van het gegevensbereik in oplopende volgorde sorteren, anders zal het een verkeerd resultaat opleveren.

2. Voer een hoofdlettergevoelige Vlookup uit in Excel

Standaard voert de functie Vlookup een niet-hoofdlettergevoelige zoekactie uit, wat betekent dat kleine letters en hoofdletters als identiek worden behandeld. Soms moet u hoofdlettergevoelig opzoeken in Excel, de Index-, Match- en Exact-functies of de Lookup- en Exact-functies kunnen u een plezier doen.

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.

Formule 1: EXACTE, INDEX, MATCH-functies gebruiken

1. Typ of kopieer de onderstaande matrixformule in een lege cel waar u het resultaat wilt krijgen:

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

2. Druk vervolgens op Ctrl + Shift + Enter toetsen tegelijkertijd om het eerste resultaat te krijgen, en selecteer vervolgens de formulecel, sleep de vulgreep naar de cellen waarin u deze formule wilt vullen, dan krijgt u de juiste resultaten die u nodig hebt. Zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, A2: A10 is de kolom met de specifieke waarden waarin u wilt opzoeken, F2 is de opzoekwaarde, C2: C10 is de kolom waaruit het resultaat wordt geretourneerd.
  • 2. Als er meerdere overeenkomsten zijn gevonden, retourneert deze formule altijd de eerste overeenkomst.

Formule 2: Lookup- en Exact-functies gebruiken

1. Pas de onderstaande formule toe in een lege cel waar u het resultaat wilt krijgen:

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

2. Sleep vervolgens de vulgreep naar de cellen die u deze formule wilt kopiëren, en u krijgt de overeenkomende waarden met hoofdlettergevoelig zoals onderstaand screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, A2: A10 is de kolom met de specifieke waarden waarin u wilt opzoeken, F2 is de opzoekwaarde, C2: C10 is de kolom waaruit het resultaat wordt geretourneerd.
  • 2. Als er meerdere overeenkomsten zijn gevonden, retourneert deze formule altijd de laatste overeenkomst.

3. Vlookup-waarden van rechts naar links in Excel

De functie Vlookup zoekt altijd een waarde op in de meest linkse kolom van een gegevensbereik en retourneert de corresponderende waarde van een kolom naar rechts. Als u een omgekeerde Vlookup wilt doen, wat betekent dat u een specifieke waarde aan de rechterkant opzoekt en de bijbehorende waarde in de linkerkolom retourneert zoals onderstaand screenshot:

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


4. Bekijk de tweede, n-de of laatste overeenkomende waarde in Excel

Normaal gesproken, als er meerdere overeenkomende waarden worden gevonden bij het gebruik van de Vlookup-functie, wordt alleen het eerste overeenkomende record geretourneerd. In dit gedeelte zal ik het hebben over hoe u de tweede, n-de of laatste overeenkomende waarde kunt krijgen met de Vlookup-functie.

Vlookup en retourneer de tweede of n-de overeenkomende waarde

Stel dat u een lijst met namen heeft in kolom A, de training die ze hebben gekocht in kolom B, en nu zoekt u de 2e of de negende training die door de gegeven klant is gekocht. Zie screenshot:

1. Om de tweede of n-de overeenkomende waarde te krijgen op basis van de gegeven criteria, past u de volgende matrixformule toe in een lege cel:

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

2. Druk vervolgens op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen, en selecteer vervolgens de formulecel, sleep de vulgreep naar de cellen waarin u deze formule wilt vullen en alle tweede overeenkomende waarden op basis van de gegeven namen zijn tegelijk weergegeven, zie screenshot:

Opmerking:

  • In deze formule A2: A14 is het bereik met alle waarden voor opzoeken, B2: B14 is het bereik van de overeenkomende waarden waaruit u wilt terugkeren, E2 is de opzoekwaarde en het laatste nummer 2 geeft de tweede overeenkomende waarde aan die u wilt krijgen, als u de derde overeenkomende waarde wilt retourneren, hoeft u deze alleen naar 3 te wijzigen.

Vlookup en retourneer de laatste overeenkomende waarde

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


5. Vlookup-overeenkomende waarden tussen twee opgegeven waarden of datums

Soms wilt u waarden tussen twee waarden of datums opzoeken en de overeenkomstige resultaten retourneren zoals onderstaand screenshot, in dit geval kunt u de functie ZOEKEN en een gesorteerde tabel gebruiken.

Vlookup-overeenkomende waarden tussen twee opgegeven waarden of datums met formule

1. Ten eerste moet uw oorspronkelijke tabel een gesorteerd gegevensbereik zijn. En kopieer of voer vervolgens de volgende formule in een lege cel in:

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

2. Sleep vervolgens de vulgreep om deze formule naar andere cellen te vullen die u nodig hebt, en nu krijgt u alle overeenkomende records op basis van de gegeven waarde, zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, A2: A6 is het bereik van kleinere waarden en B2: B6 is het bereik van grotere getallen in uw gegevensbereik, de E2 is de gegeven waarde waarvan u de bijbehorende waarde wilt krijgen, C2: C6 zijn de kolomgegevens waaruit u wilt extraheren.
  • 2. Deze formule kan ook worden gebruikt voor het extraheren van overeenkomende waarden tussen twee datums, zoals onderstaand screenshot:

Vlookup-overeenkomende waarden tussen twee opgegeven waarden of datums met een handige functie

Als je pijnlijk bent met de bovenstaande formule, zal ik hier een eenvoudig hulpmiddel introduceren - Kutools for Excel, Met ZOEKEN tussen twee waarden functie, kunt u het overeenkomstige item retourneren op basis van de specifieke waarde of datum tussen twee waarden of datums zonder een formule te onthouden.   Klik om Kutools voor Excel nu te downloaden!


6. Jokertekens gebruiken voor gedeeltelijke overeenkomsten in de Vlookup-functie

In Excel kunnen de jokertekens worden gebruikt binnen de Vlookup-functie, waardoor een gedeeltelijke overeenkomst wordt uitgevoerd op een opzoekwaarde. U kunt bijvoorbeeld Vlookup gebruiken om overeenkomende waarde 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?

1. De normale Vlookup-functie werkt niet correct, u moet de tekst of celverwijzing samenvoegen met een jokerteken, kopieer of voer de volgende formule in een lege cel in:

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

2. Sleep vervolgens de vulgreep om deze formule naar andere cellen te vullen die u nodig hebt, en alle overeenkomende scores zijn geretourneerd zoals onderstaand screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, E2 & "*" is de opzoekwaarde, de waarde in E2 en * jokerteken ("*" geeft een willekeurig teken of een willekeurig teken aan), A2: C11 is het opzoekbereik, het nummer 3 de kolom die de waarde bevat die moet worden geretourneerd.
  • 2. Vlookup wanneer u jokertekens gebruikt, moet u de exacte overeenkomstmodus instellen met FALSE of 0 voor het laatste argument in de Vlookup-functie.

Tips:

1. Zoek en retourneer de overeenkomende waarden die eindigen op een specifieke waarde, pas deze formule toe: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Om de overeenkomende waarde op te zoeken en te retourneren op basis van een deel van de tekstreeks, of de opgegeven tekst nu voor, achter of in het midden van de tekstreeks staat, hoeft u alleen maar twee * tekens rond de celverwijzing of tekst samen te voegen. Doe het met deze formule: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Vlookup-waarden uit een ander werkblad

Meestal moet u mogelijk met meer dan één werkblad werken, de Vlookup-functie kan worden gebruikt om gegevens op te zoeken van een ander blad zoals op een 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:

1. Voer de onderstaande formule in of kopieer deze naar een lege cel waar u de overeenkomende items wilt ophalen:

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

2. Sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen en u krijgt de bijbehorende resultaten zoals u nodig hebt, zie screenshot:

Opmerking: In de bovenstaande formule:

  • A2 vertegenwoordigt de opzoekwaarde;
  • Data sheet is de naam van het werkblad waaruit u gegevens wilt opzoeken, (als de bladnaam spaties of leestekens bevat, moet u enkele aanhalingstekens rond de bladnaam plaatsen, anders kunt u de bladnaam rechtstreeks gebruiken zoals = VERT.ZOEKEN (A2, Datasheet! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 is het gegevensbereik in het gegevensblad waarnaar we gegevens zoeken;
  • het nummer 3 is het kolomnummer dat overeenkomende gegevens bevat waaruit u wilt terugkeren.

8. Vlookup-waarden uit een andere werkmap

In dit gedeelte wordt ingegaan op opzoeken en worden de overeenkomende waarden uit een andere werkmap geretourneerd met behulp van de functie Vlookup.

De eerste werkmap bevat bijvoorbeeld de product- en kostenlijsten, nu wilt u de bijbehorende kosten extraheren in de tweede werkmap op basis van het productitem, zoals onderstaand screenshot.

1. Om de relatieve kosten uit een andere werkmap op te halen, opent u eerst beide werkmappen die u wilt gebruiken en past u vervolgens de volgende formule toe in een cel waarin u het resultaat wilt plaatsen:

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

2. Sleep en kopieer deze formule vervolgens naar andere cellen die u nodig hebt, zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule:
    B2 vertegenwoordigt de opzoekwaarde;
    [Product list.xlsx] Blad1 is de naam van de werkmap en het werkblad waaruit u gegevens wilt opzoeken, (de verwijzing naar de werkmap staat tussen vierkante haken en de hele werkmap + het werkblad staat tussen enkele aanhalingstekens);
    A2: B6 is het gegevensbereik in het werkblad van een andere werkmap waarnaar we gegevens zoeken;
    het nummer 2 is het kolomnummer dat overeenkomende gegevens bevat waaruit u wilt terugkeren.
  • 2. Als de opzoekwerkmap is gesloten, wordt het volledige bestandspad voor de opzoekwerkmap in de formule weergegeven zoals in de volgende schermafbeelding:

9. Vlookup en retourneer lege of specifieke tekst in plaats van de foutwaarde 0 of # N / B

Normaal gesproken, wanneer u de functie vlookup toepast om de overeenkomstige waarde te retourneren, als uw overeenkomende cel leeg is, retourneert deze 0, en als uw overeenkomende waarde niet wordt gevonden, krijgt u een fout # N / A-waarde zoals hieronder afgebeeld. In plaats van de waarde 0 of # N / A weer te geven met een lege cel of een andere waarde die u bevalt, dit Vlookup om een ​​blanco of specifieke waarde te retourneren in plaats van 0 of n.v.t. tutorial kan je stap voor stap een plezier doen.


Geavanceerde VLOOKUP-voorbeelden

1. Tweeweg opzoeken met Vlookup-functie (Vlookup in rij en kolom)

Soms moet u een tweedimensionale zoekactie uitvoeren, wat inhoudt dat u tegelijkertijd in zowel rij als kolom moet Vlookup. Stel, als u het volgende gegevensbereik heeft, en nu, moet u mogelijk de waarde voor een bepaald product in een bepaald kwartaal krijgen. In dit gedeelte wordt een formule geïntroduceerd voor het omgaan met deze taak in Excel.

Formule 1: de functies VERT.ZOEKEN en VERGELIJKEN gebruiken

In Excel kunt u een combinatie van VERT.ZOEKEN en VERGELIJKEN-functies gebruiken om in twee richtingen op te zoeken, pas de volgende formule toe in een lege cel en druk vervolgens op Enter sleutel om het resultaat te krijgen.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Opmerking: In de bovenstaande formule:

  • H1: de opzoekwaarde in de kolom waarop u de bijbehorende waarde wilt krijgen op basis van;
  • A2: E6: het gegevensbereik inclusief rijkoppen;
  • H2: de opzoekwaarde in de rij waarvan u de bijbehorende waarde wilt krijgen op basis van;
  • A1: E1: de cellen van kolomkoppen.

Formule 2: INDEX- en MATCH-functies gebruiken

Hier is een andere formule die u ook kan helpen bij het uitvoeren van een tweedimensionale opzoeking, pas de onderstaande formule toe en druk vervolgens op Enter sleutel om het gewenste resultaat te krijgen.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Opmerking: In de bovenstaande formule:

  • B2: E6: het gegevensbereik waaruit het overeenkomende item moet worden geretourneerd;
  • H1: de opzoekwaarde in de kolom waarop u de bijbehorende waarde wilt krijgen op basis van;
  • A2: A6: de rijkoppen bevatten het product waarnaar u zoekt.
  • H2: de opzoekwaarde in de rij waarvan u de bijbehorende waarde wilt krijgen op basis van;
  • B1: E1: de kolomkoppen bevatten het kwartaal waarnaar u wilt zoeken.

2. Vlookup-overeenkomende waarde op basis van twee of meer criteria

Het is gemakkelijk voor u om de overeenkomende waarde op basis van één criterium op te zoeken, maar wat kunt u doen als u twee of meer criteria heeft? De functies ZOEKEN of VERGELIJKEN en INDEX in Excel kunnen u helpen 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.

Formule 1: de functie ZOEKEN gebruiken

Pas de onderstaande formule toe in een cel waar u het resultaat wilt krijgen en druk vervolgens op invoeren sleutel, zie screenshot:

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

Opmerkingen:

  • 1. In de bovenstaande formule:
    A2: A12 = G1: betekent zoeken naar de criteria van G1 in bereik A2: A12;
    B2: B12 = G2: betekent zoeken naar de criteria van G2 in bereik B2: B12;
    D2: D12: het bereik waarvan u de bijbehorende waarde wilt retourneren.
  • 2. Als u meer dan twee criteria heeft, hoeft u alleen de andere criteria in de formule samen te voegen, zoals: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formule 2: INDEXT- en MATCH-functies gebruiken

De combinatie van Index en Match-functie kan ook worden gebruikt om de overeenkomende waarde te retourneren op basis van meerdere criteria. Kopieer of voer de volgende formule in:

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

Druk vervolgens op Ctrl + Shift + Enter-toetsen samen om de relatieve waarde te krijgen die u nodig hebt. Zie screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule:
    A2: A12 = G1: betekent zoeken naar de criteria van G1 in bereik A2: A12;
    B2: B12 = G2: betekent zoeken naar de criteria van G2 in bereik B2: B12;
    D2: D12: het bereik waarvan u de bijbehorende waarde wilt retourneren.
  • 2. Als u meer dan twee criteria heeft, hoeft u alleen de nieuwe criteria in de formule samen te voegen, zoals: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup om meerdere overeenkomende waarden met een of meer voorwaarden te retourneren

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

Bekijk alle overeenkomende waarden horizontaal op basis van een of meer voorwaarden

Bekijk alle overeenkomende waarden horizontaal op basis van één voorwaarde:

Om alle overeenkomende waarden horizontaal op basis van één specifieke waarde te bekijken en te retourneren, is de generieke formule:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Opmerking:: m is het rijnummer van de eerste cel in het retourbereik min 1.
      n is het kolomnummer van de eerste formulecel min 1.

1. Pas de onderstaande formule toe in een lege cel en druk vervolgens op Ctrl + Shift + Enter sleutels samen om de eerste overeenkomende waarde te krijgen, zie screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. En selecteer vervolgens de eerste formulecel en sleep de vulgreep naar de juiste cellen totdat een lege cel wordt weergegeven en alle bijbehorende items zijn geëxtraheerd, zie screenshot:

Tips:

Als er dubbele overeenkomende waarden in de geretourneerde lijst staan, gebruik dan deze formules om de duplicaten te negeren en druk vervolgens op Enter om het eerste resultaat te krijgen: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Ga door met het invoeren van deze formule: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") in een cel naast het eerste resultaat en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het tweede resultaat te krijgen en sleep deze formule vervolgens naar de juiste cellen om alle andere overeenkomende waarden te krijgen totdat een lege cel wordt weergegeven, zie screenshot:


Bekijk alle overeenkomende waarden horizontaal op basis van twee of meer voorwaarden:

Om alle overeenkomende waarden horizontaal te bekijken en te retourneren op basis van meer specifieke waarden, is de generieke formule:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Opmerking:: m is het rijnummer van de eerste cel in het retourbereik min 1.
      n is het kolomnummer van de eerste formulecel min 1.

1. Pas de volgende formule toe in een lege cel waarin u het resultaat wilt uitvoeren:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Selecteer vervolgens de formulecel en sleep de vulgreep naar de juiste cellen totdat een lege cel wordt weergegeven en alle overeenkomende waarden op basis van de specifieke criteria zijn geretourneerd, zie screenshot:

Opmerking:: Voor meer criteria hoeft u alleen de lookup_value en lookup_range in de formule samen te voegen, zoals: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Bekijk alle overeenkomende waarden op basis van een of meer voorwaarden verticaal

Bekijk alle overeenkomende waarden verticaal op basis van één voorwaarde:

Om alle overeenkomende waarden verticaal op basis van één specifieke waarde te Vlookup en te retourneren, is de generieke formule:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Opmerking:: m is het rijnummer van de eerste cel in het retourbereik min 1.
      n is het rijnummer van de eerste formulecel min 1.

1. Kopieer of typ de volgende formule in een cel waar u het resultaat wilt krijgen, en druk vervolgens op Ctrl + Shift + Enter sleutels samen om de eerste overeenkomende waarde te krijgen, zie screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Selecteer vervolgens de eerste formulecel en sleep de vulgreep naar andere cellen totdat een lege cel wordt weergegeven en alle bijbehorende items in een kolom zijn vermeld, zie screenshot:

Tips:

Gebruik deze formules om de duplicaten in de geretourneerde overeenkomende waarden te negeren: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Druk vervolgens op Ctrl + Shift + Enter toetsen samen om de eerste overeenkomende waarde te krijgen en sleep deze formulecel vervolgens naar andere cellen totdat een lege cel wordt weergegeven en u krijgt het resultaat zoals u nodig hebt:


Bekijk alle overeenkomende waarden op basis van twee of meer voorwaarden verticaal:

Om alle overeenkomende waarden verticaal op basis van meer specifieke waarden te Vlookup en te retourneren, is de generieke formule:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Opmerking:: m is het rijnummer van de eerste cel in het retourbereik min 1.
      n is het rijnummer van de eerste formulecel min 1.

1. Kopieer de onderstaande formule naar een lege cel en druk op Ctrl + Shift + Enter toetsen samen om het eerste overeenkomende item te krijgen.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Sleep vervolgens de formulecel naar andere cellen totdat een lege cel wordt weergegeven, zie screenshot:

Opmerking:: Voor meer criteria hoeft u alleen de lookup_value en lookup_range in de formule samen te voegen, zoals: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup alle overeenkomende waarden op basis van twee of meer voorwaarden in één cel

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

Bekijk alle overeenkomende waarden op basis van één voorwaarde in één cel:

Pas de onderstaande eenvoudige formule toe in een lege cel en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het resultaat te krijgen:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Tips:

Gebruik deze formules om de duplicaten in de geretourneerde overeenkomende waarden te negeren: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Bekijk alle overeenkomende waarden op basis van twee of meer voorwaarden in één cel:

Voor het omgaan met meerdere voorwaarden bij het retourneren van alle overeenkomende waarden in een enkele cel, past u de onderstaande formule toe en drukt u vervolgens op Ctrl + Shift + Enter toetsen samen om het resultaat te krijgen:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Opmerkingen:

1. De TEXTJOIN-functie is alleen beschikbaar in Excel 2019 en Office 365.

2. Als u Excel 2016 en eerdere versies gebruikt, gebruik dan de door de gebruiker gedefinieerde functie van onderstaande artikelen:


4. Vlookup om de hele of hele rij van een overeenkomende cel te retourneren

In dit gedeelte zal ik het hebben over het ophalen van de hele rij met een overeenkomende waarde met behulp van de Vlookup-functie.

1. Kopieer of typ de onderstaande formule in een lege cel waar u het resultaat wilt uitvoeren en druk op Enter sleutel om de eerste waarde te krijgen.

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

2. Sleep vervolgens de formulecel naar de rechterkant totdat de gegevens van de hele rij worden weergegeven, zie screenshot:

Opmerking:: In de bovenstaande formule, F2 is de opzoekwaarde waarvan u de hele rij wilt retourneren op basis van, A1: D12 is het gegevensbereik dat u wilt gebruiken, A1 geeft het eerste kolomnummer binnen uw gegevensbereik aan.

Tips:

Als er meerdere rijen zijn gevonden op basis van de overeenkomende waarde, past u deze formule toe om alle overeenkomende rijen te retourneren: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen, sleep vervolgens de vulgreep naar rechts naar de cellen, zie screenshot:

En sleep vervolgens de vulgreep naar beneden over de cellen om alle overeenkomende rijen te krijgen, zoals hieronder wordt getoond:


5. Voer meerdere Vlookup-functies uit (geneste Vlookup) in Excel

Soms wilt u waarden in meerdere tabellen opzoeken, als een van de tabellen de gegeven opzoekwaarde bevat zoals onderstaand screenshot laat zien, in dit geval kunt u een of meer Vlookup-functies combineren met de IFERROR-functie om meerdere zoekacties uit te voeren.

De generieke formule voor geneste Vlookup-functie is:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Opmerking:

  • opzoekwaarde: de waarde waarnaar u op zoek bent;
  • Table1, Table2, Table3, ...: de tabellen waarin de opzoekwaarde en de retourwaarde bestaan;
  • col: het kolomnummer in de tabel waaruit u de overeenkomende waarde wilt retourneren.
  • 0: Dit wordt gebruikt voor een exacte overeenkomst.

1. Pas de volgende formule toe in een lege cel waar u het resultaat wilt plaatsen:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Sleep vervolgens de vulgreep omlaag naar de cellen waarop u deze formule wilt toepassen en alle overeenkomende waarden zijn geretourneerd zoals onderstaand screenshot:

Opmerkingen:

  • 1. In de bovenstaande formule, J3 is de waarde waarnaar u op zoek bent; A3: B7, D3: E7, G3: H7 zijn de tabelbereiken waarin de opzoekwaarde en retourwaarde bestaan; Het nummer 2 is het kolomnummer in het bereik waarvan de overeenkomende waarde moet worden geretourneerd.
  • 2. Als de opzoekwaarde niet kan worden gevonden, wordt een foutwaarde weergegeven, om de fout te vervangen door een leesbare tekst, gebruikt u deze formule: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup om te controleren of de waarde bestaat op basis van een lijstgegevens in een andere kolom

De Vlookup-functie kan u ook helpen om te controleren of waarden bestaan ​​op basis van een andere lijst, bijvoorbeeld als u de namen in kolom C wilt zoeken en gewoon Ja of Nee wilt retourneren als de naam al dan niet in kolom A wordt gevonden, zoals onder screenshot getoond.

1. Pas de volgende formule toe in een lege cel:

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

2. Sleep vervolgens de vulgreep naar de cellen waarin u deze formule wilt vullen en u krijgt het resultaat zoals u nodig hebt, zie screenshot:

Opmerking:: In de bovenstaande formule, C2 is de opzoekwaarde die u wilt controleren; A2: A10 is de lijst met het bereik van waaruit opzoekwaarden zullen worden gevonden; het nummer 1 is het kolomnummer van waaruit u waarde in uw bereik wilt ophalen.


7. Bekijk en som alle overeenkomende waarden op in rijen of kolommen

Als u met numerieke gegevens werkt, moet u bij het extraheren van de overeenkomende waarden uit de tabel soms ook de getallen in verschillende kolommen of rijen optellen. In dit gedeelte worden enkele formules geïntroduceerd om deze taak in Excel te voltooien.

Bekijk en som alle overeenkomende waarden op in een rij of meerdere rijen

Stel dat u een productlijst heeft met verkopen voor meerdere maanden, zoals onderstaand screenshot laat zien, nu moet u alle bestellingen in alle maanden optellen op basis van de gegeven producten.

Vlookup en tel de eerste overeenkomende waarden op een rij op:

1. Kopieer of typ de volgende formule in een lege cel en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen.

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

2. Sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren die u nodig hebt, en alle waarden in een rij van de eerste overeenkomende waarde zijn bij elkaar opgeteld, zie screenshot:

Opmerking:: In de bovenstaande formule: H2 is de cel met de waarde waarnaar u op zoek bent; A2: F9 is het gegevensbereik (zonder kolomkoppen) dat de opzoekwaarde en de overeenkomende waarden bevat; Het nummer 2,3,4,5,6 {} zijn kolomnummers die worden gebruikt om het totaal van het bereik te berekenen.


Bekijk en som alle overeenkomende waarden op in meerdere rijen:

De bovenstaande formule kan alleen waarden in een rij optellen voor de eerste overeenkomende waarde. Als u alle overeenkomsten in meerdere rijen wilt optellen, gebruik dan de volgende formule en sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen, en u krijgt het gewenste resultaat dat u nodig hebt, zie screenshot:

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

Opmerking:: In de bovenstaande formule: H2 is de opzoekwaarde waarnaar u op zoek bent; A2: A9 zijn de rijkoppen die de opzoekwaarde bevatten; B2: F9 het gegevensbereik van de numerieke waarden die u wilt optellen.


Vlookup en som alle overeenkomende waarden in een kolom of meerdere kolommen op

Vlookup en som de eerste overeenkomende waarden in een kolom op:

Als u de totale waarde voor de specifieke maanden wilt optellen, zoals weergegeven in de onderstaande schermafbeelding.

Pas de onderstaande formule toe in een lege cel en sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren, nu zijn de eerste overeenkomende waarden op basis van de specifieke maand in een kolom bij elkaar opgeteld, zie screenshot:

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

Opmerking:: In de bovenstaande formule: H2 is de opzoekwaarde waarnaar u op zoek bent; B1: F1 zijn de kolomkoppen die de opzoekwaarde bevatten; B2: F9 het gegevensbereik van de numerieke waarden die u wilt optellen.


Vlookup en som alle overeenkomende waarden op in meerdere kolommen:

Om alle overeenkomende waarden in meerdere kolommen op te zoeken en op te tellen, moet u de volgende formule gebruiken:

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

Opmerking:: In de bovenstaande formule: H2 is de opzoekwaarde waarnaar u op zoek bent; B1: F1 zijn de kolomkoppen die de opzoekwaarde bevatten; B2: F9 het gegevensbereik van de numerieke waarden die u wilt optellen.


Bekijk en som de eerste overeenkomende of alle overeenkomende waarden op met een krachtige functie

Misschien zijn de bovenstaande formules moeilijk te onthouden, in dit geval zal ik een handige functie aanbevelen - Opzoeken en optellen of Kutools for Excel, met deze functie kunt u het resultaat zo gemakkelijk mogelijk krijgen.    Klik om Kutools voor Excel nu te downloaden!


Vlookup en som alle overeenkomende waarden op, zowel in rijen als 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.

Pas de volgende formule toe in een cel en druk vervolgens op invoeren sleutel om het resultaat te krijgen, zie screenshot:

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

Opmerking:: In de bovenstaande formule: B2: F9 is het gegevensbereik van de numerieke waarden die u wilt optellen; B1: F1 is de kolomkoppen bevatten de opzoekwaarde waarop u wilt optellen; I2 is de opzoekwaarde binnen de kolomkoppen waarnaar u op zoek bent; A2: A9 zijn de rijkoppen de opzoekwaarde die u wilt optellen op basis van; H2 is de opzoekwaarde binnen de rijkoppen waarnaar u op zoek bent.


8. Vlookup om twee tabellen samen te voegen op basis van een of meer sleutelkolommen

In uw dagelijkse werk moet u bij het analyseren van de gegevens mogelijk alle benodigde informatie verzamelen in één tabel op basis van een of meer sleutelkolommen. Om deze klus op te lossen, kan de Vlookup-functie u ook een plezier doen.

Vlookup om twee tabellen samen te voegen op basis van één sleutelkolom

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

Formule 1: de functie VERT.ZOEKEN gebruiken

Om de twee tabellen samen te voegen tot één op basis van een sleutelkolom, past u de volgende formule toe in een lege cel waar u het resultaat wilt krijgen en sleept u vervolgens de vulgreep omlaag naar de cellen waarop u deze formule wilt toepassen. krijg een samengevoegde tabel waarbij de orderkolom wordt samengevoegd met de eerste tabelgegevens op basis van de sleutelkolomgegevens.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Opmerking:: In de bovenstaande formule, A2 is de waarde waarnaar u op zoek bent, E2: F8 is de tafel om te zoeken, het nummer 2 is het kolomnummer in de tabel waaruit de waarde moet worden opgehaald.

Formule 2: INDEX- en MATCH-functies gebruiken

Als uw gemeenschappelijke gegevens aan de rechterkant en de geretourneerde gegevens in de linkerkolom in de tweede tabel, om de orderkolom samen te voegen, kan de Vlookup-functie het werk niet doen. Om van rechts naar links op te zoeken, kunt u de INDEX- en MATCH-functies gebruiken om de Vlookup-functie te vervangen.

Kopieer of voer de onderstaande formule in een lege cel in, kopieer vervolgens de formule door de kolom en de orderkolom is samengevoegd met de eerste tabel, zie screenshot:

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

Opmerking:: In de bovenstaande formule, A2 is de opzoekwaarde waarnaar u op zoek bent, E2: E8 is een gegevensbereik dat u wilt retourneren, F2: F8 is het opzoekbereik dat de opzoekwaarde bevat.


Vlookup om twee tabellen samen te voegen op basis van meerdere sleutelkolommen

Als de twee tabellen waaraan u wilt deelnemen meerdere sleutelkolommen hebben, kunnen de functies INDEX en MATCH u helpen om de tabellen samen te voegen op basis van deze gemeenschappelijke kolommen.

De algemene formule voor het samenvoegen van twee tabellen op basis van meerdere sleutelkolommen is:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Pas de onderstaande formule toe in een lege cel waar u het resultaat wilt plaatsen en druk vervolgens op Ctrl + Shift + Enter sleutels samen om de eerste overeenkomende waarde te krijgen, zie screenshot:

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

Opmerking:: In de bovenstaande formule, wat de celverwijzingen vertegenwoordigen, zoals onderstaand screenshot:

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

Tips: In Excel 2016 en latere versies kunt u ook de Power Query functie om twee of meer tabellen samen te voegen tot één op basis van sleutelkolommen. Klik om de details stap voor stap te kennen.

9. Vlookup-overeenkomende waarden over meerdere werkbladen

Heb je ooit geprobeerd om waarden op meerdere werkbladen op te zoeken? Stel dat ik de volgende drie werkbladen met gegevensbereik heb, en nu wil ik een deel van de overeenkomstige waarden krijgen op basis van de criteria van deze drie werkbladen om het resultaat te krijgen zoals onderstaand screenshot. In dit geval is het Vlookup-waarden over meerdere werkbladen tutorial kan je stap voor stap een plezier doen.


VERT.ZOEKEN overeenkomende waarden behouden de celopmaak

1. Vlookup om celopmaak (celkleur, lettertypekleur) samen met de opzoekwaarde te krijgen

Zoals we allemaal weten, kan de normale Vlookup-functie ons alleen helpen om de overeenkomende waarde uit een ander gegevensbereik te retourneren, maar soms wilt u misschien de overeenkomstige waarde retourneren samen met de celopmaak, zoals de vulkleur, lettertypekleur, lettertypestijl zoals hieronder getoond screenshot. In dit gedeelte wordt besproken hoe u de celopmaak met de geretourneerde waarde in Excel kunt krijgen.

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

1. In het werkblad staan ​​de gegevens die u wilt Vlookup, klik met de rechtermuisknop op de bladtab en selecteer Bekijk code vanuit het contextmenu. Zie screenshot:

2. In het geopende Microsoft Visual Basic voor toepassingen -venster, kopieer de onderstaande VBA-code naar het codevenster.

VBA-code 1: Vlookup om celopmaak samen met opzoekwaarde te krijgen

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

3. Nog steeds in de Microsoft Visual Basic voor toepassingen venster klikt Invoegen > Moduleen kopieer vervolgens de onderstaande VBA-code 2 naar het modulevenster.

VBA-code 2: Vlookup om celopmaak samen met opzoekwaarde te krijgen

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. Nadat u de bovenstaande codes heeft ingevoerd, klikt u op Toolbox > Referenties functie in het Microsoft Visual Basic voor toepassingen venster. Controleer dan het Microsoft Script-runtime checkbox in de Referenties - VBAProject dialoog venster. Zie screenshots:

5. Dan klikken OK om het dialoogvenster te sluiten en vervolgens het codevenster op te slaan en te sluiten, ga nu terug naar het werkblad en pas deze formule toe: =LookupKeepFormat(E2,$A$1:$C$10,3) in een lege cel waar u het resultaat wilt uitvoeren en druk vervolgens op Enter. Zie screenshot:

Opmerking:: In de bovenstaande formule, E2 is de waarde die je opzoekt, A1: C10 is het tafelbereik en het nummer 3 is het kolomnummer van de tabel waarvan u de overeenkomende waarde wilt retourneren.

6. Selecteer vervolgens de eerste resultaatcel en sleep de vulgreep naar beneden om alle resultaten samen met hun opmaak te zien. Zie screenshot.


2. Bewaar de datumnotatie van een door Vlookup geretourneerde waarde

Normaal gesproken, wanneer u de functie Vloook gebruikt om de overeenkomende datumnotatie op te zoeken en te retourneren, wordt een getalnotatie weergegeven zoals onderstaand screenshot. Om de datumnotatie van een geretourneerd resultaat te behouden, moet u de TEXT-functie toevoegen aan de Vlookup-functie.

Pas de onderstaande formule toe in een lege cel en sleep vervolgens de vulgreep om deze formule naar andere cellen te kopiëren en alle overeenkomende datums zijn geretourneerd zoals onderstaand screenshot:

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

Opmerking:: In de bovenstaande formule, E2 is de look-waarde, A2: C9 is het opzoekbereik, het nummer 3 is het kolomnummer waarvan u de waarde wilt retourneren, mm/dd/yyy is de datumnotatie die u wilt behouden.


3. Vlookup en retourneer overeenkomende waarde met celcommentaar

Heb je ooit geprobeerd om Vlookup te gebruiken om niet alleen de overeenkomende celgegevens te retourneren, maar ook de celcommentaar in Excel zoals in het volgende screenshot wordt getoond? Om deze taak op te lossen, kan de onderstaande door de gebruiker gedefinieerde functie u een plezier doen.

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Module, kopieer en plak vervolgens de volgende code in het modulevenster.

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 het codevenster op en sluit het, voer deze formule in: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) in een lege cel om het resultaat te lokaliseren en sleep vervolgens de vulgreep om deze formule naar andere cellen te kopiëren, nu worden zowel de overeenkomende waarden als de opmerkingen in één keer geretourneerd, zie screenshot:

Opmerking:: In de bovenstaande formule, D2 is de opzoekwaarde die u de bijbehorende waarde wilt retourneren, A2: B9 is de gegevenstabel die u wilt gebruiken, het nummer 2 is het kolomnummer dat de overeenkomende waarde bevat die u wilt retourneren.


4. Behandel de tekst en reële cijfers in Vlookup

Ik heb bijvoorbeeld een reeks gegevens, het ID-nummer in de originele tabel is een getalnotatie, in de opzoekcel die als tekst is opgeslagen, wordt bij het toepassen van de normale Vlookup-functie een # N / A-foutresultaat weergegeven zoals onder screenshot getoond. Hoe zou u in dit geval de juiste informatie kunnen krijgen als het opzoeknummer en het originele nummer in de tabel een ander gegevensformaat hebben?

Voor het omgaan met de tekst en reële getallen in de Vlookup-functie, past u de volgende formule toe in een lege cel en sleept u vervolgens de vulgreep naar beneden om deze formule te kopiëren, en u krijgt de juiste resultaten zoals hieronder wordt getoond:

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

Opmerkingen:

  • 1. In de bovenstaande formule, D2 is de opzoekwaarde die u de bijbehorende waarde wilt retourneren, A2: B8 is de gegevenstabel die u wilt gebruiken, het nummer 2 is het kolomnummer dat de overeenkomende waarde bevat die u wilt retourneren.
  • 2. Deze formule werkt ook goed als u niet zeker weet waar u getallen heeft en waar u tekst heeft.

Download VLOOKUP-voorbeeldbestanden

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
kte tabblad 201905
  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anton · 1 months ago
    Здравствуйте, возможно ли сравнить любое значение из столбцов в таблице. Суть в том, что значения сделаны неправильно и есть слова "CO" "BE" как сделанные на английском так и на русском, чтобы каждый раз не делать замену их неудобно, т.к. файл обновляется постоянно. Необходимо чтобы Впр искал любой вариант из первого и второго столбца. 1 столбец русские символы, 2-ой английские.