HULP met ALS functie

[Rabberzzz]

Zie bijlage.

Ik kom er niet uit met mijn onderstaande formule.

{=ALS(IS.EVEN(RIJ());KLEINSTE(ALS(L53:L59>D68;N53:N59);1);GROOTSTE(ALS(M53:M59<D68;N53:N59);1))}

D68 = wat onder d50 (mm) staat.
Graag wil ik een zoekfunctie toevoegen op basis van de input.
De input moet overeenkomen met een andere cel elders in het werkblad.
Namelijk ='Onderbouw Op Locatie'!E43 (hier staat h.o.h. 3 m of h.o.h. 4 m)

 

[AlexCEL]

Een voorbeeld Excel bestandje met daarin aangegeven wat je wilt verduidelijkt meer dan deze (voor mij) abracadabra.

 

[Rabberzzz]

Dag Alex,

Bedankt voor je bericht.
Zie bijlage voor een werkblad.
De input van E15 moet overeenkomen met H5:H11 en dan dan moet deze formule (in I17) zijn werking doen dat de waarde bij A20 wordt gevonden op de juiste rij van I5:J11 en dat dan de output K5:K11 wordt weergegeven.
Mijn beoogd doel is dat er een soort dubbele zoekfunctie of als functie is die dus zoekt op h.o.h. en dan op basis van de waarde die in een range valt van I5:J11 zodat hij mij de output geeft van K5:K11.

Hopelijk geeft dit voldoende uitleg anders hoor ik dit graag.

 

[emields]

de waarde van A20 kan niet worden gevonden want ze bestaat niet.

 

[Rabberzzz]

de waarde van A20 kan niet worden gevonden want ze bestaat niet.

Ik vraag me af wat je bedoeld met bestaat niet en kan niet worden gevonden?
Een kleine toelichting op de werking van de formule lees je hieronder, hopelijk is het meer duidelijk?

In I17 wordt de waarde gegeven op basis van de input van A20. Als je A20 veranderd naar 0.17 zul je zien dat de output bij I17 veranderd in 3.
Echter zijn er twee outputs waarin de 0.17 vallen. Eentje bij h.o.h. 4 m (de waarde 3 die wordt teruggegeven) en een bij h.o.h. 3 m (wat 5 is).
Nu is mijn beoogd doel dat de formule ziet dat de waarde moet teruggeven als het h.o.h. 4 m of h.o.h. 3 m is.

 

[emields]

zo dan?

 

[Rabberzzz]

zo dan?

Helaas werkt het niet naar behoren. Je insteek om het met een ALS formule 4 m en anders 3 m te laten zoeken vond ik wel slim bedacht maar het geeft niet de verwachte output.

Ik heb het excel bestand aangepast door de ene kolom 'laagste' te noemen en de andere 'hoogste' om de range meer te verduidelijken. Voor mij zelfsprekend maar het doel is dat de waarde in A20 tussen de laagste en hoogste valt, maar wel de juiste rij neemt op basis van h.o.h. 4 m of h.o.h. 3 m zodat de juiste output van K5:K11 wordt getoond.

Misschien een ALS, INDEX of Verticaal zoeken geeft een betere oplossing?

 

[Rabberzzz]

Bedankt voor de reacties!

De gewenste resultaat is bereikt met een index formule te vinden in de bijlage in cel I19 en hieronder te lezen.

{=INDEX(K5:K11;VERGELIJKEN(1;(A20>=I5:I11)*(A20<=J5:J11)*(E15=H5:H11);0))}

 

[AlexCEL]

Niet matrix-variant:

=INDEX(K:K;SOMPRODUCT((A20>=I5:I11)*(A20<=J5:J11)*(E15=H5:H11)*RIJ(5:11)))

 

[Rabberzzz]

Niet matrix-variant:

=INDEX(K:K;SOMPRODUCT((A20>=I5:I11)*(A20<=J5:J11)*(E15=H5:H11)*RIJ(5:11)))

Dit werkte niet zoals gehoopt. Dit omdat ik mijn eerder gevonden formule ook niet meer werkt wanneer ik de input van cellen of kolommen van verschillende tabbladen gebruik.
Vanwege bedrijfsgevoelige informatie wil ik niet het hele werkblad uploaden, en zoals eerder een aparte sheet aanmaken vond ik even te veel werk. Hopelijk werkt het middels de afbeelding en uitleg mbt de formule.

Dropdown! sheet is waar ik alle back end gegevens wil 'droppen', vandaar ik hiermee een koppeling wil maken.
G_Onderbouwlabtest is de input die moet gaan zoeken in die twee kolommen enerzijds groter dan het laagste getal en anderzijds lager dan het grootste getal.
Onderbouw Op Locatie'!E43 is de tweede input (namelijk de h.o.h. 3 m of 4 m)

