Verticaal zoeken versnellen met Index en vergelijken

Vert.zoeken (VLOOKUP) is een veel gebruikte functie in Excel. Zeker bij controllers die met veel data te maken hebben en veel controles en rapporten moeten verzorgen.

Een groot nadeel van verticaal zoeken is dat deze methode niet erg snel is. Dus met veel data kan het even duren voordat het resultaat er is.
Dit is met de methode vert.zoeken zelf niet op te lossen, maar wel door een alternatief te gebruiken. Die lijkt in eerste instantie iets moeilijker, maar ik denk dat hij prima te begrijpen is. Even wennen, maar veel voordelen! UPDATE: Microsoft heeft XLOOKUP toegevoegd. De moeite waard. En maakt zoeken makkelijker dan ooit.

Even een korte uitleg van verticaal zoeken. Zodat we het in ieder geval over hetzelfde hebben.

Met verticaal zoeken kan er gezocht worden naar een waarde in bereik. Vervolgens kan van het bereik een bepaalde kolom teruggegeven worden. Een voorbeeld van deze formule:

=VERT.ZOEKEN(“Bel”;Voorbeeld!B2:C11;2;ONWAAR)

Indien de waarde Bel voorkomt in de eerste kolom van de matrix, de waarde uit de tweede kolom van de matrix terug.

In dit geval is de retourwaarde = Voorraad

Uitleg INDEX en Vergelijken om verticaal te zoeken

Waar verticaal zoeken één functie betreft, is het alternatief gebaseerd op 2 functies.

=INDEX([Bereik voor het resultaat];
VERGELIJKEN([Zoekwaarde];[Bereik om in te zoeken];0)
;[Resultaat kolom])

We lezen van binnen naar buiten en beginnen dus met de functie VERGELIJKEN (MATCH)

Met vergelijken krijg je het rijnummer retour die overeenkomt met de selectie.
In het voorbeeld willen we weten in welke rij bel voorkomt.
Vergelijken(“Bel”;Gegevens uit Voorbeeld;0 => hiermee geef je aan dat het exact moet zijn)
Dit resultaat in een rijnummer, namelijk rij 6. Het betreft hier de rijnummers van Excel zelf, niet wat er in de kolom eventueel staat! U geeft dus ook nog niet op welke waarde u wilt zien.

We weten nu dat Bel gevonden kan worden in Rij 6.
Met deze informatie kunnen we iedere kolom opvragen die we willen in rij 6 van werkblad Voorbeeld.

Dat doen we met de functie INDEX
Geef bij Index opnieuw een matrix op uit het werkblad Voorbeeld. Dit mag een andere matrix zijn dan eerder opgegeven. In het voorbeeld geef ik A2:C11 op, maar mag dus ook alleen C2:C11 zijn.
Uit A2:C11 vraag ik alleen de rij op die de functie Vergelijken heeft berekent. Namelijk rij 6.

Als laatste geef ik de kolom op, dat is in dit geval de 3de kolom. Voorbeeld in I4
De 3de kolom uit rij 6 heeft als waarde: “Voorraad”
Het getal 3 is in deze functie relatief aan de matrix die is opgegeven.  Zie ook voorbeeld in K4
Stel ik geef als matrix C2:C11 op, dan dient als kolom 1 te worden opgegeven!

Redenen voor dit alternatief

Als eerste is deze methode veel sneller om te verwerken. Dus heeft u een Excel sheet die er lang over doet om te openen of loopt deze vast bij het verticaal zoeken, dan is er een grote kans dat u dit oplost door het alternatief te gebruiken.

U heeft met deze functie meer bewegingsvrijheid. Een nadeel van verticaal zoeken is dat de zoekwaarde in de eerste kolom van het bereik moet staan, anders is er geen match mogelijk. De waarde die u vervolgens wilt tonen moet dus dezelfde waarde of een waarde rechts van de zoekwaarde zijn.
Dat hoeft met het alternatief niet, want daar is de zoeklogica gescheiden van het resultaat. Zoeken gebeurt met vergelijken en door middel van de functie index wordt de gewenste waarde bepaald!

Aangezien de functie vergelijken een rijnummer teruggeeft, bent u dus niet gebonden aan het werkblad waar de zoekfunctie is toegepast, u kunt nu in elk werkblad deze rij opvragen. Dat het kan, betekent nog niet dat het moet overigens. Maar om u even buiten u comfort zone te laten denken. Hiermee kunt u dus verticaal zoeken in verschillende werkbladen (tabbladen).