[EXCEL] Meerdere resultaten INDEX en VERGELIJKEN

[TWDER]

Ik loop tegen een probleem aan waar ik niet uit kom. Ik zoek al een tijdje naar een manier om meerdere resultaten op te zoeken dmv Vertikaal zoeken of INDEX/VERGELIJKEN.

Ik wil voor het nummer 888888 alle waardes in de eerste kolom weergeven (zie voorbeeld). Hoe fix ik dit?

[Atanamir]

Interessante vraag!

Ik ben hier ook wel eens tegenaan gelopen en ben benieuwd of er nog slimme methodes zijn om dit relatief eenvoudig op te lossen. (zonder 'valsspelen' met VBA, aangezien dat niet altijd mag)

Uiteindelijk had ik het met wat complexe formules opgelost, alleen dat ging over een situatie waarin de zoektermen slechts in 1 kolom konden staan. Dus indien jouw nummer 888888 in alle kolommen kan staan gaat dit niet werken.

In ieder geval voor de kennis deling toch even mijn oplossing hieronder.

[Lustucru]

De volgende matrixformule lost dit soort problemen op. Als voorbeeld een manier om de alle waarden (tenminste, als je de matrix waarin je hem invult groot genoeg) uit kolom c weer te geven waar in kolom a een a staat. Om de foutmeldingen in de opvolgende regels te vermijden kun je er nog een 'als.fout()' omheen gooien.
De 999 is niet belangrijk, zolang die waarde maar groter is dan de grootste rij waarin een gevonden waarde kan voorkomen.

code:

=INDEX($C$1:$C$6;KLEINSTE(ALS(($A$1:$A$6)="a";RIJ($A$1:$A$6);999);RIJ()))

[TWDER]

Atanamir schreef op donderdag 22 juni 2017 @ 10:24:
Interessante vraag!

Ik ben hier ook wel eens tegenaan gelopen en ben benieuwd of er nog slimme methodes zijn om dit relatief eenvoudig op te lossen. (zonder 'valsspelen' met VBA, aangezien dat niet altijd mag)

Uiteindelijk had ik het met wat complexe formules opgelost, alleen dat ging over een situatie waarin de zoektermen slechts in 1 kolom konden staan. Dus indien jouw nummer 888888 in alle kolommen kan staan gaat dit niet werken.

In ieder geval voor de kennis deling toch even mijn oplossing hieronder.

[afbeelding]

Sick. Ik krijg hoofdpijn van jouw complexe formules . Snap er niks van.

Lustucru schreef op donderdag 22 juni 2017 @ 12:05:
De volgende matrixformule lost dit soort problemen op. Als voorbeeld een manier om de alle waarden (tenminste, als je de matrix waarin je hem invult groot genoeg) uit kolom c weer te geven waar in kolom a een a staat. Om de foutmeldingen in de opvolgende regels te vermijden kun je er nog een 'als.fout()' omheen gooien.
De 999 is niet belangrijk, zolang die waarde maar groter is dan de grootste rij waarin een gevonden waarde kan voorkomen.

code:

=INDEX($C$1:$C$6;KLEINSTE(ALS(($A$1:$A$6)="a";RIJ($A$1:$A$6);999);RIJ()))

Dit ziet er mooi uit! Alleen heb ik er even mee lopen spelen en krijg ik hem in mijn Sheet niet correct ingevoerd. Hoe moet ik hem precies invullen? Welk bereik moet ik waar invullen?

[jjust]

De formule die voorgesteld wordt werkt volgens mij alleen als je data op rij 1 begint. Dit omdat de laatste rij in de formule aangeeft welk n^th kleinste de formule teruggeeft. Dit moet beginnen met 1 omdat dit de eerste rij is met een match. Dat werkt dus alleen als je op rij 1 begint. Als je op rij 6 begint, krijg je de 6de kleinste terug en die is er niet aangezien er maar 5 matches zijn.

