In de meeste administratieve toepassingen van Excel zie je wel ergens een opzoekfunctie opduiken. Dit meestal in de vorm van verticaal zoeken (ofwel VERT.ZOEKEN), maar ook in de horizontale vorm (HORIZ.ZOEKEN). Maar lang niet altijd is deze functie (zonder aanpassingen aan het spreadsheet) toepasbaar.
Voorbeeld
We hebben een overzicht van bedragen per maand en per regio.
In welke maand en in welke regio hebben we de maximale omzet gehaald?
Met verticaal zoeken lukt dat niet direct, want zoals iedereen wel eens tot zijn of haar frustratie heeft meegemaakt, kun je met deze functie niet “naar links” zoeken.
Dus zit er niets anders op dan het verplaatsen van kolommen of het aanmaken van hulpkolommen.
Alternatief voor VERT.ZOEKEN (en ook voor HORIZ.ZOEKEN)
Als VERT.ZOEKEN dus niet (direct) werkt moeten we het anders aanpakken.
We willen vanuit het vorige voorbeeld (waarin de gegevens doorlopen tot en met de Excel-rij 66) het volgende resultaat bereiken:
Dus vanuit het maximum-bedrag moet Excel ons de betreffende maand en regio laten zien.
Eerst het makkelijkste gedeelte: in cel G4 zoeken we het maximum bedrag op met de formule =MAX(D4:D66)
Maar hoe vinden we nu de betreffende maand en regio?
Hiervoor zijn 2 stappen nodig (die we uiteindelijk in één formule stoppen):
- zoek de regel, waarin het maximum zit
- zoek in die regel de maand (of regio)
Regel opzoeken met VERGELIJKEN
We willen dus weten op welke regel in het bereik D4:D66 het maximum (de waarde in cel G4) staat.
Dat gaat heel simpel met de formule =VERGELIJKEN(G4; D4:D66; 0).
Denk aan de 0 (nul) op het einde; deze geeft aan, dat de functie een exacte waarde moet zoeken. De reeks getallen hoeft dan ook niet gesorteerd te zijn.
Zet deze formule in H4 en we weten in ieder geval op welke regel in het bereik het maximumbedrag staat.
Betreffende maand (of regio) opzoeken met INDEX
In cel G5 zetten we nu de formule =INDEX(B4:B66;H4) en klaar is Kees (of Jolanda)!
Wil je de regio weten, dan moet het natuurlijk zijn =INDEX(C4:C66;H4).
Deze functie werkt dus net “andersom” als VERGELIJKEN: waar VERGELIJKEN de plaats zoekt waar een opgegeven waarde in een bereik staat, zal INDEX een waarde in een bereik opzoeken, als je de plaats in dat bereik meegeeft.
Resultaat
Als we voorgaande stappen samenvatten in één formule dan ziet dat er als volgt uit:
=INDEX(B4:B66;VERGELIJKEN(G4;D4:D66;0))
of =INDEX(C4:C66;VERGELIJKEN(G4;D4:D66;0))
De formules kunnen nog compacter door ook het berekenen van het maximum hierin op te nemen.
LET OP: de gebruikte bereiken in de functies VERGELIJKEN en INDEX moeten even groot zijn en met elkaar corresponderen: regels (of bij horizontaal zoeken de kolommen) moeten met elkaar overeenkomen. In ons voorbeeld beginnen de bereiken allemaal in rij 4 en lopen door t/m rij 66.
Wanneer je in je spreadsheets met namen werkt dan worden de formules een stuk leesbaarder:
=INDEX(Maanden;VERGELIJKEN(MaxBedrag;Bedragen;0)
of =INDEX(Regios;VERGELIJKEN(MaxBedrag;Bedragen;0)
Een volgende keer zullen we op het gebruik van bereiknamen terugkomen.
Minimum, gemiddelde, mediaan
Wat we net met het maximum van de bedragen hebben gedaan kan natuurlijk ook met andere samenvattingsformules; of toch niet? Wat denk je?
Voorbeeldbestand
Download het Voorbeeldbestand. Hier is ook een variant met bereiknamen terug te vinden.