Uren op basis van drie voorwaarden

[AnkieForum]

Hi allemaal,
Bekijk bijlage Voorbeeld verlof obv leeftijd.xlsx
Ik probeer om in bijgaande excel gegevens van het ene tabblad in het andere te krijgen. Normaal is dit met verticaal zoeken e.d. goed te doen, ik zit alleen met een heleboel voorwaarden.
De situatie is als volgt:
- Medewerkers kunnen op basis van hun arbeidsvoorwaarden per leeftijd(sgroep) extra verlofuren krijgen
- Deze verlofuren worden o.b.v. hun leeftijd op een peildatum toegekend
- De stappen tussen de leeftijden zijn niet evenredig en wisselen ook per arbeidsvoorwaarde
- De peildatum is wel per arbeidsvoorwaarde gelijk

In het achterliggende tabblad staan per arbeidsvoorwaarde verschillende regels met de 'vanaf leeftijd' en het aantal uren met de peildatum erachter
In het voorste tabblad staat de medewerker met de arbeidsvoorwaarden die die medewerker heeft en de geboortedatum.

Ik wil graag per medewerker in het voorste tabblad hebben staan hoeveel extra verlofuren zij krijgen.

Wie kan mij verder helpen? Ik hoop dat mijn verhaal en bijgaande Excel duidelijk genoeg zijn, anders hoor ik het graag.

Ankie

 

[exhelp]

Zet deze formule eens in G5 op blad 'Medewerkers'
Doet die wat je wil?

=ALS(SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=B5)*(E5>'Gegevens verlof op leeftijd'!$B$5:$B$38))=0;0;INDEX('Gegevens verlof op leeftijd'!$C$5:$C$38;SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=B5)*(E5>'Gegevens verlof op leeftijd'!$B$5:$B$38))))

 

[exhelp]

En deze in H2 (vervolgens doorvoeren naar beneden)

=ALS(D5=2;ALS(SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=$B5)*(F5>'Gegevens verlof op leeftijd'!$B$5:$B$38))=0;0;INDEX('Gegevens verlof op leeftijd'!$C$5:$C$38;SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=$B5)*(F5>'Gegevens verlof op leeftijd'!$B$5:$B$38))));"")

zie ook bijlage

 

[AnkieForum]

Dank je wel voor je snelle reactie. Ik kan niet alle formules volgen die je gebruikt (moet ik even induiken), maar het resultaat komt helaas niet goed uit.

Voorbeeld: Marieke is op Peildatum 1 57,5 jaar en peildatum 2 58 jaar. Volgens haar eigen arbeidsvoorwaarden (Bedrijf B) zou ze dan 53,2 en 76 uur moeten krijgen. Volgens de formule krijgt ze maar 48 en 46 uur.

Heb je nog een idee waar dat in zit?

 

[Timshel]

Ik kom uit op een matrixformule; ingeven in G5 met CTRL SHIFT ENTER, daarna doortrekken naar beneden en naar rechts:

=ALS(E5="";"";ALS.FOUT(INDEX('Gegevens verlof op leeftijd'!$C$5:$C$38;GROOTSTE(ALS(('Gegevens verlof op leeftijd'!$A$5:$A$38=$B5)*('Gegevens verlof op leeftijd'!$B$5:$B$38<=E5);RIJ('Gegevens verlof op leeftijd'!$A$5:$A$38)-4);1));""))

 

[exhelp]

Hoi Ankie,

Het was me wat lang geleden.
Er zaten 2 fouten in mijn formules.

Nu zou het juist moeten zijn:

G5: dan doorvoeren naar beneden

=INDEX('Gegevens verlof op leeftijd'!$C$5:$C$38;SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=$B5)*(E5>='Gegevens verlof op leeftijd'!$B$5:$B$38)*(E5<'Gegevens verlof op leeftijd'!$B$6:$B$39)*(RIJ('Gegevens verlof op leeftijd'!$A$5:$A$38)-4)))

H5: dan doorvoeren naar beneden

=ALS(D5=2;INDEX('Gegevens verlof op leeftijd'!$C$5:$C$38;SOMPRODUCT(('Gegevens verlof op leeftijd'!$A$5:$A$38=$B5)*(F5>='Gegevens verlof op leeftijd'!$B$5:$B$38)*(F5<'Gegevens verlof op leeftijd'!$B$6:$B$39)*(RIJ('Gegevens verlof op leeftijd'!$A$5:$A$38)-4)));"")

Zie ook bijlage

 

[AnkieForum]

Dank je wel Timshel, maar de formule van ExHelp werkt ook.

ExHelp, ik wil ook graag begrijpen wat er gebeurt, maar ik ben in zowel de indexformule als de somproduct niet thuis. Index kan ik wel uitvogelen vermoed ik straks, maar ik kom er niet uit wat je doet met de SomProduct. Ik ben aan het ontleden welke gegevens je ophaalt, maar ik begrijp niet wat de formule ermee doet en waarom.

Zou jij het uit kunnen leggen (voor dummies )

Thanks