Verder kan je de formule gewoon maar beneden slepen / kopiëren totdat je een #ref terug krijgt. Let op dat je de formule niet met een enter afsluit maar met ctrl shift enter om er een matrix formule van te maken. Er worden dan van die curly braces om de formule gezet.
(klikbaar wel de engelse excel)



Als je het echt netjes wilt maken kan je zoals Lustucru zegt werken met is.fout.

[TWDER]

Bedankt voor je toelichting. Ik krijg hem echter nog steeds niet helemaal werkend....
Wat doe ik fout?




Dit is trouwens maar een voorbeeld bestand. De daadwerkelijke data bestaat uit 50.000 + regels en +- 15 kolommen.

[ Voor 21% gewijzigd door TWDER op 26-06-2017 12:50 ]

[Atanamir]

Dank @Lustucru voor de formule, een stuk eenvoudiger dan mijn oplossing eerder!

@TWDER - in jouw formule hierboven gebruik je zo te zien OF, terwijl je ALS moet gebruiken.

Daarnaast hoef je de formule natuurlijk niet perse op de eerste rij te gebruiken, alleen moet je in dat geval wel even de references aanpassen.
De laatste "RIJ()" in de formule verwijst bijvoorbeeld naar de verschillende matches in de range, waarbij dit dus alleen werkt als je deze formule op rij 1 laat beginnen. Als je deze vervangt door RIJ(1:100) zul je match #1 t/m match #100 krijgen, ongeacht waar je de formule neerzet.

Als je zoekrange ook niet op rij 1 begint zul je dit stukje als laatste ook nog even moeten veranderen om te zorgen dat de eerste match wel het goede rijnummer teruggeeft.

[TWDER]

Atanamir schreef op maandag 26 juni 2017 @ 13:35:
Dank @Lustucru voor de formule, een stuk eenvoudiger dan mijn oplossing eerder!

@TWDER - in jouw formule hierboven gebruik je zo te zien OF, terwijl je ALS moet gebruiken.

Daarnaast hoef je de formule natuurlijk niet perse op de eerste rij te gebruiken, alleen moet je in dat geval wel even de references aanpassen.
De laatste "RIJ()" in de formule verwijst bijvoorbeeld naar de verschillende matches in de range, waarbij dit dus alleen werkt als je deze formule op rij 1 laat beginnen. Als je deze vervangt door RIJ(1:100) zul je match #1 t/m match #100 krijgen, ongeacht waar je de formule neerzet.

Als je zoekrange ook niet op rij 1 begint zul je dit stukje als laatste ook nog even moeten veranderen om te zorgen dat de eerste match wel het goede rijnummer teruggeeft.

Het is gelukt als ik op rij 1 begin .

Alleen snap ik nog niet helemaal hoe ik hem werkend krijg als ik bijv op rij 5 begin. Komt ook omdat ik sws niet helemaal begrijp hoe de formule werkt... . Ik heb een beetje gespeeld met de laatste verwijzing in RIJ maar ik kom er niet uit. Bij welke waarde begint ie op welke rij?.. Het bronbestand waaruit ik ga werken begint op rij 3 btw.

[Atanamir]

Je formule (uitgaande van voorbeeld, niet van je echte bronbestand) begint op rij 5, dus moet je RIJ referentie daar rekening mee houden.

Ik heb geen Nederlandse Excel dus ik probeer het even zonder te testen in de formule hieronder aan te passen. In ieder geval moet je de $-tekens even uit het laatste stuk weghalen denk ik, en in het stuk ervoor even een aantal regels aftrekken zodat de range op 1 begint. (dus -4 als je dataset regel 5 begint)

code:

=INDEX($B$5:$B$23;KLEINSTE(ALS(($G$5:$G$23)=$J$3;RIJ($G$5:$G$23)-4;999);RIJ(1:100)))

En voor jouw begrip:
- Het laatste stukje in de formule (RIJ(1:100)) hoort bij KLEINSTE, en geeft aan welke sequentiële match er gepakt moet worden. Daarom werkte dit eerst niet als het niet op rij 1 stond. Row() geeft dan immers bijv. 5 terug, wat naar de 5e match binnen je dataset verwijst

