Verticaal zoeken met dubbele waarden

[juuulv3]

Mijn vraag
Ik probeer data te koppelen met een zoekcriteria die meermaals voorkomt. Dit lukt deels met verticaal zoeken, echter krijg ik bij de resultaten enkel de eerste gevonden waarde en niet de daarop volgende waarde. Is er een mogelijkheid doormiddel van een formule dit te omzeilen?


Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Helaas geen werkende kunnen vinden. Ik kan ook helaas het bestand niet koppelen aan de topic. Hopelijk hebben jullie antwoordt met deze enigszins beperkte omschrijving/vraag

[ Voor 13% gewijzigd door juuulv3 op 21-11-2022 12:34 ]

[loeberce]

Wanneer voldoet de eerst gevonden waarde niet en waarom niet? En is er een mogelijkheid dat je niet de 2e gevonden waardem, maar de derde (of nog hoger) moet hebben?

[Sethro]

Een cel kan maar 1 waarde bevatten en dus niet 3 als het 3x voorkomt. Wel zou je de 3 kunnen optellen of op andere manier samenvoegen. Als je ze alle 3 wilt tonen heb je 3 cellen nodig met 3 formules.

Dus wat wil je precies?

[ Voor 6% gewijzigd door Sethro op 21-11-2022 12:41 ]

[Dido]

De hamvraag: als er 3 regels voldoen aan je criterium, welke van de drie wil je dan hebben?

Zonder antwoord op die vraag ga je geen oplossing vinden.

Als je de vraag beantwoord hebt,heb je waarschijnlijk ook je probleem opgelost.

Andere mogelijkheid:
Als je meerdere resultaten terug wilt krijgen uit een functie die gemaakt is om 1 resultaat terug te geven, gebruik je de verkeerde functie (wellicht wil je dan iets met filters doen).

[juuulv3]

Sheet 1

dateReferntie wat ik ophaal doormiddel van vert.zoeken

Hopelijk help dit om het te visualiseren.

[ Voor 8% gewijzigd door juuulv3 op 21-11-2022 12:43 ]

[Dido]

Wat in je kolom "wat ik wil" staat, staat letterlijk in je tab "dataReferentie". Je voorbeeld voegt dus geen bruikbare informatie toe, want je stelt nu dat je een kopie wilt van je data (en dat is heel simpel)

[juuulv3]

De date uit de dateReferentie roep ik op doormiddel van verticaal zoeken, met het zoekcriteria 'Dier'. Nu vindt hij enkel bij zoekcriteria 'aap' enkel de eerste waarde '2' maar niet de daaropvolgende data. Dus enkel de eerste waarde, dit gebeurt ook bij 'Ezel', waarde '8'. Maar als je in de dataReferentie kijkt zie je dat 'Aap' en 'Ezel' meerde waardes heeft.

[ Voor 13% gewijzigd door juuulv3 op 21-11-2022 12:49 ]

[Sethro]

Als dit is wat je wilt pak dan gewoon een draaitabel / pivot table

[juuulv3]

Ik werk met een datadump en hierin wil/kan ik de data niet bewerken, enkel oproepen via deze sheet. Dit is een voorbeeld sheet, de daadwerkelijke data sheet bevat 1000+ regels.

[superduper]

Dit is weer een voorbeeld van excel gebruiken als database tool. Werkt tot op zeker hoogte aardig, maar wat je nu wilt kan echt niet (goed) in excel. Dat wordt zelf scripten of met een databaseje gaan werken.

[dix-neuf]

Typ in B3 van Blad1 en bevestig met Ctrl-Shift-Enter:

code:

=ALS.FOUT(INDEX(Datareferentie!B$2:B$7;KLEINSTE(ALS(Datareferentie!A$2:A$7=A$2;RIJ(B$2:B$7)-RIJ(B$2)+1);RIJ(A1)));"")

Kopieer naar beneden t/m B4.

Typ in B6 van Blad1 en bevestig met Ctrl-Shift-Enter:

code:

=ALS.FOUT(INDEX(Datareferentie!B$2:B$7;KLEINSTE(ALS(Datareferentie!A$2:A$7=A$5;RIJ(B$2:B$7)-RIJ(B$2)+1);RIJ(A1)));"")

Kopieer naar beneden t/m B7.

[ Voor 6% gewijzigd door dix-neuf op 21-11-2022 14:14 ]

[Sethro]

juuulv3 schreef op maandag 21 november 2022 @ 12:52:
Ik werk met een datadump en hierin wil/kan ik de data niet bewerken, enkel oproepen via deze sheet. Dit is een voorbeeld sheet, de daadwerkelijke data sheet bevat 1000+ regels.

Dan kun je toch nog steeds met draaitabel werken? Nieuwe data erin, draaitabel verversen en weer door.

[Dido]

juuulv3 schreef op maandag 21 november 2022 @ 12:52:
Ik werk met een datadump en hierin wil/kan ik de data niet bewerken, enkel oproepen via deze sheet. Dit is een voorbeeld sheet, de daadwerkelijke data sheet bevat 1000+ regels.

Op het blad dataReferentie gaan staan, ctrl-A doen, op blad 1 gaan staan in A1, ctrl-V?

Je maakt gewoon een kopie van je data, en dat wil je via ingewikkelde formules doen.
Waarom je dat niet simpel wilt doen is nog steeds niet duidelijk.

Sethro schreef op maandag 21 november 2022 @ 12:50:
Als dit is wat je wilt pak dan gewoon een draaitabel / pivot table

