VERT.ZOEKEN tussen twee werkmappen: eenvoudige stapsgewijze handleiding

Als u met meerdere werkmappen in Excel werkt, moet u mogelijk de functie VERT.ZOEKEN gebruiken om gegevens van de ene werkmap naar de andere op te halen.

De functie VERT.ZOEKEN zoekt naar een specifieke waarde in een tabel in een afzonderlijke werkmap en retourneert een overeenkomstige waarde uit dezelfde rij. De syntaxis moet een volledig gekwalificeerde verwijzing naar de tweede werkmap en werkbladnaam bevatten.

Het kan een uitdaging zijn om de referenties goed te krijgen wanneer u de functie voor het eerst gebruikt. Dit artikel gebruikt voorbeeldspreadsheets om u precies te laten zien hoe u de formules invoert.

U leert ook enkele verbeteringen aan VERT.ZOEKEN en verschillende alternatieven voor de functie.

Laten we beginnen!

Inhoudsopgave

• Wat is VERT.ZOEKEN?
• Hoe VERT.ZOEKEN tussen twee werkmappen te gebruiken
  • Voorbeeld gegevens
  • Stap 1: houd beide werkmappen open
  • Stap 2: Begin met het invoeren van de VLOOKUP-formule
  • Stap 3: verwijzen naar de tweede werkmap
• 3 Veelvoorkomende VLOOKUP-fouten en hoe u ze kunt oplossen
  • 1. #N/A Fout
  • 2. #Verwijzing! Fout in kolomnummer
  • 3. Fout tussen aanhalingstekens en vierkante haken
• 4 tips voor het beheren van verschillende werkmappen
• Gegevens automatisch bijwerken
• Geavanceerd gebruik van de VERT.ZOEKEN-functie
• 2 belangrijkste alternatieven voor VERT.ZOEKEN
  • 1. INDEXWEDSTRIJD
  • 2. XLZOEKEN
• Hoe VERT.ZOEKEN op meerdere bladen te gebruiken
  • Stap 1: Identificeer de componenten
  • Stap 2: stel uw gegevens in
  • Stap 3: Gebruik de VERT.ZOEKEN-functie
  • (Optioneel) Stap 4: Los #N/A-fouten op
• Laatste gedachten

Wat is VERT.ZOEKEN?

Stel dat je een werkboek hebt met productverkopen en een apart werkboek met de prijzen van elk product. U wilt toegang tot de prijzen uit het tweede werkboek en deze gebruiken in berekeningen in het eerste werkboek.

VERT.ZOEKEN is een oplossing voor deze taak. De term staat voor “Vertical Lookup . “

Het is een ingebouwde functie waarmee u naar een specifieke waarde in een kolom kunt zoeken en een overeenkomstige waarde in een andere kolom kunt retourneren op basis van de locatie.

Het kan worden gebruikt met:

• een enkel rekenblad

• meerdere bladen in dezelfde werkmap

• twee Excel-werkmappen

Als je niet de extra complexiteit van meerdere werkmappen hebt, bekijk dan deze zelfstudie over het vergelijken van twee kolommen in Excel met VLOOKUP .

Hoe VERT.ZOEKEN tussen twee werkmappen te gebruiken

De functie VERT.ZOEKEN is vooral handig wanneer u naar gegevens moet zoeken in meerdere werkbladen of werkmappen.

De drie belangrijkste stappen om dit te doen zijn:

1. Open beide werkmappen.

2. Voer de VLOOKUP-formule in de eerste Excel-werkmap in.

3. Geef een volledig gekwalificeerde verwijzing naar de tweede werkmap.

Voorbeeld gegevens

Deze eenvoudige zelfstudie werkt door voorbeelden op basis van voorbeeldgegevens. Om mee te doen, maakt u een nieuwe werkmap met de naam "Verkoop" en voert u deze gegevens in de kolommen A en B in:

• Appels, 40

• Sinaasappels, 30

• Peren, 70

• Bananen, 20

Voeg een derde kolom toe voor de prijsinformatie:

Maak een tweede Excel-werkmap met de naam "Product" en hernoem Blad1 als "Prijzen". Vul deze gegevens in kolom A en kolom B in:

• Appels, 1,50

• Sinaasappels, 14.30 uur

• Peren, 3,60