[ Voor 21% gewijzigd door Atanamir op 26-06-2017 15:59 ]

[jjust]

Je moet inderdaad de $ teken uit de laatste RIJ(1:100) weglaten. Daarnaast moet je corrigeren voor de beginrij van je data. Dit kun je doen door de (eerste rij -1) van de uitkomst van kleinste af te halen. Als je dit niet doet is de uitkomst gelijk aan de rij die je zoekt plus het aantal rijen voordat de data begint.

Op het tabblad formules staat een functie "evaluate formula".(heb hier de engelse versie weet zo de NL naam niet). Deze functie is handig omdat je dan stap voor stap ziet wat er gebeurd. Zo kan je er mogelijk achter komen waar het in je formule mis gaat.

[TWDER]

Atanamir schreef op maandag 26 juni 2017 @ 15:51:
Je formule (uitgaande van voorbeeld, niet van je echte bronbestand) begint op rij 5, dus moet je RIJ referentie daar rekening mee houden.

Ik heb geen Nederlandse Excel dus ik probeer het even zonder te testen in de formule hieronder aan te passen. In ieder geval moet je de $-tekens even uit het laatste stuk weghalen denk ik, en in het stuk ervoor even een aantal regels aftrekken zodat de range op 1 begint. (dus -4 als je dataset regel 5 begint)

code:

=INDEX($B$5:$B$23;KLEINSTE(ALS(($G$5:$G$23)=$J$3;RIJ($G$5:$G$23)-4;999);RIJ(1:100)))

En voor jouw begrip:
- Het laatste stukje in de formule (RIJ(1:100)) hoort bij KLEINSTE, en geeft aan welke sequentiële match er gepakt moet worden. Daarom werkte dit eerst niet als het niet op rij 1 stond. Row() geeft dan immers bijv. 5 terug, wat naar de 5e match binnen je dataset verwijst

Je bent een held .Het werkt. Ook in het brondbestand. Ik moest alleen even de "999" vergroten naar 50000. Geniaal. Nog een als.fout eromheen hangen en hij is helemaal netjes.

Ik begrijp wat ik waar moet invullen om de juiste waardes te krijgen. Maar de formule zelf zorgt nog voor een beetje koppijn. Ik snap de functie van KLEINSTE niet helemaal. Wat DOET de formule nou precies?

[jjust]

De ALS-functie controleert per rij of de inhoud van de cell gelijk is aan J2. Als dat het geval is, komt uit de formule het rij nummer anders krijgt deze de waarde 999. Dus cellen G5 t/m G13 resulteren ieder in de waarde 999. G14 = J2 en krijgt de waarde van de rij dus 14. G15 geldt hetzelfde en levert dus 15 op. Dit geldt tot en met G18. De cellen daarna resulteren weer ieder in 999. Je krijgt dus uiteindelijk een array (omdat het een matrixformule is) van 999,999,.......,999,14,15,16,17,18,999 enz

Om de rijnummers uit deze array te halen wordt KLEINSTE gebruikt. KLEINSTE rangschikt de array van klein naar groot en geeft de n^th kleinste terug. Hiervoor wordt RIJ(1:100) gebruikt. In de eerste cel is RIJ(1:100) gelijk aan 1. Hierbij krijgt je dus de kleinste waarde in de array terug namelijk 14. Als je de formule door kopieërt (zoals je hier doet) wordt RIJ(1:100) eerst 2 dan 3 enz. Dus als je de formule doorkopieërt, krijg je de tweede keer niet de KLEINSTE naar de 2de kleinste de derde keer de 3de kleinste enz. dus dan krijg je rij 15, 16. enz enz...

Als je de formule evaluate gebruikt, zie je ook de hele array's terug in de onderliggende stappen.

[TWDER]

