Verticaal zoeken o.b.v. meerdere cellen en datum

[raoulduivestein]

Goedemiddag,

Ik heb een vraag over het volgende,
ik heb een excelfile Bekijk bijlage Producten en Klanten.xlsx, met een lijst producten bij ieder product staat een begindatum en een einddatum. De extra keuze zijn afhankelijk van deze twee data.
Ik zou graag willen dat als er in de lijst klanten een nieuwe naam, product en datum wordt toegevoegd er direct de mogelijke keuzes worden getoond op basis van de ingevoerde datum.
Daarnaast zou ik graag een foutmelding willen toevoegen indien er een onmogelijke datum word ingevoerd, dit gebeurt als de datum niet voorkomt in de tussenliggende periode in de productenlijst.

Hopelijk kan en wil iemand mij helpen.

Groet,

Raoul Duivestein

 

[WHER]

Probeer eens of de formule in bijlage het gewenste resultaat geeft.

 

[Jezet]

Hoi Raoul,

Ik heb een vergelijkbare oplossing als WHER. Ik werk met een hulpkolom in J2. Het is maar net wat je wilt

 in J2:
=ALS(OF(ISLEEG($B2);ISLEEG($C2));"";SOMPRODUCT(($B$11:$B$99<=$B2)*($C$11:$C$99>=$B2)*($A$11:$A$99=$C$2)*RIJ($A$11:$A$99)))
in D2:
=ALS($J2="";"";ALS($J2=0;"Product en datumcombinatie klopt niet";ALS(ISLEEG(INDEX(D$11:D$99;$J2-10));"";INDEX(D$11:D$99;$J2-10))))

Beide werken we met SOMPRODUCT een heel handige formule om te zoeken. Hoe werkt de formule:
Excel kan bereiken 'zien' =A11:A23 ziet Excel als jouw bereik {"Product 1"\"Product 1"\"Product 1"\"Product 1"\"Product 2"\"Product 3"\"Product 3"\"Product 3"\"Product 3"\"Product 4"\"Product 4"\"Product 4"\"Product5"}
Vergelijk je dat met het veld C2 dan controleert Excel welke cellen voldoen het criterium.
=(A11:A23=C2) wordt dan {WAAR\WAAR\WAAR\WAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR\ONWAAR}
Dat doet de formule ook met de datum
=(A11:A23=C2)*(B11:B23=B2) wordt dan {1\1\0\0\0\0\0\0\0\0\0\0\0} waarbij 1 staat voor WAAR en 0 voor ONWAAR.
Voor rij 11 is dit A11=C2 is waar, voor B11=B2 geldt ook waar. Waar*waar =1 voor excel.

Zo kun je de formule uitbreiden met een tweede datum en het rijnummer en dan vind je dus het rijnummer van de geldige datum-productcombinatie.
Het kan ook zijn dat er geen geldige combinatie is in dat geval wordt het resultaat 0 omdat er ergens een onwaar criterium is en 1*1*0*15 = 0 (zomaar een voorbeeld)

Hiermee kun je heel leuke formules bouwen.
Een valkuil is echter dat je moet voorkomen dat er 2 regels zijn die aan de criteria voldoen. Want stel dat regel 11 en regel 16 aan alle voorwaarden voldoen dan wordt het resultaat niet 11 of 16, maar 11+16. Excel telt ze op!

Ook dat kun je voorkomen door niet SOMPRODUCT maar MAX te gebruiken (in een zogenaamde matrix-formule).

Goet,
Jeroen

 

[raoulduivestein]

Bedankt voor jullie antwoord!

Is het ook mogelijk om de productenlijst in een ander werkblad op te slaan, als de producten verplaatst in de file die WHER heeft aangepast krijg ik een foutmelding hoe kan ik dit voorkomen.

Bekijk bijlage Producten en Klanten2.xlsx

 

[WHER]

Zie aangepaste versie in bijlage.

hoe kan ik dit voorkomen

Probeer de werking van de formule te doorgronden met behulp van de "Evaluate formula" button, meer bepaald waarom het gedeelte
"ROW($A$11:$A$23)-10" veranderd moest worden in "ROW(Sheet2!$A$2:$A$14)-1" bij het verplaatsen van je productlijst.

 

[raoulduivestein]

Super bedankt voor je hulp Waar vind ik de Evalute Formula button :$

 

[Ad Becude]

Tab: Formules > Formules controleren > Formule evalueren

 

[WHER]

En ziet er uit als een soort vergrootglas met "fx" erin.