Excel- De dichtstbijzijnde datum vinden met verticaal zoeken

[Anoniem: 959077]

Beste excel experts,

In blad 1 heb ik namen staan met datums erachter wanneer een afspraak is.
In blad 2 heb ik diezelfde namen staan met datums wanneer zij iets hebben gekocht.

Nou zou ik graag in blad 1 de aankoopdatum zien die het dichtst bij de afspraak datum komt.

Voor de duidelijkheid, hier een voorbeeld.
Stel, de afspraak met meneer Jansen is op 25-2-2016.
Meneer Jansen heeft aankopen gedaan op 2-1-2016, 4-2-2016 en 20-2-2016
20-2-2016 komt het meest in de buurt bij de afspraakdatum, dus die zou ik dan graag zien verschijnen.

Ik heb verschillende dingen geprobeerd. Het lukte mij bijvoorbeeld wel om de meest recente datum te vinden met de volgende formule en dan afsluiten met Ctrl+Shift+Enter. Verder kom ik echter niet.

=MAX(ALS(Blad2!A2:A8=A2;Blad2!B2:B8))

Kan iemand mij hier mee helpen?

[yozgoesdigital]

Geen compleet antwoord, maar volgens kan het bereiken met MATCH en INDEX combinatie.
zie ook deze link voor uitleg:
http://www.randomwok.com/excel/how-to-use-index-match/
In de MATCH type argument 1 (default) kan je dan de juiste datum vinden

In bovenstaande optie ga ik er wel vanuit dat je aankoop altijd kleiner is dat afspraak datum. Als je de dichtstbijzijnde datum wilt vinden vanaf referentie zowel in toekomst als verleden zul je denk ik met [afspraak datum] - [aankoopdatum] moeten werken --> daar negatieve waardes positief maken --> dan kleinste waarde vinden.
(Ik heb alleen Engelstalige Excel dus wellicht moet je de functies nog wel vertalen naar Nederlands)

[Lustucru]

Ervan uitgaande dat de afspraakdatum zowel voor als na de aankoop kan liggen krijg je idd iets als:
- zoek het kleinste verschil
- zoek de datum die exact dat verschil oplevert. Simpelweg optellen of aftrekken lukt niet omdat je het verschil zowel positief als negatief kan zijn.

code:

=INDEX($C$1:$C$12;VERGELIJKEN(KLEINSTE(ALS($D$1:$D$12=$B$1;ABS($C$1:$C$12-$A$1);9999999);1);ABS($C$1:$C$12-$A$1);0))

[Morelleth]

Pakt verticaal of horizontaal zoeken sowieso niet de dichts bijzijnde waarde? (tenzij je onwaar ingeeft als laatste)

Waarschijnlijk is er een makkelijkere manier maar je kan het volgende doen:
=HORIZ.ZOEKEN(VERT.ZOEKEN(NAAM;Tabel blad1;2;ONWAAR);tabel blad 2;VERT.ZOEKEN(NAAM;tabel blad 2;2))

Zet achter de naam op blad 2 een nummer waarmee je de horizontaal zoek functie een indexgetal geeft en het werkt.


In de kleine test die in net deed:
krijg je dan de volgende formule =HORIZ.ZOEKEN(VERT.ZOEKEN(A10;A2:B6;2;ONWAAR);F2:K6;VERT.ZOEKEN(A10;F2:H6;2))

tabel 1 tabel 2
A 15-05-17 A 1 12-05-17 14-05-17 18-05-17
B 12-05-17 B 2 14-03-17 17-09-17
C 16-06-17 C 3 3-07-17 6-08-17 21-05-17 1-05-17
D 18-09-17 D 4 12-02-17 10-08-17 2-07-17
E 12-04-17 E 5 6-09-17 7-08-17


invoer resultaat
A 14-05-17


(oftewel je zoekt eerst in tabel 1 naar de naam met verticaal zoeken, daarmee vind je de afspraakdatum, en dan zoek je daarmee in tabel 2 in de juiste rij (die je kan vinden met die 2e verticaal zoek functie) naar het gewenste resultaat.)

Geeft toch wat problemen, ookal leek het op het eerste zicht te werken.
Maar goed mogelijk kan je er al iets mee.

[ Voor 17% gewijzigd door Morelleth op 22-08-2017 14:20 ]

[Anoniem: 959077]

Excuses voor de late reactie heren, en enorm bedankt voor de antwoorden!

Ik ben er met behulp van iemand uitgekomen door eerst alle datums van blad 2 te vergelijken met blad 1 en vervolgens te filteren, zodat de dichtstbijzijnde bovenaan staat. Vervolgens krijg je die met verticaal zoeken.