=INDEX(Dropdown!D2:D8;SOMPRODUCT((G_OnderbouwLabtestM50>=Dropdown!B2:B8)*(G_OnderbouwLabtestM50<=Dropdown!C2:C8)*('Onderbouw Op Locatie'!E43=Dropdown!A2:A8)*RIJ(2:8)))

Voor de bovenstaande formule krijg ik de waarde #WAARDE! terug.

{=INDEX(Dropdown!D2:D8;VERGELIJKEN(1;(G_OnderbouwLabtestM50>=Dropdown!B2:B8)*(G_OnderbouwLabtestM50<=Dropdown!C2:C8)*('Onderbouw Op Locatie'!E43=Dropdown!A2:A8);0))}

Voor de bovenstaande formule krijg ik de waarde #WAARDE! terug.

OEPS! nu snap ik de foutmeldingen. Verschilletje van een hoofdletter in h.o.h. dit was H.o.h.
Maar ter informatie de formule zonder de matrix variant geeft mij alsnog een verkeerde uitkomst. h.o.h. 4 m met 0.29 geeft 3 weer maar moet 5 zijn.

 

[AlexCEL]

Zo wel gewenste resultaten?

=INDEX([COLOR="#FF0000"][B]Dropdown!D:D[/B][/COLOR];SOMPRODUCT((G_OnderbouwLabtestM50>=Dropdown!B2:B8)*(G_OnderbouwLabtestM50<=Dropdown!C2:C8)*('Onderbouw Op Locatie'!E43=Dropdown!A2:A8)*RIJ(2:8)))

 

[Rabberzzz]

Zo wel gewenste resultaten?

=INDEX([COLOR="#FF0000"][B]Dropdown!D:D[/B][/COLOR];SOMPRODUCT((G_OnderbouwLabtestM50>=Dropdown!B2:B8)*(G_OnderbouwLabtestM50<=Dropdown!C2:C8)*('Onderbouw Op Locatie'!E43=Dropdown!A2:A8)*RIJ(2:8)))

Dank! Zo wel! Wat is het verschil of voordeel van een niet matrix variant?

 

[AlexCEL]

Snelheid.... heel veel matrixfuncties werkt vertragend in een sheet.

 

[Rabberzzz]

Snelheid.... heel veel matrixfuncties werkt vertragend in een sheet.

Snelheid. Goed om te weten, thanks!

Met RIJ() kun je één specificeren neem ik aan maar je kunt het ook een volledige kolom van maken vermoed ik? Bijvoorbeeld RIJ(D) in dit geval?

 

[AlexCEL]

Ik snap niet precies wat je met de laatste vraag bedoelt, maar RIJ($2), RIJ(D2), RIJ(Y2) geven allemaal hetzelfde resultaat: 2.

De laatste vermenigvuldiging staat erin om het rij-nummer te krijgen van de tabelrij waar alle voorwaarden waar zijn. De INDEX zoekt 'm dan op in de kolom.

 

[Rabberzzz]

Ik snap niet precies wat je met de laatste vraag bedoelt, maar RIJ($2), RIJ(D2), RIJ(Y2) geven allemaal hetzelfde resultaat: 2.

De laatste vermenigvuldiging staat erin om het rij-nummer te krijgen van de tabelrij waar alle voorwaarden waar zijn. De INDEX zoekt 'm dan op in de kolom.

RIJ(D:D)

moest het zijn om de D kolom te specificeren.

Toch zie ik bij jou meerdere manieren om een kolom te selecteren die nieuw zijn voor mij. Even mee experimenteren.

 

[AlexCEL]

Je hoeft de D niet expliciet te noemen, het RIJ-nummer is genoeg.
D: D is sowieso niet goed...

PS "Quoten" is niet nodig. Gebruik knop reageer op bericht, of tekstvak Snel reageren onderaan. (forum-etiquette i.v.m. leesbaarheid)

 

[Rabberzzz]

Is het mogelijk om een andere return te geven wanneer de indexering niet overeenkomen? Ik heb nu gezien dat die de waarde 0 geeft als ie hem niet vindt, maar kan ik hier ook een tekst waarde terug krijgen?

 

[AlexCEL]

Als de "uitkomst" een getal is denk ik hier aan:

=ALS.FOUT(1/(1/INDEX(Dropdown!D:D;SOMPRODUCT((G_OnderbouwLabtestM50>=Dropdown!B2:B8)*(G_OnderbouwLabtestM50<=Dropdown!C2:C8)*('Onderbouw Op Locatie'!E43=Dropdown!A2:A8)*RIJ(2:8))));"hier de tekst")

 

[Rabberzzz]

Ik krijg inderdaad het getal 0 terug. Maar ik krijg een melding van een foutieve formule terug. Zie bijlage voor de toepassing van de formule. De index formule werkt waarbij ik "voldoet' terug krijg als het voldoet, maar geeft mij een 0 terug als het niet klopt, waar ik "voldoet niet" van wil maken. Moet de ALS.FOUT niet gespecificeerd worden als zijnde getal? Of is dit alleen nodig met tekst?