Als Cel B2 bevat tekst X dan tekst Y in D2

[koen999]

Beste helpers,

Ik ben opzoek naar een oplossing voor het volgende. Ik heb een lijst met gegevens van artikelen. In kolom B van die lijst staan namen. Nu wil ik daarachter een formule plakken dat 'als de naam bevat X' waarbij X zoekt in een brontabel in een ander tabblad.

Als bijlage een voorbeeld waarbij ik dus wil dat in cel D3 'A' komt te staan omdat B3 '*DL1/' bevat.

Ik hoop dat ik het voldoende duidelijk heb kunnen uitleggen en hoop op een oplossing.


Bij voorbaat dank!



Bekijk bijlage Map0.xls

 

[Joske2000]

Hi,

De volgende formule doet wat je vraagt :

=ALS(AANTAL.ALS(Brontabel!A:A;"*"&LINKS(B2;4)&"*")=1;VERT.ZOEKEN("*"&LINKS(B2;4)&"*";Brontabel!A:B;2;0);"")

Joske

 

[koen999]

Hartelijk dank voor de snelle reactie! Klopt het dat in deze formule de tekst altijd aan de linkerkant van de cel moet staan?

 

[arrie23]

De formule van Joske kijkt inderdaad naar de eerste 4 posities van de Naam in het Formuleblad. Als daar geen *DL1/TEST maar TEST/*DL1/ staat werkt hij niet meer.
Als je dat wil wordt het een stuk complexer. Ik denk niet dat het dan met een VERT.ZOEKEN op te lossen is.
Hoeveel verschillende waarden heb je in de Brontabel (je opzoektabel) staan?

 

[koen999]

Hoeveel verschillende waarden heb je in de Brontabel (je opzoektabel) staan?

Op dit moment 15, maar ik moet dit kunnen aanvullen. Ik heb het volgende gevonden:

{=INDEX(Brontabel!$B$1:$B$4;ALS(Brontabel!$A$2:$A$4>0;MAX(ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)*RIJ(Brontabel!$A$2:$A$4));""))}

Dit lijkt te werken, ik weet alleen niet precies wat dit doet. Kan iemand me dat uitleggen? Misschien is de formule nog te optimaliseren?

 

[arrie23]

Mooie formule! Volgens mij kan hij nog ietsje korter: {=INDEX(Brontabel!$B$1:$B$4;MAX(ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)*RIJ(Brontabel!$A$2:$A$4)))} (het is een matrixformule dus ingeven met CTRL+SHIFT+ENTER)

De formule in stukjes:
- ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)
Dit stuk bepaalt of *DL1/, *DL2/, *DL3/ voorkomt in de waarde van cel B2 ofwel *DL1/TEST. Het VIND.SPEC subdeel levert {1, #WAARDE, #WAARDE}: *DL1/ komt voor in *DL1/TEST vanaf positie 1, *DL2/ komt niet voor in *DL1/TEST, *DL3/ komt niet voor in *DL1/TEST
Het ALS(ISGETAL(...) bepaalt of het resultaat van het VIND.SPEC subdeel een getal is en zo ja een 1 anders een 0: {1, #WAARDE, #WAARDE} wordt dan via {WAAR;ONWAAR;ONWAAR} uiteindelijk {1,0,0}

-RIJ(Brontabel!$A$2:$A$4 levert standaard de matrix {2,3,4} ofwel de rijnummers waarop respectievelijk *DL1/, *DL2/, *DL3/ staan

De bovenste twee worden met elkaar vermenigvuldigd:
{1,0,0} * {2,3,4} = {2,0,0}

Hier wordt de MAX van genomen: MAX{2,0,0} = 2

Eigenlijk betekent deze 2 dat *DL1/ gevonden is in de waarde van cel B2 (*DL1/TEST) en op rijpositie 2 van de brontabel staat.

-{=INDEX(Brontabel!$B$1:$B$4;....
Dit deel van de formule kiest een bepaalde positie in de matrix $B$1:$B$4 van de Brontabel. MAX(ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)*RIJ(Brontabel!$A$2:$A$4))) leverde zoals hierboven beschreven de waarde 2 op. Dit is de waarde van de rij binnen matrix $B$1:$B$4, ofwel de tweede rij ofwel de waarde van cel B2 = A

Volgens mij is dit deel: ALS(Brontabel!$A$2:$A$4>0 overbodig.

Kan het voorkomen dat de naam uit kolom B geen van de waarden uit de Brontabel bevat? In dat geval is het handig om de formule nog wat aan te passen. In dat geval geeft hij nu de waarde van cel B1 ofwel de kolomkop weer (in het voorbeeld "Dan")

 

[koen999]

Kan het voorkomen dat de naam uit kolom B geen van de waarden uit de Brontabel bevat? In dat geval is het handig om de formule nog wat aan te passen. In dat geval geeft hij nu de waarde van cel B1 ofwel de kolomkop weer (in het voorbeeld "Dan")

Bedankt voor de uitleg arrie23. Het kan inderdaad zo zijn dat de naam in kolom B geen herkenbare waarden uit de brontabel bevat. Wat is jouw oplossing voor het feit dat daar nu 'dan' als resultaat uitkomt?

 

[arrie23]

=ALS(MAX(ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)*RIJ(Brontabel!$A$2:$A$4))=0;"geen waarde in brontabel";INDEX(Brontabel!$B$1:$B$4;MAX(ALS(ISGETAL(VIND.SPEC(Brontabel!$A$2:$A$4;B2));1;0)*RIJ(Brontabel!$A$2:$A$4))))

Het "geen waarde in brontabel" is uiteraard te wijzigen in iets naar keuze

 

[koen999]

Dank! Het blijft hiermee wel een matrixformule, toch?

 

[Haije]

voor D2:

=ALS(ISFOUT(VERT.ZOEKEN(LINKS(B2;5);Brontabel!$A$1:$B$4;2;0));"";VERT.ZOEKEN(LINKS(B2;5);Brontabel!$A$1:$B$4;2;0))

doortrekken naar beneden

 

[arrie23]

Het blijft inderdaad een matrixformule! De oplossing van Haije kijkt net als die van Joske ook alleen naar de eerste karakters vanaf links. Tip voor de volgende keer: een voorbeeld bijvoegen die ook alle varianten toont waarvoor je een oplossing wilt.

 

[koen999]

Het blijft inderdaad een matrixformule! De oplossing van Haije kijkt net als die van Joske ook alleen naar de eerste karakters vanaf links. Tip voor de volgende keer: een voorbeeld bijvoegen die ook alle varianten toont waarvoor je een oplossing wilt.

Nogmaals bedankt! Tip neem ik mee. Ik zet het topic op 'opgelost'.