jjust schreef op dinsdag 27 juni 2017 @ 13:28:
De ALS-functie controleert per rij of de inhoud van de cell gelijk is aan J2. Als dat het geval is, komt uit de formule het rij nummer anders krijgt deze de waarde 999. Dus cellen G5 t/m G13 resulteren ieder in de waarde 999. G14 = J2 en krijgt de waarde van de rij dus 14. G15 geldt hetzelfde en levert dus 15 op. Dit geldt tot en met G18. De cellen daarna resulteren weer ieder in 999. Je krijgt dus uiteindelijk een array (omdat het een matrixformule is) van 999,999,.......,999,14,15,16,17,18,999 enz

Om de rijnummers uit deze array te halen wordt KLEINSTE gebruikt. KLEINSTE rangschikt de array van klein naar groot en geeft de n^th kleinste terug. Hiervoor wordt RIJ(1:100) gebruikt. In de eerste cel is RIJ(1:100) gelijk aan 1. Hierbij krijgt je dus de kleinste waarde in de array terug namelijk 14. Als je de formule door kopieërt (zoals je hier doet) wordt RIJ(1:100) eerst 2 dan 3 enz. Dus als je de formule doorkopieërt, krijg je de tweede keer niet de KLEINSTE naar de 2de kleinste de derde keer de 3de kleinste enz. dus dan krijg je rij 15, 16. enz enz...

Als je de formule evaluate gebruikt, zie je ook de hele array's terug in de onderliggende stappen.

Duidelijk . En met formule evalueren helemaal .Op deze manier hoeven dus de waarden in KOLOM G dus ook niet gesorteerd te staan. Belangrijk, want ik kan de brondata wel sorteren, maar het feit dat het niet hoeft geeft meer mogelijkheden (en hogere data integriteit).

[jeroenathome]

Ondanks dat het al even geleden is dat er een reactie is geplaatst zit ik met een vraag:

In de formule staat de functie RIJ(1:100) aan het eind. Dit heb ik nu ook, maar als ik de cellen met de formule naar beneden kopieer dan wijzigt deze in RIJ(2:101), RIJ(3:102) etc. etc.

Waarom is dit nodig en welke invloed heeft het op de rest van de formule?
Als er meer als 100 regels aan de voorwaarde voldoen. Moet ik dan het getal 100 aanpassen of is dat dan niet meer nodig doordat het met kopiëren al is aangepast?

[jjust]

jeroenathome schreef op vrijdag 22 december 2017 @ 21:23:
.......
In de formule staat de functie RIJ(1:100) aan het eind. Dit heb ik nu ook, maar als ik de cellen met de formule naar beneden kopieer dan wijzigt deze in RIJ(2:101), RIJ(3:102) etc. etc.

Waarom is dit nodig en welke invloed heeft het op de rest van de formule?

jjust schreef op dinsdag 27 juni 2017 @ 13:28:

Om de rijnummers uit deze array te halen wordt KLEINSTE gebruikt. KLEINSTE rangschikt de array van klein naar groot en geeft de n^th kleinste terug. Hiervoor wordt RIJ(1:100) gebruikt. In de eerste cel is RIJ(1:100) gelijk aan 1. Hierbij krijgt je dus de kleinste waarde in de array terug namelijk 14. Als je de formule door kopieërt (zoals je hier doet) wordt RIJ(1:100) eerst 2 dan 3 enz. Dus als je de formule doorkopieërt, krijg je de tweede keer niet de KLEINSTE naar de 2de kleinste de derde keer de 3de kleinste enz. dus dan krijg je rij 15, 16. enz enz...

Als je de formule evaluate gebruikt, zie je ook de hele array's terug in de onderliggende stappen.

Zie de quote. Die Rij(1:100) wordt gebruikt om door te tellen en dus de n^th kleinste steeds met 1 te verhogen. Rij(1:100) geeft 1 als je hem doorkopieërt krijg je Rij(2:101) die geeft 2 dan Rij(3:103) die geeft 3 etc. De eerste rij geeft de kleinste waarde, de 2de rij de 2de kleinste waarde, de 3de rij de 3de kleinste waarde etc.

