Met de functie verticaal zoeken (VERT.ZOEKEN, Engels: VLOOKUP) kun je met behulp van één zoeksleutel een waarde opzoeken in een tabel. Wil je meer over deze functie weten, bekijk dan eens dit artikel. Echter is het ook mogelijk om een waarde in een tabel op te zoeken met twee zoeksleutels? Gelukkig kan dit ook. Twee mogelijke oplossingen hiervoor kun je vinden in de artikelen Zoeken in Excel met twee sleutels en verticaal zoeken met 2 zoeksleutels. Er is echter nog een derde mogelijkheid die in sommige gevallen makkelijker is. Hiervoor moet je wel een matrixformule gebruiken. Een korte inleiding hierop vind je hier. Een voorbeeld van deze derde mogelijkheid zal ik in de rest van dit artikel laten zien.
Op basis van de bovenstaande tabel willen we met een formule de juiste cursus bij de juiste persoon zoeken. Met VERT.ZOEKEN lopen we hiermee vast omdat “Vries, de” twee keer voorkomt. We zullen zowel op naam als op voorletter moeten zoeken. Dit is mogelijk door een matrixformule te maken met de functies INDEX, VERGELIJKEN (Engels: MATCH) en ALS (Engels: IF).
In het bovenstaande voorbeeld hebben we deze formule gebruikt in de cellen D12 en D13. De formule (voor D12) luidt:
{=INDEX($D$4:$D$7;VERGELIJKEN(B12;ALS($C$4:$C$7=C12;$B$4:$B$7);0))}
Aan de buitenste accolades kun je zien dat het hier om een matrixformule gaat. Deze accolades moet je niet intypen. Ze verschijnen vanzelf als je na het intypen van de formule deze afsluit met de toetsencombinatie CTRL+SHIFT+ENTER
De structuur van de functie INDEX is INDEX(matrix;rijgetal;[kolomgetal]), waarbij het kolomgetal optioneel is. Voor dit voorbeeld hebben we het kolomgetal weggelaten. In deze vorm geeft de functie een waarde terug uit de gespecificeerde rij van de matrix. De matrix is in dit geval het bereik D4:D7. Het rijgetal bepalen we met behulp van de functie VERGELIJKEN. Deze heeft de structuur VERGELIJKEN(zoekwaarde;zoeken-matrix;[criteriumtype_getal]). De zoekwaarde is de naam (cel B12). Voor het definiëren van de zoeken-matrix maken we gebruik van een matrixformule. Hierbij gebruiken we in dit geval de functie ALS. De precieze werking hiervan zullen we zo bekijken. De VERGELIJKEN functie sluiten we af met het criteriumtype_getal 0, wat betekent dat we zoeken naar een exacte overeenkomst.
De crux van dit voorbeeld zit in het gebruik van de functie ALS in combinatie met een matrixformule. Hoe dit precies werkt kan ik het beste laten zien door gebruik te maken van de functie “formule evalueren” uit Excel zelf. In Excel 2007 start je deze op via het tabblad Formules:
Deze functie start met het volgende scherm:
B12 is hier onderstreept en Excel evalueert dit als eerste met het volgende resultaat:
Voor B12 is nu “Vries, de” ingevuld. Volgende stap is het invullen van cel C12.
Voor C12 is “C” ingevuld. Het bereik C4:C7 (de voorletters) is vertaald in de matrix {A\B\C\D\}.
De logische test van de ALS functie is nu uitgevoerd. Het resultaat is de matrix {ONWAAR|ONWAAR|WAAR|ONWAAR}. De derde cel in deze matrix voldeed aan de conditie (namelijk =”C”). Wat de matrixformule dus eigenlijk doet is 4x de functie ALS uitvoeren. Vervolgens zal voor ieder van deze resultaten de “waarde-als-waar” of “waarde-als-onwaar” expressie worden uitgevoerd. De “waarde-als-onwaar” hebben we weggelaten waardoor Excel hier standaard de waarde ONWAAR voor zal teruggeven. Als het resultaat WAAR is moet Excel de overeenkomstige cel in het bereik B4:B7 teruggeven (de naam). Dit zien we in de volgende stap:
Het resultaat van de ALS functie is de matrix {ONWAAR|ONWAAR|”Vries, de”|ONWAAR}. Excel kan nu de VERGELIJKEN functie uitvoeren.
De zoekwaarde “Vries, de” is terug te vinden in de derde cel van de matrix. Het resultaat van de VERGELIJKEN functie is dan ook 3. Nu hoeft Excel alleen de functie INDEX nog maar uit te voeren. Deze moet de derde rij teruggeven van het bereik D4:D7 (de cursussen). Dit is de cursus die hoort bij C. de Vries, namelijk Excel voor controllers.
