vergelijken;verschuiving en index

[Jon12]

Beste specialisten,

Mogelijk dat jullie mij kunnen helpen met de volgende uitdaging:

In de bijlage is een excel-sheet met 2 tabbladen toegevoegd.

Het eerste blad laat een tabel zien met groente/fruit artikelen en 1VP (= 1 voordeelpakket).
Wanneer in cel c1 VP1 wordt geselecteerd, vult de 2e tabel zich automatisch met de gevraagde gegevens.
Dit komt door de verborgen vergelijken;verschuiving formule en de index-formules.

Nu probeer ik op het tweede tabblad hetzelfde te bereiken, echter met meerdere voordeelpakketten. Het lastige is dat de vergelijkingsfunctie slechts met één kolom of rij werkt. En dat voordeelpakketten dezelfde producten kunnen bevatten.

De formule in cel N7 werkt niet (ook andere mogelijkheden zijn niet gelukt). Wanneer hier de juiste formule staat, kan in de rest van de tabel, de juiste index-formule worden geplaatst.

Weten jullie een oplossing? (bij voorkeur géén VBA).

Alvast bedankt voor het meedenken.

 

[JEC.]

Als je office 365 hebt kan het zo

In O7, tabblad 2:
Houdt ook rekening met een eventueel groter wordende tabel.

=LET(col;KOLOM(Tabel2[#Kopteksten])-1;FILTER(FILTER(Tabel2;POS.NEG(PRODUCTMAT(N(Tabel2[[VP1]:[Aantal VP4]]=E3);REEKS(MAX(col)-3))));(col<4)+(col=(VERGELIJKEN(E3;Tabel2[#Kopteksten];0))+1)))

Voor oudere versies:
Zorg hier dat de kolomkoppen identiek zijn aan de tabel.

=ALS.FOUT(INDEX(Tabel2;AGGREGAAT(15;6;1/(1/((Tabel2[[VP1]:[Aantal VP4]]=$E$3)*(RIJ(Tabel2[Artikel])-6)));RIJ(A1));KLEINSTE(VERGELIJKEN($O$6:$R$6;Tabel2[#Kopteksten];0);KOLOM(A1)));"")

 

[sylvester-ponte]

als je 365 hebt dan is deze ook mogelijk: ik heb voor het gemak een verschuivingsnummer naast je invoer (grijze cel) gezet:

 

[Jon12]

Zelf werk ik met office 365 en dan werkt de formule van Sylvester perfect. En de 365 code van JEC ook.
Helaas moet ik het toepassen in een omgeving waar wordt gewerkt met Microsoft Office Professional Plus 2016.

Daar heb ik de code voor oudere versies van JEC geprobeerd, maar dat is niet gelukt.
Ik heb de formule ook geprobeerd met ctrl + enter, omdat het een matrixformule lijkt. Maar ook dat werkte niet.
Mogelijk doe ik iets fout. Zo weet ik niet waarom er bij rij en kolom A1 in de formule staat. Is A1 hier een vaste gegeven?
En ook -6 in dit stuk: "*(RIJ(Tabel2[Artikel])-6)))" begrijp ik niet helemaal goed. Waarom is het hier het getal 6?

Enig idee wanneer het versie MS Office prof. plus 2016 betreft?

 

[JEC.]

Je moet afsluiten met control shift enter.
De -6 zorgt ervoor dat de reeks begint bij 1.

De tabel begint namelijk niet in rij 1 in dit geval.

 

[peter59]

Hallo,

Bijgevoegd nog een voorbeeldje zoals het met power query zou kunnen.

 

[Jon12]

Natuurlijk is het minus het aantal regels dat de eerste regel begint, waar is die smiley dat ik mezelf tegen mijn kop sla

En ctrl+shft enter inderdaad, maar heb in het grotere bestand nog steeds een foutmelding, dus ik zal hier nog wat moeten puzzelen om te zien waar het verschil in zit. Daar ga ik mee aan de slag.

Een draaitabel met slicer is inderdaad ook een goed idee. PowerQuery is volgens mij niet beschikbaar, maar met een hulpkolom kan het ook.

ik ben er nog niet helemaal, maar ik ga hiermee aan de slag. dank jullie wel :thumb:

 

[peter59]

Hallo Jon

Power query is ook voor office 2016 beschikbaar.