zowel vertikaal als horizontaal zoeken met verschillende voorwaarden

[Havana100]

beste,

In het voorbeeld :

de formule zou een combinatie moeten zoeken

de rij bepalen waarin de waarde uit cel G17 staat ( hier dus rij 11)

daarna in deze rij de waarde uit cel H17 zoeken met als voorwaarde de waarde uit F17 in rij 2 ( hier dus cel P14, kolom P)

daarna in rij 2 naar de waarde zoeken uit cel I17 die als eerst volgt op kolom P, hier dus kolom R

daarna de combinatie weergeven van kolom R en rij 17 , hier dus blauw ( cel J17)

idem voor het tweede vraag in het voorbeeld ; zoeken naar BDC in rij 2, in kolom A naar 55 , in rij 2 FL wat als resultaat de waarde 1 geeft ( cel J18)

hopelijk een beetje duidelijk uitgelegd......

Voor ieder lezer natuurlijk de beste wensen !!!

 

[sylvester-ponte]

bedoel je zo iets? zie grijze cellen

 

[sylvester-ponte]

Havana, als dit ongeveer is wat je bedoeld, dan maak de function voor je af.
met bvb de optie om het adres van het antwoordt optioneel weer te geven.
en als hij iets niet vind dat hij vertelt waar het fout ging. (dat is nog niet helemaal af)

dus als dit het ongeveer is, laat het dan even weten.

 

[VenA]

Met een formule kan het ook wel.

=INDEX($B$3:$DI$14;MATCH(G17;$A$3:$A$14;0);SUMPRODUCT(($A$3:$DI$14=H17)*($A$2:$DI$2=F17)*($A$3:$A$14=G17)*(COLUMN($A$1:$DI$1)))+MATCH(I17;{"LN";"Fl"};0))

 

[sylvester-ponte]

volgens mij moet het deze zijn:

=INDEX($B$3:$DI$14;VERGELIJKEN(G18;$A$3:$A$14;0);SOMPRODUCT(($A$3:$DI$14=H18)*($A$2:$DI$2=F18)*($A$3:$A$14=G18)*(KOLOM($A$1:$DI$1)))+VERGELIJKEN(I18;[B][COLOR="#FF0000"]$R$2:$AC$2[/COLOR][/B];0)[B][COLOR="#FF0000"]-(F18=$C$2)[/COLOR][/B])

ik geloof niet dat het een matrixformule is.(ik bedoel dat je niet hoeft af te sluiten met CSE)

 

[sylvester-ponte]

hier nog een met gewone excel formule en udf met commentaar en eventueel adres van gevonden oplossing. (zie grijze cellen)

 

[Havana100]

Allebei van harte bedankt hoor !

VenA : ik heb de formule aangepast aan de originele file. Spijtig genoeg kan ik deze (nogal wat privé gegevens) niet mee doorsturen. de formule wordt dan

{=INDEX('[AO G lev.xlsx]Blad1'!$B$11:$GZ$2000;VERGELIJKEN('FAC geg'!A2;'[AO G lev.xlsx]Blad1'!$A$11:$A$2000;0);SOMPRODUCT(('[AO G lev.xlsx]Blad1'!$A$11:$GZ$2000=B8)*('[AO G lev.xlsx]Blad1'!$A$2:$GZ$2=T1)*('[AO G lev.xlsx]Blad1'!$A$11:$A$2000='FAC geg'!A2)*(KOLOM('[AO G lev.xlsx]Blad1'!$A$1:$GZ$1)))+VERGELIJKEN(T2;{"LN";"Fl"};0))}

waarin : '[AO G lev.xlsx]Blad1'!$B$11:$GZ$2000 gelijk is aan $B$3:$DI$14 is dus wel een andere excel file , 'FAC geg'!A2 = de waarde die in cel G17 (voorbeeld) staat. B8 is gelijk aan de waarde uit cel H17. T1 is de waarde uit cel G17 en T2 = gelijk aan de waarde uit cel I17.

Echter ik #N/B als resultaat.

als ik VERGELIJKEN('FAC geg'!A2;'[AO G lev.xlsx]Blad1'!$A$11:$A$2000;0) uitvoer krijg ik de juiste waarde.

echter als ik SOMPRODUCT(('[AO G lev.xlsx]Blad1'!$A$11:$GZ$2000=B8)*('[AO G lev.xlsx]Blad1'!$A$2:$GZ$2=T1)*('[AO G lev.xlsx]Blad1'!$A$11:$A$2000='FAC geg'!A2)*(KOLOM('[AO G lev.xlsx]Blad1'!$A$1:$GZ$1))) laat uitvoer krijg ik weer de foutmelding.

dan geprobeerd in te geven : =SOMPRODUCT(('[AO G lev.xlsx]Blad1'!$A$11:$GZ$2000=B8)*('[AO G lev.xlsx]Blad1'!$A$2:$GZ$2=T1)*('[AO G lev.xlsx]Blad1'!$A$11:$A$2000='FAC geg'!A2)), foutmelding.

daarna : =SOMPRODUCT(('[AO G lev.xlsx]Blad1'!$A$2:$GZ$2=T1)*('[AO G lev.xlsx]Blad1'!$A$11:$A$2000='FAC geg'!A2)) en geen foutmelding

dus dacht ik dat het probleem in het gedeelte '[AO G lev.xlsx]Blad1'!$A$11:$GZ$2000=B8) zou zitten. Dan heb ik B8 vervangen door een gewoon getal, dus geen datum, maar geen resultaat.

Enig idee waar of waarom het niet lukt?

 

[VenA]

Het is mij een te groot breiwerk om te analyseren wat er misgaat. Gebruik en kleiner bereik en gebruik formules evalueren om te zien waar het misgaat. Met de suggesties van @sylvester-ponte lukt het ook niet?

 

[Havana100]

VenA,

Blijkbaar heb ik onderstaande nog niet verstuurd. We puzzelen nog even verder ..... De oorzaak zou niet kunnen zijn dat in de somproduct formule de verwijzing '[AO G lev.xlsx]Blad1'!$A$11:$GZ$2000 staat? dus van A11 tot en met GZ2000?

toch bedankt voor alle moeite hoor !

Sylverster,

als ik in je file ga kijken naar het resultaat in cel J17 dan zie ik daar rood staan , maar resultaat zou eigenlijk blauw moeten zijn....

 

[sylvester-ponte]

kijk eens na, ik heb je opgave een beetje veranderd. zie eerste code. als je die terug veranderd naar wat er onderstaat komt er weer blauw te staan
succes en een goed jaar toe gewenst.