Toepassing en definitie van deze Excel-functie
De VERT.ZOEKEN is een Excel-functie waarmee de gebruiker de inhoud van tabellen kan zoeken en evalueren. Deze functie is beschikbaar in versies van Excel 2007 voor Windows en Mac.
Wat is de VERT.ZOEKEN?
De mogelijke toepassingen van VERT.ZOEKEN worden hier aan de hand van een voorbeeld uitgelegd: In deze ben je een groot fan van literatuur en heb je daarom je eigen Excel-spreadsheet gemaakt waarin je de boeken die je hebt verzameld zorgvuldig kunt sorteren. Elk werk wordt ingevoerd met informatie over de volgende categorieën:
-
auteur
-
titel
-
Paginanummer
-
Jaar van uitgave
Nu wil je een vriend(in) een boekentip geven om mee te nemen naar je volgende ontmoeting. Helaas kun je alleen aan de auteur denken, niet aan de titel van het boek. Dit is waar VERT.ZOEKEN in het spel komt, omdat het deze invoerwaarde kan gebruiken om de informatie die u zoekt in één klap weg te gooien.
Hoe wordt VERT.ZOEKEN gebruikt?
Alvorens zelfs maar na te denken over het formuleren van formules, moet worden bepaald waar het invoerveld en de verschillende uitvoervelden later zullen worden geplaatst. Om dit te doen, is het zinvol om een aparte tabel te maken die in eerste instantie leeg is en dus ruimte biedt voor de genoemde informatie. Als u deze nieuwe tafel ontwerpt op basis van het voorbeeld van de bestaande tafel, heeft u later een tijdbesparend voordeel.
Op basis hiervan kan de VERT.ZOEKEN-formule handmatig worden gemaakt of automatisch worden gegenereerd door Excel. Voor beginners is het de moeite waard om de laatste benadering te gebruiken om geleidelijk de structuur en het effect van de formule te leren kennen. Hiervoor is op het tabblad "Formules" de knop "Functie invoegen" geselecteerd. De VERT.ZOEKEN is verborgen in het venster dat wordt geopend. Na bevestiging opent er weer een venster waarin de vier parameters van de formule kunnen worden ingevuld. Dit zijn:
-
Zoekcriterium
-
Matrix
-
Kolomindex
-
Gebied_referentie
Het ruwe ontwerp van de formule ziet er daarom als volgt uit:
= VERT.ZOEKEN (zoekcriterium, matrix, kolomindex, bereik_link)
en in een mogelijke toepassing zoals deze:
= VERT.ZOEKEN (H3; A3: E40; 5)
Zoekcriterium
Om ervoor te zorgen dat de functie weet welke waarde als uitgangspunt moet worden gebruikt, wordt in het veld "Zoekcriterium" de regel genoteerd die twee stappen eerder als invoerveld is geselecteerd. In ons voorbeeld wordt daar de naam van de boekauteur “Phillip Pulmann” ingevoerd. Dit maakt de formule flexibel en hoeft niet opnieuw te worden aangepast zodra de ingevoerde waarde verandert.
Matrix
Het invoerveld "Matrix" beschrijft de tabel waarin de uit te voeren informatie te vinden is. Deze speciale matrix bevat dus ook de kolommen voor de boektitel, het paginanummer en het jaar van uitgave.
De matrix wordt eenmalig volledig geselecteerd zonder de kopjes van de marge linksboven naar de marge rechtsonder. Zo weet Excel met welke inhoud rekening moet worden gehouden bij de evaluatie.
Kolomindex
Het invoerveld voor de "kolomindex" vraagt de gebruiker om de kolom van de matrix te definiëren waarin alleen de gezochte waarde wordt vermeld. De toewijzing van de kolommen is chronologisch genummerd. Dit betekent dat de eerste kolom van de tabel de waarde 1 krijgt, de tweede de waarde 2, etc. In ons voorbeeld komt dit overeen met kolomindex 1 voor de auteur, kolomindex 2 voor de titel, kolomindex 3 voor het paginanummer en kolomindex 4 voor het jaar van uitgave.
Om de tabel zo flexibel mogelijk te houden, kan in plaats van het nummer de kolomkop worden gekoppeld. Dit heeft als voordeel dat de formule ook probleemloos naar andere rijen kan worden overgezet, omdat de kolomkop telkens flexibel kan worden aangepast.
Aandacht: De VERT.ZOEKEN leest de matrix van links naar rechts, daarom moet de kolomindex rechts van de kolom voor het zoekcriterium worden geplaatst om door de functie in aanmerking te worden genomen!
Gebied_referentie
De parameter "Range_Lookup" voltooit de VLOOKUP-formule door de nauwkeurigheid op te geven waarmee de tabel wordt geëvalueerd. Het verschilt echter van de eerder genoemde componenten van de formule omdat het optioneel is. Als de waarde 0 wordt ingevuld voor "onjuist", zoekt Excel alleen naar de waarde die als zoekcriterium is opgegeven. Met de waarde 1 voor "true" gaat de zoektocht echter verder naar voor de hand liggende waarden als de exacte waarde niet kon worden gevonden.
Het opgeven van deze parameter is optioneel omdat de waarde 1 standaard is ingesteld. Deze instelling zal later nuttig zijn in de geavanceerde VERT.ZOEKEN met meerdere zoekcriteria.
de samenvoeging
Zodra alle benodigde parameters zijn ingesteld, kan VERT.ZOEKEN worden gebruikt. Na het invoeren van het zoekcriterium en het bevestigen van de functie, verschijnt de waarde die u zoekt in de regel die als uitvoerveld is gedefinieerd.
In ons voorbeeld wordt nu de boektitel "The Golden Compass" weergegeven, die overeenkomt met de ingevoerde auteur. Om snel het paginanummer en het jaar van publicatie te achterhalen, hoeft u niets meer te doen dan de bestaande VERT.ZOEKEN-formule naar de volgende cellen te slepen. Dit is zo eenvoudig omdat de kolomindex van de VERT.ZOEKEN is gekoppeld aan de kolomkop van de eerste tabel en de tweede tabel ook in dezelfde volgorde is gestructureerd.
In het geval dat de tabellen van elkaar zouden verschillen of dat er ondanks alles een fout optreedt, kan de VERT.ZOEKEN-formule ook handmatig worden gewijzigd. Om dit te doen, moet het voorlaatste cijfer voor de kolomindex overeenkomen met de kolom van de nieuwe waarde die moet worden uitgevoerd.
VERT.ZOEKEN met meerdere zoekcriteria
Vaak genoeg komt het voor dat een enkel zoekcriterium niet voldoende is om een grote Excel-tabel nauwkeurig te evalueren. Dan is het logisch om de VERT.ZOEKEN uit te voeren met verschillende zoekcriteria. Hiervoor moet de bestaande formule worden aangevuld met een extra ALS-functie. Op deze manier kan er tijdens de aanvraag rekening worden gehouden met maximaal acht verschillende zoekcriteria.
VERT.ZOEKEN in meerdere Excel-spreadsheets
Als het zoekcriterium niet alleen in de ene tabel kan worden gevonden, maar mogelijk ook in een andere, kan de formule VERT.ZOEKEN dienovereenkomstig worden aangepast. Hiervoor moet zowel een if-functie als een ISERROR-functie voor de bestaande formule worden geplaatst. Hiervoor zijn vijf parameters nodig:
-
Zoekcriterium
-
Matrix1 en Matrix 2
-
Kolomindex1 en kolomindex2
Het resultaat ziet er als volgt uit:
= IF (ISERROR (VERT.ZOEKEN (zoekcriterium, matrix1, kolomindex1, 0));
VERT.ZOEKEN (zoekcriterium; matrix2; kolomindex2,0); VERT.ZOEKEN (zoekcriterium; matrix1; kolomindex1;))
en in een mogelijke toepassing zoals deze:
= IF (ISERROR (VERT.ZOEKEN (E5, A5: B9,2, 0)), VERT.ZOEKEN (E5, A13: B17,2, 0), VERT.ZOEKEN (E5, A5: B9,2, 0))
Het zoekcriterium wordt gebruikt om de waarde in te voegen waarnaar moet worden gezocht in de twee tabellen. Matrix1 en Matrix2 definiëren de respectieve celgebieden van de twee tabellen. Kolomindex1 en kolomindex2 worden gebruikt om in meer detail te definiëren in welke kolommen van de respectievelijke tabellen moet worden gezocht.
Als de waarde die u zoekt in beide tabellen voorkomt, geeft Excel het resultaat uit de eerste tabel weer. Als de waarde echter niet in een van de twee tabellen wordt gevonden, verschijnt er een foutmelding. Het voordeel van de formule is dat de twee lijsten niet dezelfde structuur of grootte hoeven te hebben.
Wijs waarden toe aan categorieën met VERT.ZOEKEN
Met een extra functie van VERT.ZOEKEN kunnen vermelde waarden automatisch worden onderverdeeld in letters en predikaten naar keuze. In ons vorige voorbeeld moet een extra tabelkolom worden ingevoegd voor het boektype. De boeken met een lengte tot 50 pagina's moeten in het genre van het korte verhaal vallen, terwijl boeken van 51 tot 150 pagina's worden toegewezen aan de novelle en van 151 pagina's aan de roman. Om dit mogelijk te maken, is er geen extra formule nodig in VERT.ZOEKEN, alleen het gebruik van accolades "{}". De voltooide formule ziet er als volgt uit:
= VERT.ZOEKEN (B1; {1. "Kort verhaal"; 51. "Novella"; 151. "Roman"}; 2)
De inhoud van de accolades geeft een matrix aan die het gebied van een respectievelijk boektype definieert. De toewijzing van de zijlengte aan het juiste geslacht is daarom ondergebracht in de accolades. De formule gebruikt waardeparen, elk gescheiden door een punt. De matrix {1. "Kort verhaal"; 51. "Novella"; 151. "Roman"} wordt als volgt gelezen:
"Van 1 toon een kort verhaal, van 51 tonen een novelle, van 151 tonen een roman."
Deze matrix kan eenvoudig worden aangepast aan verschillende taken. Dit betreft enerzijds de grootte en het aantal van de matrices alsook hun aanduiding. Het is dus mogelijk om strings of cijfers als resultaat uit te voeren in plaats van individuele letters. Het enige wat je hoeft te doen is de letters in de formule aan te passen.
VERT.ZOEKEN over meerdere werkbladen
Met een andere functie van VERT.ZOEKEN kunnen gebruikers inhoud koppelen die zich op verschillende spreadsheets bevindt. Voor ons voorbeeld kan deze optie handig zijn wanneer informatie eerst in verschillende werkbladen wordt gesorteerd en vervolgens wordt bijgewerkt in een overzichtstabel.
Stel je voor dat je naast je boeken ook je verzamelde films in een Excel-spreadsheet opslaat. Je combineert dan beide collecties in één grote tafel.
Het voordeel van deze procedure ligt niet alleen in de verhoogde volgorde, maar ook in het vermijden van mogelijke fouten. Als u een nieuw item wilt maken of een bestaand item wilt bijwerken, hoeft u niet in de grote tabel te zoeken, maar kunt u in plaats daarvan toegang krijgen tot de kleinere. De waarden worden dan automatisch overgebracht naar de samenvattende Excel-tabel. Dit maakt herschrijven in de grote tabel overbodig, wat in het beste geval een ongelukkige zet en een daaropvolgende aaneenschakeling van foutmeldingen voorkomt.
Hoe ziet de formule eruit?
Deze functie wordt weer mogelijk gemaakt door een andere formule in te voegen. Terwijl zoeken met meerdere criteria een extra IF-formule vereiste, vereist het werken met meerdere werkbladen een INDIRECTE formule. Hierdoor kan een bereik van een ander werkblad worden opgegeven voor de VERT.ZOEKEN-matrix.
= VERT.ZOEKEN (zoekcriterium; INDIRECT (matrix); kolomindex; bereik_link)
Aandacht: Deze formule werkt alleen als de afzonderlijke tabellen in de verschillende bladen dezelfde namen hebben als de kolomkoppen van de algemene tabel. Hele tabellen kunnen een naam krijgen in het "Naamveld" links bovenaan het cellenraster. Tabellen die al een naam hebben gekregen, kunnen worden bekeken met de toetsencombinatie Ctrl + F3.
Omgaan met opkomende foutmeldingen
Werken met gekoppelde Excel-tabellen kan tot ongewenste problemen leiden. Dit omvat met name de uitvoer van verkeerde waarden. In het geval dat de verkeerde waarde 0 wordt uitgevoerd, is er een klein probleem in de instellingen van Excel, dat snel kan worden opgelost.
De veelvoorkomende foutmelding #NV daarentegen is een opzettelijke functie van VERT.ZOEKEN, die de gebruiker aangeeft dat de vereiste waarde niet beschikbaar is. Deze notitie kan met behulp van een formule anders worden vormgegeven.
VERT.ZOEKEN - een overzicht
VERT.ZOEKEN is een handige Excel-functie die kan worden gebruikt om tabellen te zoeken en te evalueren. De voordelen zijn duidelijk in de gebruiksvriendelijke en flexibele toepassing. Op deze manier kan iedereen die regelmatig met Excel-tabellen werkt, profiteren van de functie. Of het nu de particuliere verzamelaar is die zijn eigen kleine tabellen maakt, of het grote bedrijf dat aanzienlijk grotere datasets verwerkt.
Als je daarentegen nog onbeantwoorde verzoeken hebt waar VLOOKUP niet aan kon voldoen, kun je uitkijken naar een extra Excel-optie: Microsoft biedt Excel 365-gebruikers sinds begin 2022-2023 de nieuwe XLOOKUP aan. Dit bouwt voort op de competenties van VERT.ZOEKEN en vult deze aan met extra, soms zelfs eenvoudigere, functies. Daarom opent zich op dit punt ook een nieuwe routine in gegevensevaluatie.
