Zoeken in meerdere kolommen en tekst 'optellen'

[Yokkie]

Zit met twee bestanden met in de ene de zoekwaarde en de andere een matrix waarin een exacte match gevonden moet worden. Heb voor het gemak een voorbeeldbestandje gemaakt. Met de functie ISNB zoekt de formule nu in 2 kolommen, maar ik wil dat er verder gezocht wordt in kolom 3, 4 etc.

Ok het is veel handiger om de waarden in die verschillende kolommen onder elkaar te zetten, maar dat is nou juist net niet de bedoeling. VERT.ZOEKEN, INDEX, ALS, EN, OF...van alles geprobeerd en gezocht maar het lukt me niet om het resultaat in 1 kolom te krijgen. Iemand die weet of dit uberhaupt mogelijk is en zo ja....hoe?!

Ook bereid om concessies te doen en het resultaat in meerdere kolommen terug te krijgen, maar dan loop ik weer vast omdat ik niet weet hoe je tekst optelt aangezien in de kolommen de waarde #N/B staat.

Groet,

 

[bandito bob]

Volgende matrix formule in cel I1:

=ALS(AANTAL.ALS(A$1:E$6;H1)=0;"";INDEX(F$1:F$6;MAX(ALS(AANTAL.ALS(VERSCHUIVING(A$1;RIJ($1:$6)-1;;;5);H1)>0;RIJ($1:$6);0))-1;))

Let op! Matrix formule, zie mijn handtekening.

Hierna kan je hem doortrekken naar beneden.

 

[Yokkie]

Dit is super! Voor mijn voorbeeld werkt het. In mijn orginele bestand staat A1 t/m F6 elders op de sheet. Bijvoorbeeld in de kolommen E26 t/m K31. Eerste stuk van de formule snap ik hoe ik die kan aanpassen, maar hoe moet je dan omgaan met de verschuiving?

 

[bandito bob]

In de verschuiving geeft:
rij($1:$6) aan dat het gebied waar gezocht wordt 6 rijen hoog is.
de ;;;5 geeft aan dat het gebied 5 kolommen breed is.

Misschien kom je er zo wel uit....

 

[HSV]

Alhoewel het leek als of het super werkte, ben ik zo vrij geweest om die mooie formule eens te bekijken.

Ik heb de formule gekopiëerd en in I1 gezet, met als resultaat een aantal foute letters (gele kolom).

Helaas schuilt er een foutje in Paul’s formule, vandaar dat het misschien niet wil.

Hierbij heb ik een andere matrixformule gebruikt (groene kolom).
Test dit eens in je origineel bestand.

 

[bandito bob]

Ik heb inderdaad een foutje gemaakt in de formule. In de oude formule:

=ALS(AANTAL.ALS(A$1:E$6;H1)=0;"";INDEX(F$1:F$6;MAX(ALS(AANTAL.ALS(VERSCHUIVING(A$1;RIJ($1:$6)-1;;;5);H1)>0;RIJ($1:$6);0))[B]-1[/B];))

Moet de -1 er uit gehaald worden. dan klopt de formule wel.

Daarbij komt dat de formule van HSV een stuk simpeler en korter is. Zit ik te kl*ten met verschuiving en rij($1:$6) terwijl dit geheel niet nodig is
Soms denk je veel te moeilijk. HSV bedankt voor de correctie.:thumb:

 

[HSV]

:Soms denk je veel te moeilijk. HSV bedankt voor de correctie.:thumb:

Graag gedaan Paul,

Soms heb je van die dagen........

 

[Yokkie]

Jullie maken het wel spannend. Na een paar uur in beide formules van alles in lopen aanpassen en ontleden, krijg ik maar niet het gewenste resultaat. Ik geef weer op...

Met de formule van Paul krijg ik alleen maar Atjes in elke cel. Simpele verschuivingen snap ik nog, maar raak de draad kwijt als de matrix zich in andere cellen en kolommen bevinden. Volgens mij moet ik iets in 'waarde-als-waar nog doen'.....of zo..... De 2de formule doet helemaal niks als de matrix elders staat.

Dusss....iemand die het licht wel ziet?

 

[HSV]

Hierbij het licht Yokkie,

Bij Paul’s formule heb ik de -1 veranderd in -25, en de verschuiving(E26; in verschuiving(E25;

Bij die van mij eigenlijk niets anders dan de -25 in de formule.

Vergelijk jou bestand met deze, en je komt er wel achter denk ik.
Succes.

 

[Yokkie]

Ik doe even mijn zonnebril op....zo dat ziet er mooi uit! Ik had overal al eens 25 er van afgehaald, behalve dat van het laatste gedeelte. Haha.

Mijn dank is groot:thumb:. Ga ik er vandaag de boel maar eens mee afmaken. Moet nu vast wel lukken.