jeroenathome schreef op vrijdag 22 december 2017 @ 21:23:
Als er meer als 100 regels aan de voorwaarde voldoen. Moet ik dan het getal 100 aanpassen of is dat dan niet meer nodig doordat het met kopiëren al is aangepast?

Nee dat is niet nodig. Het gaat om het eerste cijfer in Rij(1:100) die wordt bij het kopieren vanzelf verhoogd.

[Cees Swagerman]

Meerdere waarden vinden in het eerst getoonde excel-blad kan als volgt:

Plaats in J5 de formule: =ALS.FOUT(INDEX(B:B;K5);"")
Plaats in K5 de formule: =K4+VERGELIJKEN($J$3;VERSCHUIVING($G$1:$G$99;K4;0);0)
De formules kun je doorvoeren.
Kolom K kun je verbergen.

Succes

[Anoniem: 1150215]

Hallo,

Bedankt voor deze formule, maar wanneer ik deze gebruik krijg ik in de overige kolommen #getal!
Hoe kan ik voorkomen dat deze waarde weergegeven wordt?

[Anoniem: 1169466]

je hebt inmiddels óók -overigens terecht- een eigen topic aangemaakt. Kan hier de post wel weg en mag dit topic weer wegzakken in vergetelheid.

[ Voor 94% gewijzigd door Lustucru op 17-01-2019 09:53 ]

[RobEli]

Ik ben net begonnen met het gebruik van INDEX en VERGELIJKEN. Ik begrijp en onderstaande maar krijg toch foutmeldingen als ik de onderstaande formule gebruik en aanpas aan mijn situatie,

Het verschil is dat ik tekst gebruik in plaats van waarden in mijn werkblad.

Wanneer ik de formule kopieer naar mijn eigen werkblad, vervolgens aanpas en naar de overige cellen kopieer, dan krijg ik in de eerste cel en het goede antwoord maar de rest niet.

Kan iemand mij hierover uitleg geven wat de reden hiervan is?

[RobEli]

De formule die ik gebruik is:
=INDEX($H$5:$H$26;KLEINSTE(ALS(($G$5:$G$26)=$D$5;RIJ($G$5:$G$26)-4;999);RIJ(1:100)))

[RobEli]

De data is geplaatst in regel 5.
Het zoekgegeven staat in cel D5
De gegevens waarin gezocht moet worden staat in kolom G (G5:G26) en H (H5:H26)

[g0tanks]

RobEli schreef op zondag 15 november 2020 @ 13:11:
Ik ben net begonnen met het gebruik van INDEX en VERGELIJKEN. Ik begrijp en onderstaande maar krijg toch foutmeldingen als ik de onderstaande formule gebruik en aanpas aan mijn situatie,

Het verschil is dat ik tekst gebruik in plaats van waarden in mijn werkblad.

Wanneer ik de formule kopieer naar mijn eigen werkblad, vervolgens aanpas en naar de overige cellen kopieer, dan krijg ik in de eerste cel en het goede antwoord maar de rest niet.

Kan iemand mij hierover uitleg geven wat de reden hiervan is?

[Afbeelding]

Welkom op Tweakers.

Ik denk dat je dit stuk hebt gemist:

jjust schreef op vrijdag 23 juni 2017 @ 21:04:
Verder kan je de formule gewoon maar beneden slepen / kopiëren totdat je een #ref terug krijgt. Let op dat je de formule niet met een enter afsluit maar met ctrl shift enter om er een matrix formule van te maken. Er worden dan van die curly braces om de formule gezet.
(klikbaar wel de engelse excel)

Verder hebben we in het vervolg liever dat je een nieuw topic aanmaakt, waarin je natuurlijk alsnog kunt verwijzen naar een ander topic. Anders krijgen we één topic met een boel net andere situaties en daar wordt het onoverzichtelijk van. Dit topic doe ik nu op slot.