Tweerichtings benaderende overeenkomst met meerdere criteria
In deze handleiding laten we zien hoe u een benaderende overeenkomst kunt opzoeken op basis van meerdere criteria die zowel in kolommen als in rijen staan in een Excel-werkblad, met behulp van de INDEX-, MATCH- en IF-functies.
Hoe voert u een tweerichtings benaderende overeenkomst uit met meerdere criteria in Excel?
Om de kledingmaat te bepalen voor een vrouw met een lengte van 165,5 cm en een gewicht van 55 kg volgens de bovenstaande tabel, voegt u twee MATCH-functies toe aan de formule: één in combinatie met een IF-functie om het rijnummer te vinden (op basis van geslacht en lengte) en één om het kolomnummer te bepalen (op basis van gewicht). Vervolgens gebruikt INDEX deze coördinaten om de bijbehorende maat op te zoeken. (Let op: in een INDEX-formule staat het rijnummer vóór het kolomnummer.)
Algemene syntaxis
=INDEX()return_range,MATCH()lookup_value1,IF()lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter.
- return_range: Het bereik waaruit u wilt dat de combinatieformule de maat retourneert – oftewel het matenbereik.
- lookup_value: De waarde die de formule gebruikt om de positie van de bijbehorende maat te bepalen – namelijk de informatie over het opgegeven geslacht, lengte en gewicht.
- lookup_array: Het celbereik met de waarden waarmee vergeleken moet worden met de lookup_value. Dit verwijst naar de bereiken voor geslacht, lengte en gewicht.
- match_type:1 of -1.
1 (of weggelaten, standaardinstelling): MATCH zoekt dan de grootste waarde die kleiner dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten in oplopende volgorde staan.
-1: MATCH zoekt dan de kleinste waarde die groter dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten in aflopende volgorde staan.
Om de kledingmaatte bepalen voor de vrouwmet een lengte van 165,5cm en een gewicht van 55kg, kopieert of typt u de onderstaande formule in cel M9 en drukt u op Ctrl+Shift+Enterom het resultaat te verkrijgen:
=INDEX()D5:J16,MATCH()M6,IF()B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
Uitleg van de formule
=INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): De functie IF controleert elke waarde in B5:B16 om te zien of deze overeenkomt met de waarde in cel M5, namelijk Vrouw. Als dat het geval is, behoudt de functie IF de bijbehorende lengtes uit C5:C16; zo niet, retourneert IF FALSE-waarden. De formule levert een matrix op zoals deze: {157;160;162,5;165;167,5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- MATCH(M6,)IF(B5:B16=M5,C5:C16),1)=MATCH(M6,){157;160;162,5;165;167,5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1):De match_type 1dwingt de functie MATCH om de positie te retourneren van de grootste waarde die kleiner dan of gelijk is aan 165,5(de waarde in cel)M6) in de matrix. MATCH retourneert dus 4, de positie van de waarde 165.
- MATCH(M7,D4:J4,1):De functie MATCH retourneert 3, omdat de grootste waarde die kleiner dan of gelijk is aan de waarde in M7, namelijk 55, op de 3de positie staat in het bereik D4:J4.
- INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1)) = INDEX(D5:J16: De functie INDEX haalt de waarde op op het kruispunt van de 4de rij en de 3de kolom in het matrixbereik D5:J16, namelijk de waarde in cel F8: M.
Gerelateerde functies
De Excel INDEX-functie retourneert de Weergegeven waarde op basis van een opgegeven positie uit een bereik of een matrix.
De Excel MATCH-functie zoekt naar een specifieke waarde in een bereik cellen en retourneert de relatieve positie van die waarde.
De functie ALS is een van de eenvoudigste en meest nuttige functies in een Excel-werkboek. Ze voert een eenvoudige logische test uit en retourneert, afhankelijk van het resultaat van die vergelijking, één waarde als het resultaat WAAR is en een andere waarde als het resultaat ONWAAR is.
Gerelateerde formules
Tweerichtingsopzoekactie met INDEX en MATCH
Als u in Excel iets wilt zoeken over zowel rijen als kolommen – oftewel een waarde wilt opzoeken op het kruispunt van een specifieke rij en kolom – kunt u gebruikmaken van de functies INDEX en MATCH.
Dichtstbijzijnde overeenkomst opzoeken
Om de dichtstbijzijnde overeenkomst van een opzoekwaarde in een numerieke dataset in Excel te vinden, combineert u de functies INDEX, MATCH, ABS en MIN.
Waarde van dichtstbijzijnde overeenkomst opzoeken met meerdere criteria
In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomstwaarde opzoeken op basis van meerdere criteria. Met een slimme combinatie van de functies INDEX, MATCH en IF lukt dat razendsnel in Excel.
Opzoekactie met meerdere criteria met INDEX en MATCH
Bij het werken met een grote database in een Excel-werkblad vol kolommen en rijtitels is het vaak lastig om gegevens te vinden die aan meerdere criteria voldoen. Gebruik in dat geval een matrixformule met de functies INDEX en MATCH.
Benaderende overeenkomst met INDEX en MATCH
Er zijn situaties waarin u benaderende overeenkomsten in Excel moet vinden – bijvoorbeeld om medewerkersprestaties te beoordelen, studentencijfers te bepalen of portokosten op basis van gewicht te berekenen. In deze handleiding laten we u zien hoe u met de functies INDEX en MATCH precies de resultaten ophaalt die u zoekt.
De beste Office-productiviteitshulpmiddelen
Kutools voor Excel – Helpt u om op te vallen tussen de massa
Kutools voor Excel Beschikt over meer dan 300 functies,zodat u alles wat u nodig heeft binnen één klik bereik heeft...
Office Tab – Schakel tabbladenlezen en -bewerken in Microsoft Office (inclusief Excel) in
- Schakel in één seconde tussen tientallen geopende documenten!
- Bespaar honderden muisklikken per dag en zeg vaarwel tegen muisarm.
- Verhoog uw productiviteit met 50 % bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabs naar Office (inclusief Excel), net als in Chrome, Edge en Firefox.