Als functie in combinatie met verticaal zoeken?

[bonjovi1977]

Mijn vraag is het volgende.
Als er in cel B4 een waarde staat die ook voorkomt in de cellen J7 t/m J12, dan moet de waarde die achter deze cel staan geplakt worden in de cel D2.

Ditzelfde moet ook gebeuren met cel B5. Deze gegevens moeten geplakt worden in cel E2.
Echter kan cel B5 ook leeg zijn en dan moet cel E2 ook leeg blijven.

Ik hoop dat iemand me uit deze brand kan helpen

Bekijk bijlage Voorbeeld.xlsx

 

[tkint]

Beste,

Gebruik in D2 de formule: =ALS(B4<>"";(VERT.ZOEKEN(B5;J$7:L$12;2;ONWAAR));"")
Gebruik in E2 de formule: =ALS(B5<>"";(VERT.ZOEKEN(B5;J$7:L$12;3;ONWAAR));"")

 

[bonjovi1977]

De waardes die staan in cel J7 t/m L12 kan ik die ook ergens opslaan, zodat ik met meerdere bestanden hiervan gebruik kunnen makenn maken?
Want ik moet steeds een ander excel bestand openen en dan verwerken

 

[MirjamHelp]

Hallo Bonjovi1977

Je kan ook met formules werken
Voor begin datum:

=DATUM(LINKS(B4;4);RECHTS(B4;2);1)

Voor eind datum:

=DATUM(LINKS(B4;4);RECHTS(B4;2)+1;1)-1

Hiermee heb je geen opzoektabel nodig

Gr. Mirjam

 

[bonjovi1977]

Hoi Mirjam,

Super bedankt
Ik heb alleen verzuimd om het hele lijstje te vermelden, want er staan nog meer datums in, zoals;
tijdvakcode begindatum einddatum
201321 1-1-2013 31-3-2013
201322 1-2-2013 30-4-2013
201332 1-12-2013 28-2-2014
201340 1-1-2013 31-12-2013

Is het nu nog steeds mogelijk zonder opzoektabel?

 

[MirjamHelp]

Dat ligt eraan hoe de codes die bij de perioden horen zijn opgebouwd.
Zo te zien zit er een logica in, want als je twee maanden hebt, dan staat op de een na laatste positie een 2. Althans, dat leid ik af uit je voorbeeld, dus dat kan met een formule.
Maar hoe dat zit met je laatste voorbeeld weet ik niet.

De formule die ik je gaf geeft jjjjmm, dus vier cijfers voor het jaartal en dan twee cijfers voor de maand.

 

[OctaFish]

Waar is de code 201321 op gebaseerd? Het oogt alsof je lukraak uit het luchtruim een getal achter het jaartal hebt geplakt. Ik kan althans op basis van het getal 21 nergens afleiden dat het om een kwartaal gaat. Wat voor verrassingen zitten er nog meer in je lijstje?

 

[MirjamHelp]

Michel,
Het lijkt erop dat het format is 4 cijfers voor het jaar, 1 cijfer voor het aantal maanden en 1 cijfer voor de maand.
201321 zou dan 01-01-2013 tm 28-02-2013 zijn.
Maar hoe zit het dan wanneer de maand november is? Lijkt mij dat er dan 20 bij de maand wordt opgeteld. Maar dat klopt ook niet, want 3e voorbeeld is 3 maanden...
En dan de 4 in het laatste voorbeeld?

Dus als Bonjovi1977 die logica kan achterhalen, zou het met een formule opgelost kunnen worden.

Gr. Mirjam

 

[bonjovi1977]

Ik zal voor de duidelijkheid de alle tijdvakcodes geven die ingevuld kunnen zijn.

Bekijk bijlage tijdvakcodes.xlsx

 

[MirjamHelp]

hoe kom ik nou aan twee maanden???:shocked:

 

[OctaFish]

Je doet er goed aan voor de jaarcode 201341 te gebruiken en niet 40, want alle andere groepen beginnen ook met 1. Consequent werken is het halve werk .
In C4:

=ALS(WAARDE(RECHTS(B4;2))<13;DATUM(LINKS(B4;4);RECHTS(B4;2);1);ALS(EN(WAARDE(RECHTS(B4;2))>20;WAARDE(RECHTS(B4;2))<41);
DATUM(LINKS(B4;4);RECHTS(B4;2)-20;1);ALS(WAARDE(RECHTS(B4;2))>40;DATUM(LINKS(B4;4);RECHTS(B4;2)-40;1);2)))

In D4:

=ALS(WAARDE(RECHTS(B4;2))<13;DATUM(JAAR(C4);MAAND(C4)+1;0);ALS(EN(WAARDE(RECHTS(B4;2))>20;WAARDE(RECHTS(B4;2))<41);
DATUM(JAAR(C4);MAAND(C4)+3;0);ALS(WAARDE(RECHTS(B4;2))>40;DATUM(JAAR(C4);MAAND(C4)+13;0);"")))

 

[bonjovi1977]

Octafish,

Ik kan 201340 helaas niet laten aanpassen of wijzigen.
ik krijg het zo ook maar aangeleverd.

 

[OctaFish]

Dan moet je de formule aanpassen . Hij werkt nu met 41 als waarde, dus daar maak je in de formule dan 40 van. En van >40 maak je >=40.

 

[OctaFish]

Dan wordt het voor de volledigheid dus:

=ALS(WAARDE(RECHTS(B13;2))<13;DATUM(LINKS(B13;4);RECHTS(B13;2);1);ALS(EN(WAARDE(RECHTS(B13;2))>20;WAARDE(RECHTS(B13;2))<40);
DATUM(LINKS(B13;4);RECHTS(B13;2)-20;1);ALS(WAARDE(RECHTS(B13;2))>=40;DATUM(LINKS(B13;4);RECHTS(B13;2)-39;1);2)))

 

[bonjovi1977]

Super hartstikke bedankt.
Ik heb alleen nog 1 vraagje.
Echter kan cel B5 ook leeg zijn en dan moet cel E2 ook leeg blijven

 

[OctaFish]

Tuurlijk kan dat; een extra ALS ervoor zetten.

=Als(B5="";"";<Bestaande formule>)

 

[snb]

=TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20);1);"dd-mm-jjjj")& " _ " &TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20)+1+2*INT(RIGHT(B2;2)/21);0);"dd-mm-jjjj")

en met de 201340 exotica erin:

=TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20)+N(RIGHT(B2;2)="40");1);"dd-mm-jjjj")& " _ " &TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20)+1+2*INT(RIGHT(B2;2)/21)+10*N(RIGHT(B2;2)="40");0);"dd-mm-jjjj")

Wil je de begindatum en de einddatum splitsen in aparte cellen:

K1: =TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20)+N(RIGHT(B2;2)="40");1);"dd-mm-jjjj")
L1: =TEXT(DATE(LEFT(B2;4);MOD(RIGHT(B2;2);20)+1+2*INT(RIGHT(B2;2)/21)+10*N(RIGHT(B2;2)="40");0);"dd-mm-jjjj")