Opzoeken met meerdere criteria met INDEX en MATCH
Bij het werken met een grote database in een Excel-werkblad met meerdere kolommen en rijtitels is het vaak lastig om gegevens te vinden die aan meerdere criteria voldoen. In dat geval kunt u een matrixformule gebruiken met de INDEX- en MATCH-functies.
Hoe voert u een opzoekactie uit met meerdere criteria?
Om het product te vinden dat wit en medium is en een prijs heeft van $18, zoals in de bovenstaande afbeelding, kunt u Booleaanse logica gebruiken om een matrix van 1’en en 0’en te genereren die aangeeft welke rijen aan de criteria voldoen. De MATCH-functie bepaalt vervolgens de positie van de eerste rij die aan alle criteria voldoet, waarna INDEX het bijbehorende product-ID ophaalt uit dezelfde rij.
Algemene syntaxis
=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter.
- return_range: Het bereik waaruit u wilt dat de combinatieformule het product-ID retourneert. In dit geval verwijst het naar het bereik met product-ID’s.
- criteria_value: De criteria die worden gebruikt om de positie van het product-ID te bepalen. Hierbij gaat het om de waarden in de cellen H3, H5 en H6.
- criteria_range: De bijbehorende bereiken waarin de criteria_values staan. In dit geval verwijzen ze naar de bereiken voor kleur, maat en prijs.
- match_type 0: Forceert MATCH om de eerste waarde te vinden die exact gelijk is aan de lookup_value.
Om het product te vinden dat witen mediumis en een prijs heeft van $18, kopieer of typ de onderstaande formule in cel H8 en druk op Ctrl+Shift+Enterom het resultaat te verkrijgen:
=INDEX()B5:B10,MATCH(1,())„Wit"=C5:C10)*(„Medium"=D5:D10)*(18=E5:E10),0))
Of gebruik Een celverwijzing om de formule dynamisch te maken:
=INDEX()B5:B10,MATCH(1,())H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
Uitleg van de formule
=INDEX()B5:B10,MATCH(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10):De formule vergelijkt de kleur in cel H3 met alle kleuren in het bereik C5:C10, de maat in H5 met alle maten in D5:D10, en de prijs in H6 met alle prijzen in E5:E10. Het eerste resultaat ziet er als volgt uit:
{WAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR}*{ONWAAR;ONWAAR;WAAR;WAAR;WAAR;ONWAAR}*{ONWAAR;ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR}.
Bij vermenigvuldiging worden de WAAR- en ONWAAR-waarden omgezet naar 1’en en 0’en:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Na vermenigvuldiging krijgen we één enkele matrix zoals deze:
{0;0;0;0;1;0}. - MATCH(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=MATCH(1,)De match_type 0 vraagt de MATCH-functie om een exacte overeenkomst te vinden. De functie geeft vervolgens de positie van 1 in de matrix {0;0;0;0;1;0}, namelijk 5.
- INDEX()B5:B10,MATCH(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10De INDEX-functie retourneert de 5de waarde in het product-ID-bereik B5:B10, namelijk 30005.
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 celbereik en retourneert de relatieve positie van die waarde.
Gerelateerde formules
Zoeken naar de dichtstbijzijnde overeenkomst met meerdere criteria
In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomstwaarde opzoeken op basis van meerdere criteria. Met een krachtige combinatie van de functies INDEX, MATCH en IF realiseert u dit in Excel razendsnel.
Benaderende overeenkomst met INDEX en MATCH
Er zijn momenten waarop u benaderende overeenkomsten moet vinden in Excel – bijvoorbeeld om medewerkersprestaties te beoordelen, cijfers van studenten vast te stellen of portokosten te berekenen op basis van gewicht. In deze handleiding laten we u zien hoe u met de functies INDEX en MATCH precies de resultaten ophaalt die u zoekt.
Zoekwaardebereik uit een ander werkblad of een andere werkmap
Als u weet hoe u de VLOOKUP-functie gebruikt om waarden in een werkblad op te zoeken, dan is het geen enkel probleem om waarden uit een ander werkblad of zelfs een andere werkmap op te halen. In deze handleiding leert u hoe u in Excel waarden uit een ander werkblad opzoekt.
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.