Excel formule ALS & meerdere VERT.ZOEKEN

[sanderarnouts]

Goedemiddag,

Ik ben bezig met een formule schrijven waar 4 verschillende afsplitsingen in zijn, waar dan weer maatregelen aan vast zitten aan het materiaal.

Mijn vraag was of iemand hier een formule voor heeft, het gaat als volgt:

in cel G staat de variabele (A,E,C en B )
Daar zijn 4 verschillende tabellen voor, dus 4 verschillende VERT.ZOEKEN

In die tabel moet die dus kijken naar de VTYP (A,E,C en B ), de schaden, en het materiaal (Zie afbeelding 1)



Vervolgens moet hij dus gaan zoeken naar de betreffende maatregel in de tabel (Zie afbeelding 2)



Iemand een idee of een stapje voor mij in de goede richting?

Alvast dank,
Sander

[KabouterSuper]

Waar kom je niet uit? De titel van je topic is het antwoord, toch?

[dix-neuf]

En waar zoek je naar met vert.zoeken?
Naar een letter (A,B, C, E), naar een schade, of wat anders?

[ERMAWG]

Er is denk ik een manier maar een beetje quick & dirty: steeds een nieuwe ALS functie starten in de niet-waar kant van de formule.
Dus je begint met ALS(G1="A";[Vert.Zoeken in de juiste tabel]; [hier komt de 'niet-waar' uitkomst])
en dan steeds een nieuwe ALS functie starten.

Dus zoiets: ALS(G1="A";[zoeken in tabel 1]; ALS(G1="B";[zoeken in tabel 2]; ALS(G1="C"; [zoeken in tabel 3]; ALS(G1="D"; [zoeken in tabel 4]; 'niet gevonden'))))

Mocht je dat onoverzichtelijk vinden kun je ook 4 nieuwe kolommen toevoegen (bijvoorbeeld H/I/J/K) met allemaal een eigen ALS functie met de juiste zoekopdracht in de 'waar' kant. De 'niet waar' kant laat je dan leeg.
Kolom H: ALS(G1="A"; [Zoekopdracht tabel 1];"")
Kolom I: ALS(G1="B"; [Zoekopdracht tabel 2];"")
etc.
In een vijfde kolom kun je dan de resultaten laten zien door te checken of de cel leeg is of gewoon =H1&I1&J1&K1 te doen. Dan voor de netheid kolommen H/I/J/K verbergen.

[ Voor 35% gewijzigd door ERMAWG op 06-07-2022 13:01 ]

[sanderarnouts]

ERMAWG schreef op woensdag 6 juli 2022 @ 12:44:
Er is denk ik een manier maar een beetje quick & dirty: steeds een nieuwe ALS functie starten in de niet-waar kant van de formule.
Dus je begint met ALS(G1="A";[Vert.Zoeken in de juiste tabel]; [hier komt de 'niet-waar' uitkomst])
en dan steeds een nieuwe ALS functie starten.

Dus zoiets: ALS(G1="A";[zoeken in tabel 1]; ALS(G1="B";[zoeken in tabel 2]; ALS(G1="C"; [zoeken in tabel 3]; ALS(G1="D"; [zoeken in tabel 4]; 'niet gevonden'))))

Mocht je dat onoverzichtelijk vinden kun je ook 4 nieuwe kolommen toevoegen (bijvoorbeeld H/I/J/K) met allemaal een eigen ALS functie met de juiste zoekopdracht in de 'waar' kant. De 'niet waar' kant laat je dan leeg.
Kolom H: ALS(G1="A"; [Zoekopdracht tabel 1];"")
Kolom I: ALS(G1="B"; [Zoekopdracht tabel 2];"")
etc.
In een vijfde kolom kun je dan de resultaten laten zien door te checken of de cel leeg is of gewoon =H1&I1&J1&K1 te doen. Dan voor de netheid kolommen H/I/J/K verbergen.

Ja dat had ik al wel bedacht, alleen mijn vraag was dat je dan ook nog onderscheid maakt in de informatie van cel E & F.

[ERMAWG]

Oh zo. Nee dan kun je beter een verborgen kolom maken waarin je de drie termen combineert tot 1 zoekterm. Zowel in je moedertabel als in de andere tabellen waarin je zoekt.
Dus bijvoorbeeld kolom H =E1&F1&G1 en dan in kolom G: Vert.Zoeken(H1....)

Je zoekterm in je eerste regel is dan: "BSS Keiformaat 80 mmBermenE"

