Index Vergelijken versus Verticaal Zoeken – Wie wint?

 

Bijna iedere gebruiker van Excel heeft wel is kennis gemaakt met de formule Verticaal Zoeken. Er is een slimmer alternatief voor deze zoekformule en dat is de combinatie van de twee formules Index en Vergelijken. In dit artikel wordt uitgelegd hoe het werkt en waarom dat slimmer is dan Verticaal Zoeken.

 

Het gebruikte Excelmodel bestaat uit drie tabbladen. Het eerste tabblad laat zien hoe de formules werken en het tweede blad bevat een tabel met gegevens uit 2011 van een aantal aan de AEX genoteerde bedrijven. Op het derde tabblad staat een schema hoe de formule Verticaal Zoeken werkt.

Verticaal Zoeken

Het doel van de formule Verticaal Zoeken is om een bepaalde waarde op te halen uit een andere tabel en die te plaatsen in de cel waar de formule is geplaatst. De formule Verticaal Zoeken zoekt aan de hand van een zoekwaarde in een tabel naar een waarde in een andere tabel in een bepaalde kolom.

 

De syntax van de formule Verticaal Zoeken komt neer op: zoekwaarde; matrix; kolomnummer. In Excelterminologie: =VERT.ZOEKEN(zoekwaarde; tabel-matrix; kolomindex_getal; [benadering]).

 

De toevoeging [benadering] geeft je de optie om de best passende waarde te geven, indien de zoekwaarde niet kan worden gevonden. Tenzij je dat nuttig vindt, kun je hier altijd ONWAAR invullen. In het bijgevoegde Excelmodel staat deze formule in rij 5.

 

In het onderstaande schematische voorbeeld zoeken we de EBITDA van Fugro. EBITDA staat voor Earnings Before Interest, Taxes, Depreciation and Amortization. Daarover in een ander artikel meer. Zie ook het bijgevoegde Excelmodel Index Vergelijken

 

De gebruikte formule is: =VERT.ZOEKEN([@[Onderneming (keuzemenu)]];Tabel6;4;ONWAAR). Let op dat er geen celreferenties nodig zijn omdat er gebruik is gemaakt van de tabelfunctie van Excel. Zie ook het volgende artikel De tabel – de multitool van Excel.

 

 

 

 

Index Vergelijken

De formule Verticaal Zoeken kan vervangen worden door de combinatie van twee formules. Namelijk Index en Vergelijken.

  • Met Index haal je een waarde op in een bepaalde tabel op het kruispunt van respectievelijk een rijnummer en een kolomnummer;
  • Met Vergelijken zoek je een rij- of kolomnummer op aan de hand van een zoekwaarde

 

De combinatie ervan ligt dus voor de hand:  gebruik Index om een waarde op te halen aan de hand van een rij- en kolomnummer die met de formule Vergelijken gevonden is. De formule Vergelijken komt dus twee keer voor in de totale zoekformule. De eerste voor de het rijnummer en de tweede voor het kolomnummer.

 

De volledige syntax lijkt dus op: =INDEX(matrix; VERGELIJKEN (ophalen rijnummer); VERGELIJKEN (ophalen kolomnummer)). De factor matrix is de tabel waar de waarde moet worden opgehaald.

Werking Index

De syntax is: =INDEX(matrix; rij_getal; [kolom_getal])

 

De matrix is de tabel waar je de waarde wilt ophalen. De factor rij_getal is het nummer van de rij waar de zoekwaarde zich in bevindt en kolom_getal het nummer van de kolom is. Het kruispunt van beide geeft dus de gewenste zoekwaarde.

Werking Vergelijken

Met Vergelijken zoek je een rij- of kolomnummer op aan de hand van een zoekwaarde.

 

De syntax is: =VERGELIJKEN(zoekwaarde; zoeken-matrix; [criteriumtype_getal])

 

De zoekwaarde is de waarde waarvan je wilt weten in welk rij- of kolomnummer die voorkomt. De zoeken-matrix geeft het bereik aan waar die zoekwaarde zich bevindt. Bij het zoeken van een rijnummer selecteer je als bereik dat gedeelte van de zoekmatrix waar alle mogelijke waarden in voorkomen. Ins ons voorbeeld is dat dus het bereik [A2:A28] op het tabblad [Register]. Voor het kolomnummer geldt hetzelfde principe, maar het bereik bestaat dan uit alle kopteksten van de zoekmatrix op het tabblad [Register]. De laatste factor criteriumtype_getal] geeft eveneens weer aan of je een benadering wilt indien de waarde niet kan worden gevonden. Tenzij je dat nuttig vindt is het advies om deze altijd op ‘0’ te zetten (onwaar).

Werking combinatie Index en Vergelijken

Aan de hand van onderstaande uitsnede zie je het resultaat van het gebruik van Index en Vergelijken (zie ook het bijgeleverde Excelmodel). We gaan weer de EBITDA opzoeken van Fugro. De zoekmatrix staat op het tabblad [Register] en heet [Tabel6].

 

In de eerste rij zie je het resultaat als je gewoon rij- en kolomnummer (resp. 11 en 4) invult in de Index-formule. In de tweede rij zie je het resultaat als je rijnummer vervangt door de formule vergelijken om het rijnummer op te zoeken. Tenslotte zie je in de derde rij het resultaat als je ook het kolomnummer laat zoeken met de formule Vergelijken.

 

 

De syntax van de combinatie Index en Vergelijken is ingewikkelder dan die van Verticaal zoeken. Maar de eerste heeft wel een belangrijk voordeel ten opzichte van de klassieke zoekformule.

 

Wie wint?

Het grote voordeel van de combinatie Index met Vergelijken is dat je ook kunt zoeken in een kolom die aan de linkerzijde van de kolom met de zoekwaarde staat. Gebruikers die bekend zijn met de formule Verticaal Zoeken worden regelmatig geconfronteerd met de tekortkoming van deze formule.

 

De oorzaak daarvan is dat bij de formule Verticaal Zoeken de te vinden waarde altijd in een bepaalde kolom staat, geteld vanaf de kolom met de zoekwaarde. De kolom met de zoekwaarde heeft altijd nummer 1 en de te vinden waarde staat of in dezelfde kolom of in een kolom rechts daarvan. Je kan met Verticaal Zoeken dus nooit een waarde vinden die links van de zoekwaarde staat, want je kunt een kolomnummer (in de huidige versie van Excel) geen negatieve waarde meegeven.

 

Dat probleem heb je met de combinatie Index en Vergelijken niet. De kolom met de te vinden waarde kan links staan van de zoekwaarde, omdat de formule Vergelijken in eerste instantie zoekt op koptekst en niet op kolomnummer.

Excelmodel Index Vergelijken

Voor de voorbeelden in dit artikel is gebruik gemaakt van gegevens uit 2011 over een aantal aan de AEX genoteerde bedrijven. Dit Excelmodel kun je met de volgende link downloaden.

 

Link naar Excelmodel Index Vergelijken

 

Het kan zijn dat het bestand niet wil openen in de browser, kies dan voor het blauwe vakje [Downloaden bestand] bovenaan in de browser.

Boekentip