Worden draaitabellen de nieuwe regular expressions?

Waarom zou je een draaitabel gebruiken voor een simpel kopietje van wat data? Want dat is het enige dat gewenst is, als ik het voorbeeld zie.

[ Voor 26% gewijzigd door Dido op 21-11-2022 14:09 ]

[dix-neuf]

In mijn vorig bericht schreef ik dat de 2 formules moeten worden ingevoerd in B2 en B5 van Blad1.
Dat moet zijn: in B3 en B6 van Blad1. Ik paste het bericht hierboven daarop aan.

[Reinier]

Lees nou wat @Dido zegt, daar heb je je antwoord al. Je doet werkelijk NIETS met de brondata. Dus copy/paste voldoet.
Daarnaast een tip: als je uitleg wil, maak dan geen spelfouten in je voorbeeld. dateReferntie?

[Dido]

dix-neuf schreef op maandag 21 november 2022 @ 14:16:
In mijn vorig bericht schreef ik dat de 2 formules moeten worden ingevoerd in B2 en B5 van Blad1.
Dat moet zijn: in B3 en B6 van Blad1. Ik paste het bericht hierboven daarop aan.

Is het alsnog een heel stuk simpeler om in A3 te zetten =dataRefentie!A1 en in B3 =dataReferentie!B1
Dan kun je die gewoon naar beneden kopieren en hoef je niet bij elke dierwissel je formules aan te passen

[dix-neuf]

Tja, in het voorbeeld staan dezelfde diernamen bij elkaar, in werkelijkheid zal dat niet zo zijn denk ik.
Het aanpassen van de formule (bij elk ander dier) is nog wel te vermijden denk ik (zou het even moeten bekijken), maar de formule wordt dan wel een stuk langer.

[juuulv3]

@dix-neuf Top de formule werkt als een trein, dank voor het tijd nemen. Het ligt zoals je aangeeft complexer en veelal staat de data door elkaar heen. Ik wil de data oproepen doormiddel van gegevensvalidatie. zie onderstaand.. als ik in F4 de zoekcriteria verander van Aap naar Ezel vindt de formule de waardes wel van ezel maar zet hij dit bij Aap neer.. is dit te tackelen, want dan ben ik er volgens mij.





En de gebruikers van deze opzet zijn minder digivaardig dan gemiddeld.. Hoop nog een reactie van je te ontvangen, maar voor nu al dank!

[ Voor 62% gewijzigd door juuulv3 op 21-11-2022 16:02 ]

[Dido]

Zolang je zo vaag blijft in je probleemstelling zou ik het wonderbaarlijk vinden als iemand je verder kan helpen...

Wat werkt er nou niet aan een copy/paste?

1000+ rijen is drie keer niets om even te kopieren, dus wat maakt nou dat je naar een ingewikkelde oplossing zoekt die vooralsnog nergen voor nodig is?

[Ursamajor]

Zoek je niet een SPILL functie: FILTER function - Microsoft Support

[dix-neuf]

juuulv3,
Je laatste vraag is niet helemaal logisch:
Je wil in Blad1 de gegevens laten weergeven van het dier dat je in F4 via gegevensvalidatie kiest. Dat is 1 dier, dus dan mogen alleen de gegevens van dát dier worden weergegeven en niet van een ander dier. Als je 'aap', respectievelijk 'ezel' kiest, dan moet de weergave mijn inziens zijn zoals in de figuren 1 en 2 hieronder. Jouw blad 1 wekt de indruk alsof je álle dieren die in 'Datareferentie' voorkomen, wil weergeven en ook nog een keuze wil maken in F4 van Blad1, dat is tegenstrijdig.
Voor de formule in B3 in de twee tabellen hieronder (dat zijn dezelfde tabellen, alleen met een verschillende keuze in F4), kun je gebruiken (invoeren via Ctrl-Shift-Enter):

code:

=ALS(A3=$F$4;ALS.FOUT(INDEX(Datareferentie!B$2:B$7;KLEINSTE(ALS(Datareferentie!A$2:A$7=$F$4;RIJ(B$3:B$8)-RIJ(B$3)+1);AANTAL.ALS($A$3:$A3;A3)));"");"")

Die formule kun je met de vulgreep naar beneden kopiëren t/m B8.





Je hebt in Blad1 in kolom A 3 x aap en 3 x ezel ingevuld, maar in een groot bestand zul je waarschijnlijk vooraf de aantallen niet weten van de verschillende dieren. Je kunt daarom beter (lijkt mij) de validatiekeuze plaatsen zoals in de figuur hieronder, je kunt dan de formule in B3 zo ver 'doortrekken' als je wil, je hebt dan altijd voldoende cellen ter beschikking zonder dat je het aantal dieren hoeft te weten. Voor de formule in B3 in de twee tabellen hieronder (dat is dezelfde tabel, alleen met een verschillende keuze in B2) kun je gebruiken (invoeren via Ctrl-Shift-Enter):

code:

=ALS.FOUT(INDEX(Datareferentie!B$2:B$7;KLEINSTE(ALS(Datareferentie!A$2:A$7=$B$2;RIJ(A$3:A$8)-RIJ(B$3)+1);RIJ(A1)));"")

De formule kun je naar beneden kopiëren tot zo ver als nodig (= het maximale aantal dat van een dier kan voorkomen).





NB: Overweeg ook eens de antwoorden van anderen hierboven!

[ Voor 4% gewijzigd door dix-neuf op 22-11-2022 07:04 ]