• Bananen, 2.10

De gegevens zien er als volgt uit:

Om de omzet voor de verkopen in de eerste spreadsheet te berekenen, moeten we de prijs opzoeken in de tweede spreadsheet. VERT.ZOEKEN schiet te hulp!

Stap 1: houd beide werkmappen open

Beide werkmappen moeten geopend zijn bij gebruik van de VLOOKUP-formule tussen twee werkmappen. Als de tweede werkmap is gesloten, retourneert de functie een fout.

U kunt ze afzonderlijk openen of de opdracht "Openen" in Excel gebruiken om ze allebei tegelijk te openen. Er zijn twee manieren om beide werkmappen te openen:

• Ga naar het tabblad Bestand en selecteer "Openen".

• Gebruik de sneltoets "Ctrl + O" .

Zodra u beide werkmappen hebt geopend, kunt u ertussen schakelen door op het juiste tabblad onder aan het Excel-venster te klikken.

Stap 2: Begin met het invoeren van de VLOOKUP-formule

Hier is de basissyntaxis van de functie VERT.ZOEKEN:

=VERT.ZOEKEN(opzoekwaarde, tabelmatrix, col_index_getal, [bereik_opzoeken])

• lookup_value : de waarde die u wilt opzoeken in de andere werkmap.

• table_array : Het celbereik in de andere werkmap dat de gegevens bevat die u wilt ophalen.

• col_index_num : het kolomnummer in het bereik dat de gegevens bevat die u wilt ophalen.

• range_lookup : Deze laatste parameter is optioneel. Als u het op TRUE instelt of weglaat, zoekt VERT.ZOEKEN naar een overeenkomst bij benadering. Als u deze instelt op FALSE, zoekt VERT.ZOEKEN alleen naar een exacte overeenkomst.

Stel dat u de prijs van appels wilt opzoeken in de tweede werkmap van onze voorbeeldgegevens. Dit is hoe de formule uiteenvalt:

• De lookup_value bevindt zich in cel A2 van de eerste werkmap.

• De tabelmatrix bevindt zich in het celbereik A1:B5 van de tweede werkmap.

• De col_index_num is de tweede kolom die de prijs bevat.

Het meest gecompliceerde deel van de VLOOKUP-formule is het correct verwijzen naar de tweede werkmap.

Stap 3: verwijzen naar de tweede werkmap

Om de VLOOKUP-formule voor twee werkmappen te voltooien, moet u een volledig gekwalificeerde referentie gebruiken die deze details uit de tweede werkmap bevat:

• werkmap naam

• blad naam

• cel bereik

Dit zorgt ervoor dat Excel precies weet waar de gegevens die u zoekt te vinden zijn. De volledig gekwalificeerde referentie wordt gebruikt voor het argument table_array van de functie VERT.ZOEKEN.

Dit zijn de waarden voor de voorbeeldgegevens:

• werkmapnaam: Product.xlsx

• bladnaam: Prijzen

• celbereik: $A1:$B5

Om de prijs voor het eerste item op te zoeken, voert u de volgende formule in cel C2 van de eerste werkmap in:

=VERT.ZOEKEN(A2, [Product.xlsx]Prijzen!$A$1:$B$5, 2, FALSE)

Deze afbeelding toont de functie in actie in de formulebalk voor de eerste cel:

Merk op dat de naam van de werkmap tussen vierkante haken staat en dat de bladnaam wordt gevolgd door een uitroepteken.

Als de Excel-bestandsnaam een ​​spatie of speciale tekens bevat, moet u de hele werkmap en het hele blad tussen enkele aanhalingstekens plaatsen.

De formule gebruikt absolute waarden voor het celbereik (het voorvoegsel $). Hierdoor kunt u de formule naar de rest van de rijen kopiëren door op de vulgreep te klikken.

Dit zijn de VLOOKUP-resultaten:

En dat is het! U weet nu hoe u VERT.ZOEKEN effectief kunt gebruiken. Om u te helpen in het geval u fouten tegenkomt, hebben we in de volgende sectie drie van de meest voorkomende VLOOKUP-fouten verzameld.

3 Veelvoorkomende VLOOKUP-fouten en hoe u ze kunt oplossen

