In deze zelfstudie leert u hoe u de combinatie INDEX & MATCH kunt gebruiken om zoekopdrachten uit te voeren in Excel en Google Spreadsheets.
INDEX & MATCH, het perfecte paar
Laten we eens nader kijken naar enkele manieren waarop u de INDEX- en MATCH-functies kunt combineren. De MATCH-functie is ontworpen om de relatieve positie van een item binnen een array te retourneren, terwijl de INDEX-functie een item uit een array kan ophalen met een specifieke positie. Door deze synergie tussen de twee kunnen ze bijna elk type zoekopdracht uitvoeren dat u nodig heeft.
De combinatie INDEX / MATCH is in het verleden gebruikt als vervanging voor de VERT.ZOEKEN-functie. Een van de belangrijkste redenen is de mogelijkheid om links te zoeken (zie volgende sectie).
Opmerking: de nieuwe XZOEKEN-functie kan nu links-kijkende zoekopdrachten uitvoeren.
Opzoeken naar links
Laten we deze tabel met basketbalstatistieken gebruiken:
We willen Bob's Player # vinden. Omdat de speler # links van de naamkolom staat, kunnen we geen VERT.ZOEKEN gebruiken.
In plaats daarvan zouden we een eenvoudig MATCH-verzoek kunnen doen om de rij van Bob te berekenen
=VERGELIJKEN(H2, B2:B5, 0)
Dit zal zoeken naar een exacte overeenkomst met het woord "Bob", en dus zou onze functie het getal 2 retourneren, aangezien "Bob" in de 2 staatnd positie.
Vervolgens kunnen we de INDEX-functie gebruiken om de speler # terug te geven, die overeenkomt met een rij. Laten we voor nu gewoon handmatig "2" invoeren in de functie:
=INDEX(A2:A5, 2)
Hier verwijst INDEX naar A3, want dat is de 2nd cel binnen het A2:A5-bereik en retourneert het resultaat van 42. Voor ons algemene doel kunnen we deze twee combineren tot:
=INDEX(A2:A5, OVEREENKOMST(H2, B2:B5, 0))
Het voordeel hiervan is dat we een resultaat konden retourneren uit een kolom aan de linkerkant van waar we zochten.
Tweedimensionaal opzoeken
Laten we eens kijken naar onze tafel van vroeger:
Deze keer willen we echter een specifieke statistiek ophalen. We hebben geroepen dat we naar Rebounds willen zoeken in cel H1. In plaats van meerdere IF-instructies te moeten schrijven om te bepalen uit welke kolom het resultaat moet worden gehaald, kunt u opnieuw een MATCH-functie gebruiken. Met de INDEX-functie kunt u de rijwaarde specificeren en de kolomwaarde. We gaan hier nog een MATCH-functie toevoegen om te bepalen welke kolom we willen. Dat zal lijken op
=VERGELIJKEN(H1, A1:E1, 0)
Onze cel in H1 is een vervolgkeuzelijst waarmee we kunnen kiezen naar welke categorie we willen zoeken, en dan bepaalt onze MATCH tot welke kolom in de tabel behoort. Laten we dit nieuwe stukje in onze vorige formule pluggen. Merk op dat we het eerste argument moeten aanpassen om twee dimensies te zijn, omdat we niet langer alleen een resultaat uit kolom A willen.
=INDEX(A2:E5, MATCH(H2, B2:B5, 0), MATCH(H1, A1:E1, 0))
In ons voorbeeld willen we Rebounds voor Charlie vinden. Onze formule gaat dit als volgt evalueren:
=INDEX(A2:E5, MATCH("Charlie", B2:B5, 0), MATCH("Rebounds", A1:E1, 0)) =INDEX(A2:E5, 3, 4) =D4 =6
We hebben nu een flexibele opstelling gemaakt waarmee de gebruiker elke gewenste waarde uit onze tabel kan halen zonder meerdere formules of vertakkende IF-instructies te hoeven schrijven.
Meerdere secties
Het wordt niet vaak gebruikt, maar INDEX heeft een vijfde argument dat kan worden gegeven om te bepalen welke: Oppervlakte binnen argument één te gebruiken. Dit betekent dat we een manier nodig hebben om meerdere gebieden door te geven aan het eerste argument. U kunt dit doen door een extra set haakjes te gebruiken. Dit voorbeeld illustreert hoe u met INDEX resultaten uit verschillende tabellen op een werkblad kunt ophalen.
Dit is de lay-out die we zullen gebruiken. We hebben statistieken voor drie verschillende kwartalen.
In cellen H1:H3 hebben we vervolgkeuzelijsten voor gegevensvalidatie gemaakt voor onze verschillende keuzes. De vervolgkeuzelijst voor het kwartaal komt van J2:J4. We gebruiken dit voor een andere MATCH-instructie, om te bepalen welk gebied moet worden gebruikt. Onze formule in H4 gaat er als volgt uitzien:
=INDEX((A3:E6, A10:E13, A17:E20), MATCH(H2, B3:B6, 0), MATCH(H1, A2:E2, 0), MATCH(H3, J2:J4, 0))
We hebben al besproken hoe de twee binnenste MATCH-functies werken, dus laten we ons concentreren op de eerste en laatste argumenten:
=INDEX((A3:E6, A10:E13, A17:E20),… , OVEREENKOMST(H3, J2:J4, 0))
We hebben de INDEX-functie meerdere arrays gegeven in het eerste argument door ze allemaal tussen haakjes te plaatsen. De andere manier waarop u dit kunt doen, is door Formules - Naam definiëren te gebruiken. Je zou een naam kunnen definiëren met de naam "MyTables" met een definitie van:
=INDEX(MijnTabel,MATCH(H2,Tabel1347[Naam],0),MATCH(H1,Tabel1347[#Headers],0),MATCH(H3,J2:J4,0))
Laten we teruggaan naar de hele verklaring. Onze verschillende MATCH-functies vertellen de INDEX-functie precies waar hij moet zoeken. Eerst zullen we bepalen dat "Charlie" de 3 . isrd rij. Vervolgens willen we "Rebounds", de 4e kolom. Eindelijk hebben we vastgesteld dat we het resultaat willen van 2nd tafel. De formule evalueert hierdoor als volgt:
=INDEX((A3:E6, A10:E13, A17:E20), MATCH(H2, B3:B6, 0), MATCH(H1, A2:E2, 0), MATCH(H3, J2:J4, 0)) =INDEX((A3:E6, A10:E13, A17:E20), 3, 4, 2) =INDEX(A10:E13, 3, 4) =D13 =14
Zoals we aan het begin van dit voorbeeld vermeldden, bent u beperkt tot het hebben van de tabellen op hetzelfde werkblad. Als u de juiste manieren kunt opschrijven om uw INDEX te vertellen uit welke rij, kolom en/of gebied u gegevens wilt ophalen, zal INDEX u goed van pas komen.
Google Spreadsheets -INDEX & MATCH
Alle bovenstaande voorbeelden werken in Google Spreadsheets precies hetzelfde als in Excel.
