Voorwaardelijk zoeken in lijst naar waarde op peildatum

[lam201]

Hi allemaal.

Ik heb een uitdaging.
Zie bijgevoegd bestand.

In kolom(C:E) heb ik een lijst met: Naam | Datum | Bedrag
en in cel I4 de peildatum.
nu ben ik op zoek naar een formule die het volgende doet.
Per naam ben ik op zoek naar het bijbehorende bedrag op de peildatum.
Maar in de lijst staan zowel data die voor als na de peildatum kunnen liggen.

Zie bijlage voor duidelijk voorbeeld.

Wie kan me helpen...?


Lambert

Bekijk bijlage zoekwaarde.xlsx

 

[Leotaxi]

Lambert,

met een functie niet gedaan gekregen, maar misschien is dit ook goed ?

mvg

Leo

 

[Cobbe]

Probeer anders deze eens:

=SOMPRODUCT(($C$2:$C$20=$H21)*($D$2:$D$20<=$I$4)*($E$2:$E$20))

 

[Leotaxi]

@ Cobbe

TS zoekt 1 enkele waarde, geen som

mvg
Leo

 

[Cobbe]

Klopt, maar half gelezen.

=MAX(ALS(($C$2:$C$20=$H21)*($D$2:$D$20<=$I$4);$E$2:$E$20;""))

 

[lam201]

Leo,


dank je voor de code. Ik ga hiermee even stoeien.
Als iemand nog een oplossing in een functie weet, dan houd ik me aanbevolen.

Lambert

 

[arrie23]

Ik ben lichtelijk uit de bocht gevlogen en zelfs een beetje de draad kwijt in mijn eigen formule maar volgens mij doet ie het ;o). Matrixformule dus afsluiten met CTRL+SHIFT+ENTER

=INDEX($E$2:$E$17;VERGELIJKEN(MIN(ALS(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4)*($I$4-$D$2:$D$17)=0;MAX(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4)*($I$4-$D$2:$D$17));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4)*($I$4-$D$2:$D$17)));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4)*($I$4-$D$2:$D$17);0))

 

[arrie23]

Kleine aanpassing voor als peildatum gelijk is aan datum:
=INDEX($E$2:$E$17;VERGELIJKEN(MIN(ALS(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)=0;MAX(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17);0))

 

[Leotaxi]

als ik een hoed op had, deed ik hem af :d

 

[VenA]

@ Leotaxi
Dan mag je hem weer opdoen:d Volgens mij klopt het namelijk niet.

In de bijlage een mogelijke oplossing met een UDF.

 

[arrie23]

Wat klopt er niet? Ik ben benieuwd. Wellicht kan ik het aanpassen

 

[Leotaxi]

ben ook benieuwd, in geplaatst voorbeeld waren de gezochte waardes toch juist

maar aangezien ik gen hoed heb, geen extra werk voor mij :d


mvg
Leo

 

[VenA]

Vul als peildatum eens 14-06-2008 in. Alleen bij Paul mag dit dan nog een resultaat opleveren. Zoals ik de vraag gelezen heb moet het bedrag getoond worden van de grootste datum voor of op de peildatum. Voor de andere drie zijn er geen waarden bekend van voor of op 14-06-2008 en dan zou de waarde dus 0? moeten zijn. Maar is mijn interpretatie van de vraag

 

[Cobbe]

Deze matrixformule zoekt dat voor u uit:

=MAX(ALS(($C$2:$C$20=$H21)*($D$2:$D$20<=$I$4);$E$2:$E$20;""))

Activeren via Ctrl-Shft-Enter

 

[Leotaxi]

helemaal waar, dus werk aan de winkel
maar niet voor mij :d

mvg
Leo

 

[arrie23]

Bedankt VenA voor je oplettendheid. Nieuwe poging om het met een 'gewone' formule op te lossen:
=ALS(MIN(ALS(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)=0;MAX(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)))=0;"geen waarde";INDEX($E$2:$E$17;VERGELIJKEN(MIN(ALS(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)=0;MAX(($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17)));($C$2:$C$17=$H21)*($D$2:$D$17<$I$4+0,01)*($I$4+0,01-$D$2:$D$17);0)))

 

[Cobbe]

Dan is deze hier toch echt wel te makkelijk zeker?

{=MAX(ALS(($C$2:$C$20=$H21)*($D$2:$D$20<=$I$4);$E$2:$E$20;""))}

 

[arrie23]

Haha, waarom moeilijk doen als het makkelijk kan. Deze had ik over het hoofd gezien tussen de antwoorden. Excuses. Er zijn meerdere wegen die naar Rome leiden maar de jouwe is duidelijk de snelste en de kortste

 

[arrie23]

Cobbe, volgens mij gaat het toch niet altijd goed met jouw verkorte formule. Jij zoekt naar het maximum bedrag. In het voorbeeld groeit het bedrag naarmate de tijd vordert dus gaat het met dit voorbeeld wel goed. Maar eigenlijk zou de formule moeten zoeken naar het bedrag behorende bij de maximaal toegestane datum en dit hoeft in theorie niet het hoogste bedrag te zijn. Met het huidige voorbeeld gaat het wel helemaal goed maar kies bijvoorbeeld eens voor peildatum 22-02-2014 en vul in cel E6 het bedrag 100 EUR in. Deze 100 EUR zou dan de uitkomst voor John moeten zijn maar is met jouw formule 3800 EUR.

 

[Cobbe]

Je hebt (alweer) gelijk maar kom er zo 1 2 3 niet uit, zal straks nog even puzzelen.