Excel tip: verticaal zoeken

Ik ken heel veel projectmanagers die uren kunnen spenderen aan het ‘Excellen’. Er moeten immers geregeld rapportages worden gemaakt, en het zou te makkelijk zijn als alles rechtstreeks bruikbaar uit systemen zou komen. Er zijn een paar functies die ik zeer vaak gebruik, maar die bij veel collega projectmanagers toch uitdagingen op blijven leveren. Bij dezen dus mijn eerste Excel tip: verticaal zoeken.

Verticaal zoeken kan je enorm veel werk besparen. Stel: ik heb uit een urensysteem een lijst gekregen van resources die op bepaalde data uren hebben geschreven. Maar deze uren moeten worden vergoed aan de afdelingen, en niet aan de mensen. Hoe kom je er dan handig achter welke resource ook alweer bij welke afdeling hoorde?

1. De bron

Excel_tip_101

Dit is de bron: wat we gaan vullen, is kolom D: de afdeling. Om dat te doen – zonder handwerk – moeten we eerst een tabelletje maken waarin de resources en de bijbehorende afdelingen staan.

2. De zoek matrix

Excel_tip_102

In kolom G en H heb ik de gegevens geplaatst waarin we Excel gaan laten zoeken. Dit kan uiteraard ook een zoekbron op een ander blad zijn. We gaan nu Excel in kolom D de waarden laten zoeken uit deze matrix G2:H6.

3. De zoekformule

Verticaal zoeken doen we met de formule VERT.ZOEKEN (VLOOKUP in het Engels). Dit ziet er zo uit:

=VERT.ZOEKEN(Zoekwaarde;ZoekMatrix;KolomNummer;Benaderen?)

Zoekwaarde De waarde waarop je in de zoekmatrix wilt zoeken – hier kolom B
ZoekMatrix De tabel waaruit je de gewenste waarde wilt ophalen – hier G2:H6 (zonder kolomhoofden dus – die zijn vooral voor jezelf)
KolomNummer Het kolomnummer waar je gegevens vandaan moeten komen – hier de 2e kolom (want we willen de Afdeling hebben)
Benaderen Als de waarde niet bestaat, pakt Excel de dichtstbijzijnde waarde als je deze WAAR (TRUE) maakt. Bij mij bijna altijd ONWAAR (FALSE).

Dus: als we in cel D2 de afdeling van Erik willen opzoeken, doen we dit als volgt:

Excel_tip_103

Je ziet dat de formule gevuld is met: de zoekwaarde (B2), er wordt gezocht in de tabel G2:H6, ik wil kolom 2 hebben, en niet benaderen.

Waarom die $ tekens in de formule bij de zoekmatrix? Dat doe ik om ervoor te zorgen dat als de formule wordt gekopieerd en geplakt in andere cellen, deze waarden hetzelfde blijven: de zoekmatrix zal immers nooit verplaatsen!

4. Het resultaat

Druk je op Enter, dan zal je zien dat D2 ineens is gevuld met “Communicatie”. Kopieer je de formule van D2 naar de cellen D3 tot en met D22, dan zie je dit:

Excel_tip_104

Dit lijkt nu veel werk, maar als je dit moet toepassen op lijsten van duizenden rijen, dan scheelt het ineens fors! Zeker als je je brongegevens gaat verrijken met meer bijbehorende gegevens.

Met deze gegevens kun je vervolgens aan de slag om bijvoorbeeld draaitabellen te maken – dat behandel ik de volgende keer.

Meer tips?

Als je nu al vragen hebt over bepaalde Excel handigheidjes die je als projectmanager kunt gebruiken, laat dan een reactie achter!

One Reply to “Excel tip: verticaal zoeken”

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Deze website gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.