Tweeweg benaderende overeenkomst met meerdere criteria
In deze tutorial bespreken we hoe je een benaderende overeenkomst kunt vinden op basis van meerdere criteria die zowel in kolommen als rijen zijn vermeld in een Excel-spreadsheet, met behulp van de functies INDEX, MATCH en ALS.
Hoe voer je een tweeweg benaderende overeenkomst uit met meerdere criteria in Excel?
Om de kledingmaat te achterhalen voor een vrouw die 165,5 cm lang is en 55 kg weegt volgens de bovenstaande tabel, kun je twee MATCH-functies aan de formule toevoegen: één met een ALS-functie om het rijnummer (geslacht en lengte) te krijgen, de andere om het kolomnummer (gewicht) te verkrijgen. Vervolgens zal INDEX de bijbehorende maat vinden op basis van de coördinaten. (Let op: het rijnummer komt vóór het kolomnummer in een INDEX-formule.)
Algemene syntaxis
=INDEX(return_range,MATCH(lookup_value1,ALS(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Opmerking: Dit is een matrixformule die vereist dat je deze invoert met Ctrl + Shift + Enter.
- return_range: Het bereik waaruit je wilt dat de combinatieformule de maat retourneert. Hier verwijst dit naar het maatbereik.
- lookup_value: De waarde die de formule gebruikt om de positie van de bijbehorende maat te lokaliseren. Hier verwijst dit naar de informatie over het gegeven geslacht, lengte en gewicht.
- lookup_array: Het bereik van cellen met de waarden om te vergelijken met de lookup_value. Hier verwijst dit naar de geslacht-, lengte- en gewichtsbereiken.
- match_type: 1 of -1.
1 of weggelaten (standaard), MATCH zal de grootste waarde vinden die kleiner dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten in oplopende volgorde staan.
-1, MATCH zal de kleinste waarde vinden die groter dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten in aflopende volgorde staan.
Om de kledingmaat te achterhalen voor de vrouw die 165,5 cm lang is en 55 kg weegt, kopieer of voer de onderstaande formule in cel M9 in, en druk op Ctrl + Shift + Enter om het resultaat te krijgen:
=INDEX(D5:J16,MATCH(M6,ALS(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
Uitleg van de formule
=INDEX(D5:J16,MATCH(M6,ALS(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- ALS(B5:B16=M5,C5:C16): De ALS-functie controleert elke waarde in B5:B16 om te zien of ze overeenkomen met de waarde in cel M5, Vrouw. Zo ja, dan houdt de ALS-functie de bijbehorende lengtes in C5:C16 vast; zo niet, dan retourneert ALS ONWAAR-waarden. De formule zal een array retourneren zoals deze: {157;160;162.5;165;167.5;170;ONWAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR}.
- MATCH(M6,MATCH(M6,ALS(B5:B16=M5,C5:C16),1),1) = MATCH(M6,MATCH(M6,{157;160;162.5;165;167.5;170;ONWAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR},1),1): Het match_type 1 dwingt de MATCH-functie om de positie van de grootste waarde die kleiner dan of gelijk is aan 165,5 (de waarde in cel M6) in de array te retourneren. Dus zal MATCH 4 retourneren, de positie van de waarde 165.
- MATCH(M7,D4:J4,1): De MATCH-functie retourneert 3, omdat de grootste waarde die kleiner dan of gelijk is aan de waarde in M7, 55, zich op de 3e positie van het bereik D4:J4 bevindt.
- INDEX(D5:J16D5:J16,MATCH(M6,ALS(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1)) = INDEX(D5:J16D5:J16,4,3): De INDEX-functie haalt de waarde op het snijpunt van de 4e rij en 3e kolom in het maatbereik D5:J16 op, wat de waarde in cel F8, M, is.
Gerelateerde functies
De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een array.
De Excel MATCH-functie zoekt naar een specifieke waarde in een bereik van cellen en retourneert de relatieve positie van de waarde.
De ALS-functie is een van de eenvoudigste en nuttigste functies in een Excel-werkmap. Het voert een eenvoudige logische test uit die afhankelijk is van het vergelijkingsresultaat, en retourneert één waarde als het resultaat WAAR is, of een andere waarde als het resultaat ONWAAR is.
Gerelateerde formules
Tweewegopzoeken met INDEX en MATCH
Om iets te zoeken in zowel rijen als kolommen in Excel, ofwel om een waarde op het snijpunt van een specifieke rij en kolom te vinden, kunnen we gebruik maken van de functies INDEX en MATCH.
Zoek dichtstbijzijnde overeenkomst
Om de dichtstbijzijnde overeenkomst van een zoekwaarde in een numerieke dataset in Excel te vinden, kun je de functies INDEX, MATCH, ABS en MIN samen gebruiken.
Zoek dichtstbijzijnde overeenkomst met meerdere criteria
In sommige gevallen moet je mogelijk de dichtstbijzijnde of benaderende overeenkomstwaarde vinden op basis van meer dan één criterium. Met de combinatie van de functies INDEX, MATCH en ALS kun je dit snel doen in Excel.
Opzoeken met meerdere criteria met INDEX en MATCH
Bij het omgaan met een grote database in een Excel-spreadsheet met verschillende kolommen en rijkoppen, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kun je een matrixformule gebruiken met de functies INDEX en MATCH.
Benaderende overeenkomst met INDEX en MATCH
Er zijn momenten waarop we benaderende overeenkomsten in Excel moeten vinden om de prestaties van werknemers te evalueren, studentencijfers te beoordelen, postkosten te berekenen op basis van gewicht, enz. In deze tutorial bespreken we hoe we de functies INDEX en MATCH kunnen gebruiken om de resultaten te verkrijgen die we nodig hebben.
De Beste Office-productiviteitstools
Kutools voor Excel - Helpt U Om Uit Te Blinken In De Menigte
Kutools voor Excel Beschikt Over Meer Dan 300 Functies, Waardoor Wat U Nodig Hebt Maar Een Klik Verwijderd Is...
Office Tab - Schakel Tabbladgestuurd Lezen en Bewerken in Microsoft Office (inclusief Excel)
- Eén seconde om te schakelen tussen tientallen open documenten!
- Verminder honderden muisklikken voor u elke dag, zeg vaarwel tegen muisarm.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt Efficiënte Tabbladen naar Office (inclusief Excel), Net Als Chrome, Edge en Firefox.