Nu u hebt geleerd hoe u VERT.ZOEKEN kunt gebruiken vanuit een andere werkmap, kunt u enkele veelvoorkomende fouten tegenkomen.

Hier zijn de drie meest voorkomende fouten en hoe u ze kunt oplossen.

1. #N/A Fout

De fout #N/A treedt op wanneer de opzoekwaarde niet wordt gevonden in de tabelmatrix. Om deze fout op te lossen, kunt u het volgende proberen:

• Controleer de spelling van de opzoekwaarde.

• Zorg ervoor dat de opzoekwaarde in de eerste kolom van de tabelmatrix staat.

• Controleer of de opzoekwaarde niet is opgemaakt als tekst in de twee bladen.

• Zorg ervoor dat het argument range_lookup is ingesteld op FALSE om een ​​exacte overeenkomst te vinden.

In het bovenstaande voorbeeld is er geen overeenkomende waarde gevonden voor Kersen in de tweede werkmap.

2. #Verwijzing! Fout in kolomnummer

De #Ref ! Er treedt een fout op wanneer het argument col_index_num groter is dan het aantal kolommen in de tabelarray.

Om deze fout op te lossen, controleert u of het argument col_index_num kleiner is dan of gelijk is aan het aantal kolommen in de brontabel.

In de onderstaande afbeelding is de col_index_num ingesteld op 3, maar er zijn slechts twee kolommen in het referentiebereik.

3. Fout tussen aanhalingstekens en vierkante haken

De fout tussen aanhalingstekens en vierkante haken treedt op wanneer u VERT.ZOEKEN uitvoert met een waarde die aanhalingstekens of vierkante haken bevat.

Om deze fout op te lossen, kunt u het volgende proberen:

• Gebruik dubbele aanhalingstekens rond de opzoekwaarde.

• Gebruik de functie CHAR(34) om dubbele aanhalingstekens in te voegen.

• Gebruik de functie ALS.FOUT om een ​​lege cel te retourneren als de opzoekwaarde aanhalingstekens of vierkante haken bevat.

Als u eenmaal bekend bent met deze fouten, is het oplossen ervan eenvoudig! Om u verder te helpen een VLOOKUP-professional te worden, hebben we in het volgende gedeelte vier tips gegeven voor het beheren van verschillende werkmappen.

4 tips voor het beheren van verschillende werkmappen

Bij het werken met meerdere werkmappen is het belangrijk om bij te houden welke werkmap welke gegevens bevat.

Hier zijn onze vier beste tips om gezond te blijven:

1. Gebruik naamgevingsconventies om u te helpen onthouden welke Excel-spreadsheets welke gegevens bevatten.

2. Gebruik absolute verwijzingen in uw formules om ervoor te zorgen dat het opzoekbereik naar de juiste cellen verwijst, zelfs als u de formule naar een andere locatie of Excel-blad verplaatst of kopieert.

3. Gebruik de functie ALS.FOUT om eventuele #N/A-fouten af ​​te handelen die kunnen optreden als de opzoekwaarden niet in de brongegevens worden gevonden.

4. Overweeg om de cellen in de naslagwerkmap te vergrendelen, zodat ze niet kunnen worden overschreven.

Gegevens automatisch bijwerken

Wanneer u met meerdere Excel-bestanden werkt, kan het tijdrovend zijn om de gegevens handmatig bij te werken .

Als u wilt dat uw VLOOKUP-formule automatisch wordt bijgewerkt wanneer u gegevens toevoegt aan of verwijdert uit de andere werkmappen, kunt u de INDIRECT-functie gebruiken.

Hier is hoe:

• Vervang in de formule VERT.ZOEKEN het argument tabelarray door de functie INDIRECT.

• Gebruik in de functie INDIRECT een verwijzing naar de cel die de tabelarray in de andere werkmap bevat.

Volg deze stappen om dit te bereiken met onze voorbeeldwerkmappen.

1. Gebruik een cel in kolom D om de verwijzing naar het blad "Prijzen" van de werkmap "Product" en het celbereik A1:B5 als volgt in te voeren: [Product.xlsx]Prijzen!A:B

2. Gebruik deze formule in cel C2 van de werkmap "Verkoop": =VERT.ZOEKEN(A2, INDIRECT($D$1), 2, FALSE)

