Excel, VERT.ZOEKEN() performance

Tip & Truc met betrekking tot de performance van VERT.ZOEKEN().


 

Situatie
Een erg krachtige functie, de formule VERT.ZOEKEN(). Maar als het gaat om VEEL zoekacties in een groot aantal kolommen en rijen is het beter een andere aanpak te kiezen. Het snellere alternatief staat hier beschreven.

De functies worden vaak gebruikt, VERT.ZOEKEN() om een waarde uit een tabelmatrix bij een opgegeven celwaarde te vinden en ISNB() om te bepalen of de zoekwaarde ook in de tabel gevonden wordt. Maar hoe groter de tabelmatrix waarin wordt gezocht en hoe meer de zoekfunctie VERT.ZOEKEN() gebruikt wordt hoe langer er op het herberekenen moet worden gewacht.

Oplossing
Als er sprake is van het zoeken in een grote lijst (tabelmatrix) waarbij veel details (kolommen) bij de gevonden regel moeten worden verzameld, kan gekozen worden voor een andere aanpak. In principe hoeft men niet voor iedere kolom opnieuw de regel moet worden bepaald (iets wat met VERT.ZOEKEN() wel gebeurd). Bepaal eenmalig de regel in het zoekbereik waarop de kolomgegevens betrekking hebben en zet deze in een aparte (eventueel verborgen) kolom. Gebruik het gevonden rijnummer om "rechtstreeks" een celwaarde op te halen. Hieronder staat hieronder uitgelegd.

Het ophalen van gegevens uit een zoekbereik op basis van de zoeksleutel gebeurt met de Excelformules VERGELIJKEN() en INDIRECT(). Als "Brongegevens" het werkblad is waarop het zoekbereik is weergegeven en er wordt gezocht in de kolom "A:A", dan ziet de formule er als volgt uit:

=ALS(ISNB(VERGELIJKEN($B4;BronGegevens!A:A;ONWAAR));"";VERGELIJKEN($B4;BronGegevens!A:A;ONWAAR))

Indien de sleutelwaarde niet gevonden wordt de cel gevuld met #ISNB. Om dit te voorkomen wordt eerst gecontroleerd op het bestaan van de sleutelwaarde op werkblad BronGegevens. Zo ja, dan de gevonden waarde tonen, anders de cel leeglaten.

Vervolgens wordt op basis van het gevonden rijnummer rechtstreeks de bijbehorende BronGegevens bepaald. Hiervoor wordt op basis van het gevonden rijnummer en het gewenste kolomnummer het celadres samengesteld van op te halen celwaard. Als het nummer van de gevonden rij in kolom "C" staat en de op te halen waarde in de 2e kolom in het werkblad "Brongegevens", ziet de Excelformule er als volgt uit:

=ALS($C1="";"";INDIRECT("BronGegevens!R"&TEKST($C1;0)&"K2";ONWAAR))

Voor het samenstellen van het celadres kan ook gebruik gemaakt worden van de Excelformule TEKST.SAMENVOEGEN(). Omdat er nu per regel (en niet ook per kolom) maar één zoekactie wordt uitgevoerd, vindt er een aanzienlijke performanceverbetering plaats. Deze performanceverbetering wordt beter merkbaar naarmate de tabelmatrix groter wordt. Het blijkt dat de keuze van het gebruik van Excelformules van belang kan zijn. Er zijn meerdere wegen die naar Rome leiden. De vraag is welke is de meest optimale . . .


Heb je nog vragen, neem dan gerust contact met ons op.