"Vert.zoeken" in meerdere kolommen naar een waarde, kan dit sneller?

[Joe_nl]

Goedemorgen,

Zie graag het bijgevoegde voorbeeld:


Het gaat om de formule in kolom AA, deze is op dit moment als volgt:

=ALS.FOUT(INDEX(B:B;
ALS.FOUT(VERGELIJKEN(Z5;E:E;0);
ALS.FOUT(VERGELIJKEN(Z5;D:D;0);
ALS.FOUT(VERGELIJKEN(Z5;F:F;0);
ALS.FOUT(VERGELIJKEN(Z5;G:G;0);
ALS.FOUT(VERGELIJKEN(Z5;H:H;0);
ALS.FOUT(VERGELIJKEN(Z5;I:I;0);
ALS.FOUT(VERGELIJKEN(Z5;J:J;0);
ALS.FOUT(VERGELIJKEN(Z5;K:K;0);
ALS.FOUT(VERGELIJKEN(Z5;L:L;0);
ALS.FOUT(VERGELIJKEN(Z5;M:M;0);
ALS.FOUT(VERGELIJKEN(Z5;N:N;0);
ALS.FOUT(VERGELIJKEN(Z5;O:O;0);
ALS.FOUT(VERGELIJKEN(Z5;P:P;0);
ALS.FOUT(VERGELIJKEN(Z5;Q:Q;0);
ALS.FOUT(VERGELIJKEN(Z5;R:R;0);
ALS.FOUT(VERGELIJKEN(Z5;S:S;0);
ALS.FOUT(VERGELIJKEN(Z5;T:T;0);
ALS.FOUT(VERGELIJKEN(Z5;U:U;0);
ALS.FOUT(VERGELIJKEN(Z5;V:V;0);
ALS.FOUT(VERGELIJKEN(Z5;W:W;0);""))))))))))))))))))))-1);"")

Het is de bedoeling dat gaandeweg de week de kolommen D t/m W worden gevuld met 'RIT 1', 'RIT 2', enz. en dat kolom AA aan de hand hiervan automatisch gevuld wordt.
Met VERT.ZOEKEN of VERGELIJKEN lukt het mij alleen om in 1 kolom te zoeken, niet in meerdere kolommen.

De formule die ik gebruik is nogal omslachtig zoals ziet, maar het werkt. Mijn vraag is dan ook: kan dit sneller en netter?

Ik hoor het graag!

 

[OctaFish]

Doe ons een lol, en doe het bestandje er bij i.p.v. een afbeelding. Ik steek liever tijd in een oplossing, dan dat ik allerlei data moet gaan overkloppen. Gaat namelijk allemaal van jouw tijd af .

 

[Joe_nl]

Ik hoopte dat het antwoord simpel was (omdat ik een functie niet ken / over het hoofd zie / verkeerd gebruik) en dit niet nodig zou zijn, maar bij deze:

Bekijk bijlage Helpmij_vert.zoeken_in_meerdere_kolommen.xlsx

Alvast bedankt voor het meedenken!

 

[arrie23]

In cel AA3:

=ALS.FOUT(INDEX($B$1:$B$27;SOMPRODUCT(--($D$3:$W$26=$Z3)*RIJ($D$3:$W$26))-1;1);"")

 

[Joe_nl]

Hartelijk dank Arrie, dit werkt super!

Ik was hier zelf nooit opgekomen, is deze formule uit te leggen?

 

[arrie23]

Ik zal een poging wagen:

SOMPRODUCT(--($D$3:$W$26=$Z3)*RIJ($D$3:$W$26))-1
Dit deel van de formule bekijkt in welke rij de gezochte waarde (Rit1, Rit2 etc) voorkomt:
Het deel (--($D$3:$W$26=$Z3) genereert een 1 in de matrix $D$3:$W$26 waar de waarde 'Rit1' voorkomt, voor de rest bevat de matrix alleeen maar nullen.
Als je deze matrix met allemaal nullen en die ene 1 via de SOMPRODUCT-functie vemenigvuldigt met de matrix RIJ($D$3:$W$26) (= 3/4/5/6/7 etc.) krijg je de rij waarin die ene 1 binnen de matrix staat (gerekend vanaf rij 1 van het tabblad).

SOMPRODUCT(--($D$3:$W$26=$Z3)*RIJ($D$3:$W$26)) = voor Rit1 gelijk aan 1*8 = 8 (ofwel Rit1 staat in rij 8 van het tabblad, gerekend vanaf rij 1)

Omdat het bijbehorende kenteken 1 rij hoger staat trek ik er nog 1 vanaf: 1*8-1 = 7. Het bijbehorende kenteken bij Rit1 staat dus in rij 7.

INDEX($B$1:$B$27;SOMPRODUCT(--($D$3:$W$26=$Z3)*RIJ($D$3:$W$26))-1;1) levert dus INDEX($B$1:$B$27;7;1) is dus de waarde van rij 7 in kolom B = Kenteken3

De ALS.FOUT (had je ook in je oorspronkelijke formule) staat eromheen indien een bepaalde rit niet voorkomt (zoals Rit5). De formule zou zonder ALS.FOUT een foutmelding geven en die wordt nu dus vervangen door "" ofwel een lege cel.

 

[Joe_nl]

Bedankt voor je antwoord Arrie!

 

[Joe_nl]

Goedemiddag, toch nog een vraag

Er is iets veranderd in de opzet waardoor elke regel nu een kenteken bevat. Op dezelfde regel komt dan ook nu de ritnaam. De -1 is dan ook niet meer noodzakelijk, maar als je dan de INDEX() gebruikt en de waarde wordt niet gevonden, dan wordt het huidige regelnummer gebruikt en komt er dus toch een kenteken in de cel, ondanks de waarde niet bestaat.

Ik heb dit nu als volgt opgelost, maar dit is niet zo netjes:

=ALS(SOMPRODUCT(--($E$1:$Y$200=AD13)*RIJ($E$1:$Y$200))=0;"";INDEX($B$1:$B$200;SOMPRODUCT(--($E$1:$Y$200=AD13)*RIJ($E$1:$Y$200))))

Kan dit korter?

 

[sylvester-ponte]

weer zonder bestandje.

 

[Joe_nl]

Die zal ik morgen even maken

 

[sylvester-ponte]

misschien kun je hier iets mee:

 

[Joe_nl]

Dit is perfect Sylvester, dank!!

Wat doen de { } haakjes precies? Als ik het goed zie is dit het enige verschil (behalve het weghalen van de -1) toch?

Ik zie dat je in B1 ook =1/0 hebt gezet, daar heb ik echter een waarde staan welk nu dus wordt weergegeven als resultaat als een waarde niet bestaat. Ik ga een voorbeeldbestand in elkaar zetten!

Bij deze: Bekijk bijlage Helpmij_vert.zoeken_in_meerdere_kolommen 3.xlsx

 

[sylvester-ponte]

al die eentjes krijg je weg door in B1 iets fouts tezetten bvb 1/0 .
ik vind jouw formule ook mooi. hij kan iets korter . maar dat maakt niet veel uit.