Bijna iedere Excel gebruiker werkt met de functie verticaal zoeken. We zoeken 1 waarde op basis van 1 zoekwaarde. Een omzet op basis van een klantnummer of een saldo op basis van een grootboekrekening. Daar houdt het normaal gesproken op! Willen we de omzet van b.v klant 100 zoeken in 1000 regels met waarbij de er ook een jaartal bij de regel vermeld staat (2015, 2016) dan schiet verticaal zoeken normaal gesproken tekort. Natuurlijk kun je hulp kolommen gebruiken waarbij je in 1 cel  klant 100_2015 / 100_2016 zet en ga je dan ook zoeken op deze combinatie maar dat wordt zo’n ‘geknutsel’. Een andere beperking van verticaal zoeken is ook dat je allen maar naar rechts kan zoeken, je vindt een klant en kan alleen waarden aan de rechterkant van het klantnummer retourneren.. Vergeet verticaal zoeken!

Ik ga jullie een mooier, sneller en krachtiger alternatief tonen!

De functie INDEX() in combinatie met vergelijken(). Lijkt lastig maar als je hem snapt dan valt dat reuze mee en heb je er verschrikkelijk veel plezier van . Ik start met een Excel tabblad STAMDATA waarin ik voor de verschillende DELI bedrijven kan opgeven welke software paketten ze in gebruik hebben, voor welk boekjaar / welke maand en wat voor tarief er aan dat pakket hangt. Het origineel wat ik gemaakt heb ziet er iets anders uit maar ik hou de namen even algemeen (aantallen / tarief is ‘fictief’):

In mijn tabblad INVOER wil ik iedere maand een opgave kunnen doen van het aantal pakketten in gebruik bij de bedrijven waarbij ik wil dat het tarief wat berekend wordt voor het software pakket uit het tabblad STAMDATA wordt opgehaald op basis van bedrijf, boekjaar maand en menuitem_code uit het tabblad STAMDATA. Ga daar maar eens verticaal op zoeken :)

De hele formule om dit voor elkaar te krijgen is:

{ =INDEX(STAMDATA!$A$3:$E$5001;VERGELIJKEN(1; (B2=STAMDATA!$B$3:$B$5001) * (D2=STAMDATA!$A$3:$A$5001) *  (A2=STAMDATA!$D$3:$D$5001) *  (C2=STAMDATA!$C$3:$C$5001);0);5;1)}

In eerste instantie denk je (dacht ik) dan ‘laat maar, dat kwartje blijft bij mij niet hangen, ik ga het niet eens proberen..’
Dit valt mee, eigenlijk kun je ze uit elkaar trekken.

Je hebt een INDEX deel: {=INDEX(RANGE UIT STAMDATA WAAR IK WAT UIT WIL HALEN;  ‘HIER ZOEK IK DE REGEL IN STAMDATA DMV VERGELIJKEN’   ;5 (e kolom uit stamdata);1)}

En een VERGELIJKEN deel: {=VERGELIJKEN(1; (B2=STAMDATA!$B$3:$B$5001) * (D2=STAMDATA!$A$3:$A$5001) *  (A2=STAMDATA!$D$3:$D$5001) *  (C2=STAMDATA!$C$3:$C$5001);0)}

LET OP DE RODE {} om de functies geeft aan dat we te maken hebben met een zgn. MATRIX functie. Je kunt deze haakjes niet intypen maar je moet bij het afsluiten van je functie de toesten CTRL-SHIFT-ENTER gebruiken. De {} worden er dan automatisch omheen gezet.

Zonder {} is je functie nutteloos!

Zet ik alleen de functie vergelijken in een cel:

Dan krijg ik, mits alle zoekwaarden gevonden worden een 1:

Verander ik even cel B2 naar 2020, een boekjaar waarvan ik nog geen tarieven heb opgegeven in het tabblad stamdata dan krijg ik een fout:

Als je verticaal zoeken gebruikt dan begrijp je wel dat je de 5^e kolom opvraagt uit STAMDATA!$A$3:$E$5001.

Hier raken we echter wel de beperking van verticaal zoeken, want waar we bij verticaal zoeken alleen naar rechts kunnen, kunnen we hier ook de 0^e of 1^e kolom terugvragen c.q. naar links ‘zoeken’.

In het voorbeeld hieronder is goed te zien wat de functie vergelijken doet, hij zoekt een ‘match’ tussen de waarde in B2 (2017) met een cel in STAMDATA!B3:B5001 en een match tussen D2 (OFFICE)  met een cel in STAMDATA!A3:A5001,

A2 (BHP) moet matchen met een cel in STAMDATA!D3:D5001 en als laatste moet C2 (MAAND) matchen met een cel in STAMDATA!C3:C5001.

Aan ALLE voorwaarden moet dus worden voldaan, anders krijgen we een foutmelding!

Voorbeeld Index vergelijken

Download de voorbeeld file HIER!

Ik kreeg een interessante vraag hoe je gegevensvalidatie op een cel kan toepassen zodat er alleen alfanumerieke tekens A-Z / 0-9 zijn toegestaan. Waarschijnlijk zijn er meer wegen die naar Rome leiden maar dankzij de handige Excel functie vertaal site https://nl.excel-translator.de/translator/ kwam ik op de volgende Nederlands formule:

=ISGETAL(SOMPRODUCT(VIND.SPEC(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Hoe pas je dit toe?

Ga in het lint naar het tabblad 'Gegevens', kies 'Gegevensvalidatie' (A), 'Aangepast' (B) en copy & paste bovenstaande functie in het 'formule' vak (C).
In mijn voorbeeld wil ik dus de tekst in cel A1 valideren. Is het een andere cel, pas dit dan aan!

Waarden zoals AB1234 worden nu geaccepteerd, A!BCD wordt geweigerd:

Download het voorbeeld bestand hier