3. Kopieer de formule naar beneden (de absolute verwijzingen zorgen ervoor dat de cel met de spreadsheet behouden blijft).

Deze acties zorgen ervoor dat wijzigingen in het originele blad automatisch worden bijgewerkt in de verbonden cellen.

Deze afbeelding toont de functie INDIRECT in combinatie met VERT.ZOEKEN:

Merk op dat dit voorbeeld het bereik "A:B" gebruikt om naar de twee kolommen te verwijzen. Dit betekent dat wanneer u meer producten en prijzen toevoegt, de formule blijft werken in de huidige werkmap.

Nu je de kracht van VERT.ZOEKEN hebt geproefd, laten we eens kijken naar enkele geavanceerde toepassingen van de functie in de sectie!

Geavanceerd gebruik van de VERT.ZOEKEN-functie

De voorbeelden in dit artikel retourneren één overeenkomst voor elke opzoekwaarde. Als u met meerdere overeenkomsten uit een andere werkmap wilt werken, kunt u in plaats daarvan een matrixformule gebruiken.

U kunt VERT.ZOEKEN ook combineren met andere functies. U kunt het bijvoorbeeld insluiten in een IF-functie voor extra complexe analyse.

Complexe analyse omvat technieken zoals chikwadraattoetsen en T-toetsen met gepaarde steekproeven. Hier is een voorbeeld van het gebruik van de chikwadraattest in Excel:

Gevorderde gebruikers profiteren van VERT.ZOEKEN om datasets te vergelijken en mismatches te identificeren. Andere manieren vindt u in dit artikel over het vinden van verschillen in Excel .

Maar wat als u om wat voor reden dan ook geen gebruik wilt maken van VERT.ZOEKEN? Je hebt geluk! Er zijn enkele alternatieven, die we in de volgende sectie zullen behandelen.

2 belangrijkste alternatieven voor VERT.ZOEKEN

Als het gaat om het opzoeken van waarden in verschillende bladen, is VLOOKUP vaak de go-to-functie. Er zijn echter enkele alternatieven die in bepaalde situaties nuttig kunnen zijn.

De twee meest voorkomende zijn:

• INDEX WEDSTRIJD

• XLZOEKEN

1. INDEXWEDSTRIJD

INDEX en MATCH is een krachtige combinatie van twee functies die kunnen worden gebruikt om waarden in een tabel op te zoeken. In tegenstelling tot VERT.ZOEKEN, waarvoor de opzoekwaarde in de meest linkse kolom van de tabel moet staan, kan INDEX MATCH waarden in elke kolom opzoeken.

Dit is hoe het werkt:

1. De functie INDEX retourneert een waarde uit een opgegeven bereik op basis van een rij- en kolomnummer.

2. De functie VERGELIJKEN retourneert de positie van een opzoekwaarde in een opgegeven bereik.

Om INDEX MATCH te gebruiken, moet u het bereik opgeven waarin u wilt zoeken, de rij waarin u de opzoekwaarde wilt vinden en de kolom waarin u het resultaat wilt retourneren.

Hier is een voorbeeldformule:

=INDEX(Tabelbereik,VERGELIJKEN(opzoekwaarde,opzoekbereik,0),kolomindex)

Om dit te gebruiken met de voorbeeldspreadsheets, is dit de formule:

=INDEX([Product.xlsx]Prijzen!$B$1:$B$5, MATCH(A2, [Product.xlsx]Prijzen!$A$1:$A$5, 0))

Dit valt uiteen als:

• De functie VERGELIJKEN zoekt naar de waarde in cel A2 (productnaam) binnen het bereik A1:A5 in het blad "Prijzen" van de werkmap "Product".

• De functie INDEX retourneert een waarde uit het bereik B1:B5 in het blad "Prijzen" van de werkmap "Product", op basis van het rijnummer dat wordt geleverd door de functie VERGELIJKEN.

Deze afbeelding toont de formule in actie:

2. XLZOEKEN

XLOOKUP is een nieuwere functie die is geïntroduceerd in Excel 365 . Het is vergelijkbaar met VERT.ZOEKEN, maar met enkele extra functies.

Een van de grootste voordelen van XLOOKUP is dat het waarden links van de opzoekkolom kan opzoeken.

Dit is hoe het werkt:

