Op zoek naar waarde (geen exacte overeenkomst) op basis van meerdere criteria

[ExcelNerd92]

Hallo allen,

Ik zit met een Excel probleem. Momenteel heb ik een tabblad met 1 staffel. Daarin ga ik op basis van een fictieve brandstofprijs (tabblad parameters cel C10) opzoek naar het corresponderende staffel-% (op tabblad GOT Schepen). Dit doe ik met vert.zoeken, want er is maar 1 staffel waarin gezocht moet worden en vervolgens wordt er gezocht naar een niet exacte overeenkomst, maar één die in de buurt komt en als resultaat krijg ik de corresponderende opslag-%. Simpel!

Nu heb ik nog een tabblad (zie bijlage - tabblad GOT Klanten) daarin staan allerlei verschillende staffels. Op dit tabblad staan alle staffels onder elkaar.
Kolom A = Clausule naam
Kolom B = ondergrens prijs
Kolom C = bovengrens prijs
Kolom D = Gasolie bedrag in euro's
Kolom E = Gasolie percentage

Als kolom D een getal (groter dan 0) heeft dan staat er in Kolom E een 0 en vice versa.

Nu heb ik volgens mij een formule nodig die eerst opzoek gaat in welke rangecellen (Bijvoorbeeld voor Gasolie clausule 6 is dat A97:E111 op tabblad 'GOT Klanten') de staffeltabel zich bevind. Om vervolgens opzoek te gaan naar de niet exacte overeenkomst. Wat het vervolgens weer lastig maakt is dat bij de ene staffel het resultaat waar ik naar opzoek ben staat in kolom D (Gasolie bedrag in euro's) en bij de andere staffel in kolom E (Gasolie percentage).

De formule wil ik plaatsen in tabblad 'Marges' in Kolom N. Op basis van wat er Kolom J op tabblad 'Marges' staat moet er gezocht gaan worden in welke rangecellen de staffeltabel staat. Het kan ook voorkomen dat de cel in Kolom J is, omdat er geen afspraak is over een gasoliepercentage.


Ik hoop dat ik het een beetje helder heb uitgelegd, maar ik zou enorm blij worden als iemand mij de oplossing kan geven of ermee kan helpen.
Mocht er meer info nodig zijn, vraag maar raak.

 

[AlexCEL]

Een lang verhaal en ik zie een heleboel functies in je voorbeeld.

Wat vul je in (H12 en I12?) , wat wil je als resultaat (waar en waarom)?
Geef eens handmatig enkele concrete voorbeelden.

Er zitten hiaten in de data. De kolomkop zegt "tot", maar op basis van de 4 denk ik dat je "tot en met" bedoelt.

Er zijn meerdere manieren om te zoeken, ook op dichtstbijzijnde waarden. Ik zou niet een range gaan afkaderen, maar het op een andere manier doen.
Daarvoor eerst ook nog de vraag: welke Excel-versie gebruik je?

 

[ExcelNerd92]

Hoi AlexCEL, bedankt voor je snelle reactie.

Wat vul je in (H12 en I12?)
Cellen H2:V15 op tabblad 'GOT Klanten' was mijn test veld. Ik heb daar verschillende methodes geprobeerd, maar tot op heden zonder succes.
H12 is in dit geval de fictieve gasolie prijs waar naar gezocht moet worden in Kolommen B:C & I12 is de clausule naam waar naar gezocht moet worden in kolom A.

wat wil je als resultaat [waar en waarom] (een gasolie percentage)?
Wat ik uiteindelijk als resultaat wil is het getal wat in kolom D of E staat op basis van de clausule naam en op basis van de fictieve GOT prijs.

Er zitten hiaten in de data. De kolomkop zegt "tot", maar op basis van de 4 denk ik dat je "tot en met" bedoelt.
Wat betreft kolom kop C1 klopt het wat je zegt. Het is inderdaad t/m.

welke Excel-versie gebruik je?
Wij gebruiken Microsoft 365 MSO (Versie 2209)

Ik hoor graag wat de verschillende mogelijkheden zijn.

 

[AlexCEL]

Wat dacht je van deze voor J12:

=FILTER(E2:E772;(A2:A772=I12)*(B2:B772<=H12)*(C2:C772>=H12);"")

 

[ExcelNerd92]

@AlexCEL je bent een held!

Ik heb de formule nog verder als volgt uitgebreid =ALS.FOUT(ALS(VERT.ZOEKEN(J2;'GOT klanten'!A;4;ONWAAR)>0;FILTER('GOT klanten'!D;('GOT klanten'!A:A=J2)*('GOT klanten'!B:B<=GOT)*('GOT klanten'!C:C>=GOT);"");FILTER('GOT klanten'!D;('GOT klanten'!A:A=J2)*('GOT klanten'!B:B<=GOT)*('GOT klanten'!C:C>=GOT);"")*L2);0)

Hiermee zorg ik ervoor dat de output altijd prijs per ton is. In het geval van een percentage is het 'output (percentage) x prijs' en in geval van een bedrag is het gewoon 'output (bedrag)'.

Misschien wat overbodig complex, maar het werkt wel.

 

[AlexCEL]

Dat kan nog simpeler:

=LET(x;FILTER('GOT klanten'!D:D;('GOT klanten'!A:A=J2)*('GOT klanten'!B:B<=GOT)*('GOT klanten'!C:C>=GOT);"");ALS.FOUT(ALS(VERT.ZOEKEN(J2;'GOT klanten'!A:D;4;ONWAAR)>0;x;x*L2);0))

Ik snap de VERT.ZOEKEN nog niet. Deze geeft altijd de eerste waarde voor Clausule 1,2,3 etc. Dat is de bedoeling?

 

[ExcelNerd92]

Bedankt voor de hulp. Ik ga jouw formule eens ontleden zodat ik het kan begrijpen.

Ik snap de VERT.ZOEKEN nog niet. Deze geeft altijd de eerste waarde voor Clausule 1,2,3 etc. Dat is de bedoeling?

Ja dat is de bedoeling. De formule gaat opzoek naar de eerst waarde in kolom D (4e kolom). Als hier een getal groter dan 0 in staat dan weet ik dat deze clausule op basis van bedrag per stap is als en als er exact 0 in staat dan weet ik dat de clausule procent per stap is.