zoeken op drie ( of meer) verschillende waarden

[Havana100]

Beste,

Heeft iemand een idee waarom ik een foutmelding krijg indien ik in cel H78 ip van de tekst januari , februari invul?

De bedoeling van de formule is dat naar de waarde gezocht zal worden die het resultaat is van de drie combinaties die in de cellen H76 +H77 + H78 zijn ingeven. De waarde (geel gekleurde cel) klopt indien ik in cel H78 januari ingeef maar niet als in cel H78 februari in geef. Indien februari in cel H78 zou staan dan zou het resultaat 10000 moeten zijn. (groen gekleurd cel K34)

Sowieso al bedankt voor alle tips !

PS : ik ben echt geen excel specialist dus VBA en dergelijke is nog een beetje zoals chinees!


beste groeten! Bekijk bijlage Map1.xlsx

 

[HSV]

Bv.
Invoeren als matrixformule → Ctrl+Shift+Enter, en niet alleen Enter.

=INDEX(C4:AG100;VERGELIJKEN(H78&H76;A4:A4100&B4:B100;0);VERGELIJKEN(H77;C4:AG4;0))

 

[Cobbe]

Zo zal die wel lukken.

 

[MarcelBeug]

Zoiets (als in post #2) had ik ook, alleen met bereikregels 6-100 i.p.v. 4-100 (resp 4100 ).

Nu nog antwoord op de vraag waarom er een foutmelding ontstond.
Ik heb het even opgezocht in de helpinfo en ik kwam in een ook voor mij nieuw fenomeen terecht, namelijk 2 varianten van de INDEX-functie.
De eerste was mij genoegzaam bekend: = INDEX(matrix;rijnummer;kolomnummer)
Dat is ook de variant die in de oplossing wordt gebruikt.

De tweede VARIANT was ik nog niet eerder tegengekomen: =INDEX(verwijzing;rijnummer;kolomnummer;bereik-getal)
Deze variant was - hoogstwaarschijnlijk onbedoeld - gebruikt in de oorspronkelijke formule.
Het bijzondere in deze variant is dat verwijzing uit een opsomming van meerdere matrices kan bestaan, waarbij dan met bereik-getal wordt aangegeven in welke van de matrices gezocht moet worden.
Nu ging dat goed bij januari, omdat het laatste argument 1 opleverde en er was maar 1 matrix opgegeven als verwijzing (C6:AG100).
Bij februari ging dat fout, omdat het laatste argument 27 opleverde, oftewel de 27e matrix van het eerste argument dat maar uit 1 matrix bestond, dus vandaar de verwijzingsfout (#VERW!).

 

[Havana100]

Cobbe,

Super bedankt hoor, ik zou zeggen je bent een (excel) genie!

beste groeten !

 

[Havana100]

Beste,

toch nog een bijkomende vraag. Is de grote van het bestand beperkt? Mag bijvoorbeeld het aantal rijen 50.000 bedragen?

 

[MarcelBeug]

Zo zal die wel lukken.

Op zich een prima oplossing, alleen kom je een kolom te ver uit doordat het eerste argument in kolom B begint en de laatste in kolom A.
En ook weer de bereiken 4-100 i.p.v. 6-100 (hoewel dat voor de uitkomst niet uitmaakt).
Ik zou er dit van maken:

=ALS.FOUT(INDEX($B$6:$AH$100;VERGELIJKEN($I$78&I76;$A$6:$A$100;0);VERGELIJKEN($I$77;$B$4:$AH$4;0));"")

 

[MarcelBeug]

Bij een groot bestand zou ik zeker voor de (gecorrigeerde) oplossing van Cobbe kiezen, omdat matrixformules dan relatief veel rekentijd vergen.

 

[Havana100]

Beste,

De formule van Cobbe werkt perfect zolang de gegeven in dezelfde excel file staan. Echter als de zoek data in een andere excel file staat werkt ze blijkbaar niet. Iemand een idee waarom niet?

 

[Cobbe]

Dat is nogal wiedes.
Dan moet je voor het bereik ook de bladnaam toevoegen:

=ALS.FOUT(INDEX(Blad2!$B$4:$AH$100;VERGELIJKEN($I$78&I76;Blad2!$A$4:$A$100;0);VERGELIJKEN($I$77;Blad2!$A$4:$AH$4;0));"")

Maar de hulpkolom moet dan ook in dat andere blad gevormd worden.

 

[MarcelBeug]

Bij een andere Excel file, moet ook nog de bestandsnaam worden opgegeven.
Het beste kun je dan de formule opbouwen met beide bestanden open en telkens de diverse bereiken in de formule selecteren.
Excel zorgt er dan zelf voor dat de referenties goed komen.

Bijvoorbeeld: je begint met intypen =ALS.FOUT(INDEX(
dan selecteer je het bereik - in het voorbeeld: Blad2!$B$4:$AH$100
dan type je verder ;VERGELIJKEN(
enzovoorts

 

[Cobbe]

Ja, die uitleg is stukken beter dan mijne preek.

 

[HSV]

Nog een formule voor je openingspost.

=SOMPRODUCT(INDEX(A6:AG60;0;VERGELIJKEN(H77;A4:AG4;0))*($A$6:$A$60=H78)*(B6:B60=H76))