1. De functie XLOOKUP retourneert een waarde uit een opgegeven bereik op basis van een opzoekwaarde.

2. U kunt aangeven of u een exacte overeenkomst of een benaderende overeenkomst wilt.

3. U kunt ook aangeven wat er moet worden geretourneerd als er geen overeenkomst wordt gevonden.

Om XLOOKUP te gebruiken, moet u de opzoekwaarde opgeven, het bereik waarin u wilt zoeken en de kolom waarin u het resultaat wilt retourneren.

Hier is een voorbeeldformule:

=XLZOEKEN(LookupValue,LookupRange,ReturnRange,"Niet gevonden",0)

Om dit te gebruiken met de voorbeeldspreadsheets, is dit de formule:

=XLZOEKEN(A2, [Product.xlsx]Prijzen!$A$1:$A$5, [Product.xlsx]Prijzen!$B$1:$B$5)

Dit valt uiteen als:

• A2: De waarde die u wilt opzoeken (productnaam).

• [Product.xlsx]Prijzen!$A$1:$A$5: Het bereik waarin u wilt zoeken naar de opzoekwaarde (productnamen in het blad "Prijzen" van de werkmap "Product").

• [Product.xlsx]Prijzen!$B$1:$B$5: Het bereik dat de geretourneerde waarden bevat (prijzen op het blad "Prijzen" van de werkmap "Product").

Dit is de formule in actie:

Hoe VERT.ZOEKEN op meerdere bladen te gebruiken

Als u een VERT.ZOEKEN tussen twee werkmappen moet uitvoeren, moet u mogelijk ook VERT.ZOEKEN op meerdere bladen gebruiken.

Gelukkig lijkt het proces om dit te doen erg op het uitvoeren van een VERT.ZOEKEN tussen twee werkmappen.

Er zijn vier stappen.

Stap 1: Identificeer de componenten

In plaats van de tabelarray op te nemen zoals u zou doen voor één blad, moet u ook de bladnaam opnemen.

Als u bijvoorbeeld wilt zoeken naar een waarde in het bereik A2:B6 op het blad Jan in de werkmap Sales_reports.xlsx, ziet uw formule er als volgt uit:

=VERT.ZOEKEN(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2,FALSE)

Stap 2: stel uw gegevens in

Zorg ervoor dat uw gegevens zo zijn geordend dat u er gemakkelijk naar kunt verwijzen op meerdere bladen.

U kunt overwegen om verschillende tabbladen te maken voor specifieke referentiegegevens.

Stap 3: Gebruik de VERT.ZOEKEN-functie

Zodra uw gegevens zijn ingesteld, kunt u de functie VERT.ZOEKEN gebruiken om gegevens van andere bladen te zoeken en op te halen.

Zorg ervoor dat u de bladnaam in uw formule opneemt, zoals hierboven weergegeven.

(Optioneel) Stap 4: Los #N/A-fouten op

Als u #N/A-fouten tegenkomt bij het gebruik van VERT.ZOEKEN op meerdere bladen, controleert u uw formule nogmaals om er zeker van te zijn dat u correct naar alle benodigde bladen en bereiken hebt verwezen.

U kunt ook controleren of u een exacte overeenkomst uitvoert in plaats van een bereik op te zoeken.

Laatste gedachten

U hebt geleerd hoe u VERT.ZOEKEN kunt gebruiken om gegevens in twee spreadsheets te zoeken en op te halen. Deze veelzijdige functie maakt het gemakkelijk om waarden in de ene werkmap op te zoeken op basis van overeenkomstige waarden in een andere.

De kracht van de VERT.ZOEKEN-functie reikt veel verder dan afzonderlijke werkbladen en strekt zich uit over verschillende werkmappen om gegevens te koppelen en te analyseren.

Het beheersen van deze functie is een belangrijke stap in de richting van een goede beheersing van Excel, omdat het nieuwe mogelijkheden opent voor het beheren en interpreteren van uw gegevens.

Door functies zoals INDIRECT, INDEX-MATCH of XLOOKUP op te nemen, kunt u de flexibiliteit en effectiviteit van uw gegevenszoekopdrachten verder verbeteren.

Blijf oefenen en ontdek de enorme mogelijkheden van Excel; dit is slechts het topje van de ijsberg. Veel plezier met het zoeken naar gegevens!