verticaal- en horizontaal zoeken met meerdere voorwaarden

[gertjan1986]

Beste Mensen,

Ik ben op zoek naar een formule om mijn leven een stuk eenvoudiger te maken.

Ik moet belastingtarieven invullen in een datasheet vanuit een tabel. Dit gebeurt nu nog handmatig maar ik zou graag daar een formule voor willen hebben.

In het bijgevoegde bestand een deel van de tarieven en een opzet van de dataset.

Nu moet in kolom M t/m O de tarieven komen te staan. Op twee regels staan de waarde wat de juiste zouden moeten zijn.

Wat ik wil: een formule waar ik uit de tabel de tarieven kan halen doormiddel van drie verschillende voorwaarde. De voorwaardes in kolom J en K zijn terug te vinden in de kolommen van het tarief tabel. Daarnaast is er voor tarief 1 nog een extra voorwaarde namelijk of het wonen of niet wonen is (zie kolom L) deze is dan te vinden in rij 4 van de tabel. Voor belastingtarief 2 en 3 geldt deze voorwaarde niet. Daar maakt het niet uit wat er in kolom L staat.

Kunnen jullie mij helpen met een formule.
ik heb geprobeerd zo gedetailleerd mogelijk te zijn maar als er vragen zijn dan hoor ik dat graag.

Groet,


Bekijk bijlage tarieven.xlsx

 

[AlexCEL]

Er zijn meer mogelijkheden, maar wellicht voldoet dit:

in M2: =SOMPRODUCT(($B$5:$B$13=$J4)*($C$5:$C$13=$K4)*VERSCHUIVING($D$5:$D$13;0;ALS(L4="wonen";0;1)))
in N2: =SOMPRODUCT(($B$5:$B$13=$J4)*($C$5:$C$13=$K4)*($F$5:$F$13))
in O2: =SOMPRODUCT(($B$5:$B$13=$J4)*($C$5:$C$13=$K4)*($G$5:$G$13))

Daarna naar beneden doortrekken.

 

[zapatr]

Als je eenzelfde formule voor je hele tabel wil, dan zou je o.a. onderstaande formule kunnen gebruiken.
Typ in M4 en sluit af met Ctrl-Shift-Enter:

=INDEX($B$3:$G$13;VERGELIJKEN($K4;ALS($B$3:$B$13=$J4;$C$3:$C$13;0));ALS(VERGELIJKEN(M$3;$B$3:$G$3;0)<5;VERGELIJKEN(M$3;ALS($B$4:$G$4=$L4;$B$3:$G$3;0));VERGELIJKEN(M$3;$B$3:$G$3;0)))

Formule naar rechts en naar beneden doorvoeren.
En als je consequent wil zijn met je linkertabel, daarna de celeigenschappen van de kolommen M en N instellen als 'percentage' met 4 decimalen en kolom O als 'Financieel' met 2 decimalen.

 

[gertjan1986]

Hoi AlexCel en zapatr,

Bedankt voor jullie reactie. Beide werken al kies ik nu wel voor die van Zapatr omdat dat een formule is.
Kan je me nog wel uitleggen waarom je moet eindigen met Ctrl-Shift-Enter. Dat heb ik nog niet eerder gezien namelijk en ben nieuwsgierig.

groet,

 

[zapatr]

Formules ingevoerd via Ctrl-Shift-Enter zijn zgn. matrixformules en matrixformules moet je invoeren via Ctrl-Shift-Enter (dat laatste moet je goed onthouden). Matrixformules werken niet voor 1 cel maar voor een groep van cellen (een bereik). Ik kan dat misschien het best uitleggen aan de hand van een zeer eenvoudig voorbeeld.
Stel dat in A1:A4 de getallen 1,2,3, en 2 staan en je wil van dat bereik alleen de cellen optellen waar een 2 in staat. Het antwoord kun je eenvoudig zonder matrixformule vinden met =SOM.ALS(A1:A4;2). Dat kun je dan ook best gebruiken, maar om op een simpele manier uit te leggen wat een matrixfunctie is, gebruik ik nu voor de oplossing een matrixfunctie.

Typ in (bv.) D1: =SOM(ALS(A1:A4=2;A1:A4)) en sluit af met Ctrl-Shift-Enter, het resultaat is 4.
Typ in (bv.) E1 dezelfde formule zonder Ctrl-Shift-Enter, het resultaat is 0.
Waarom? Omdat de formule in E1, wat de voorwaarde voor het optellen van de cellen die gelijk zijn aan2 betreft, alleen maar kijkt of de eerste cel van A1:A4 (dat is A1) gelijk is aan 2. Dat is niet het geval, er wordt dus niet opgeteld, het resultaat is 0.
Wijzig nu A1 (waar 1 in staat) in 2. Het resultaat in D1 is nu 6 (correct) en in E1 is het 9 (fout). De formule in E1 'denkt' dat alle cellen van A1:A4 moeten worden opgeteld als A1 gelijk is aan 2 en dat is nu het geval. Maar dat is niet wat we wilden.
Zo zie je dat, als je bij een matrixformule niet gebruik maakt van Ctrl-Shift-Enter, je dan foute uitkomsten krijgt.
Door gebruik te maken van Ctrl-Shift-Enter komen er accolades rond de formule te staan. Die accolades mag je niet zelf invoeren, die moet je laten plaatsen door Ctrl-Shift-Enter te gebruiken.
Duidelijk zo?