Maar dat samenvoegen van de zoekterm moet je dus ook doen in je andere tabellen. Het risico is dat als iets een extra spatie heeft na het woord, dan wordt je zoekterm niet gevonden.

Je zou kunnen overwegen een andere aanpak te kiezen door elke individuele mogelijkheid een nummer te geven in een nieuwe tabel. Dan heen-en-weer zoeken via die nieuwe tabel. Waarom voeg je sowieso de vier tabellen niet samen?

[sanderarnouts]

Nou het zit dus zegmaar zo dat bij alle verschillende VTYP's (A,C,E,B) een aparte tabel hoort met aparte schades met aparte maatregelen met apart materiaal, vandaar dat het nog complexer wordt.

Zie afbeelding voor duidelijkheid

[Audiowaste]

Dat kun je makkelijk doen met de INDEX en VERGELIJKEN functie.

Je wil bijvoorbeeld weten welke maatregel je moet nemen als
VTYP = A
SCHADE = Boomwortelopdruk
MATERIAAL = bitumeus

Dan gebruik je de volgende formule (op basis van je screenshot in je laatste post):
=INDEX(T2:T20;VERGELIJKEN(1;(A=A2:A20)*(Boomwortelopdruk=B2:B20)*(bitumeus=C2:C20);0))

Waarbij:
Dikgedrukte de variabele waarden zijn waar je naar wil zoeken. Indien deze in een cel staan, dan vervang je de formule uiteraard door de cel.

Hier ook een snel voorbeeld in Excel:

[ Voor 26% gewijzigd door Audiowaste op 06-07-2022 14:39 . Reden: Voorbeeld van Excel toegevoegd ]

[Croga]

Eenvoudigste oplossing?
In beide sheets een hulpkolom maken die de betreffende kolommen aan elkaar plakt, die verbergen en die vervolgens gebruiken om te zoeken.
Alsnog wel met behulp van INDEX aangezien VLOOKUP een sorteringsvereiste heeft.

[Audiowaste]

Croga schreef op woensdag 6 juli 2022 @ 14:41:
Eenvoudigste oplossing?
In beide sheets een hulpkolom maken die de betreffende kolommen aan elkaar plakt, die verbergen en die vervolgens gebruiken om te zoeken.
Alsnog wel met behulp van INDEX aangezien VLOOKUP een sorteringsvereiste heeft.

Als je gebruik maakt van INDEX en VERGELIJKEN hoef je geen hulpkolom toe te voegen.

[Lustucru]

ik quote, mijn eigen quote, maar:

Lustucru schreef op vrijdag 1 mei 2020 @ 15:57:
Kijk hier eens:
Vertical zoeken met meerdere voorwaarden
Vertikaal zoeken met meerdere criteria en veranderende


De vraag komt vaker langs.

[Teun_2]

Audiowaste schreef op woensdag 6 juli 2022 @ 14:35:
Dat kun je makkelijk doen met de INDEX en VERGELIJKEN functie.

Je wil bijvoorbeeld weten welke maatregel je moet nemen als
VTYP = A
SCHADE = Boomwortelopdruk
MATERIAAL = bitumeus

Dan gebruik je de volgende formule (op basis van je screenshot in je laatste post):
=INDEX(T2:T20;VERGELIJKEN(1;(A=A2:A20)*(Boomwortelopdruk=B2:B20)*(bitumeus=C2:C20);0))

Waarbij:
Dikgedrukte de variabele waarden zijn waar je naar wil zoeken. Indien deze in een cel staan, dan vervang je de formule uiteraard door de cel.

Hier ook een snel voorbeeld in Excel:
[Afbeelding]

Dit, wel even de formule CTRL+SHIFT+ENTEREN afhankelijk van welke versie van excel je hebt.


Op jouw dataset wordt dit dan het volgende:
Ik ga er even vanuit dat je je tabellen een naam hebt gegeven

Methode 1:

{=INDEX(Opzoek[maatregel];VERGELIJKEN(1;([@[VTYP]]=Opzoek[VTYP])*([@[Schade]]=Opzoek[Schade])*([@[Materiaal]]=Opzoek[Materiaal]);0))}

De { } krijg je door ctrl+shift+enter. Je forceert zo de functies om arrays te verwerken.

Meer info: https://exceljet.net/form...ch-with-multiple-criteria

Methode 2: je maakt een sleutelkolom in beide tabellen die je telkens samenstelt uit VTYP&Materiaal&Schade en gebruikt daar vertikaal zoeken (of X.ZOEKEN: werk sneller en handiger dan vertikaal zoeken, je moet alleen een nieuwe versie van excel hebben)