Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

INDEX en MATCH samen gebruiken in Excel

Wanneer u met Excel-tabellen werkt, kunt u voortdurend situaties tegenkomen waarin u een waarde moet opzoeken. In deze zelfstudie laten we u zien hoe u de combinatie van de INDEX- en MATCH-functies toepast om horizontale en verticale zoekopdrachten, tweerichtingszoekopdrachten, hoofdlettergevoelige zoekopdrachten en de zoekopdrachten die aan meerdere criteria voldoen, te maken.

Wat doen INDEX- en MATCH-functies in Excel?

INDEX- en MATCH-functies samen gebruiken


Wat doen INDEX- en MATCH-functies in Excel?

Voordat we de functies INDEX en MATCH gebruiken, moeten we eerst weten hoe INDEX en MATCH ons kunnen helpen bij het opzoeken van waarden.

Gebruik van de INDEX-functie in Excel

Het INDEX functie in Excel retourneert de waarde op een bepaalde locatie in een specifiek bereik. De syntaxis van de functie INDEX is als volgt:

=INDEX(array, row_num, [column_num])
  • reeks (vereist) verwijst naar het bereik waaruit u de waarde wilt retourneren.
  • rij_nummer (vereist, tenzij column_num aanwezig is) verwijst naar het rijnummer van de array.
  • column_num (optioneel, maar vereist als row_num wordt weggelaten) verwijst naar het kolomnummer van de array.

Om bijvoorbeeld te weten de eindexamenscore van Jeff, de 6e leerling op de lijst, kun je de INDEX-functie als volgt gebruiken:

=INDEX(E2:E11, 6) >>> retouren 60

Excel-indexovereenkomst 01

√ Opmerking: het bereik E2: E11 is waar het eindexamen wordt vermeld, terwijl het nummer 6 vindt de examenscore van de 6e leerling.

Laten we hier een kleine test doen. Voor de formule: =INDEX(B2:E2,3), welke waarde zal het teruggeven? --- Ja, het zal terugkeren! China 3 waarde in het opgegeven bereik.

Nu moeten we weten dat de INDEX-functie perfect kan werken met horizontale of verticale bereiken. Maar wat als we het nodig hebben om een ​​waarde in een groter bereik met meerdere rijen en kolommen te retourneren? Welnu, in dit geval moeten we zowel een rijnummer als een kolomnummer toepassen. Om er bijvoorbeeld achter te komen het land waar Emily vandaan komt met INDEX kunnen we de waarde met een rijnummer van 8 en een kolomnummer van 3 in de cellen via B2 tot E11 als volgt lokaliseren:

=INDEX(B2:E11,8,3) >>> retouren China

Excel-indexovereenkomst 02

Volgens bovenstaande voorbeelden over de INDEX-functie in Excel, moet u weten dat:

  • De INDEX-functie kan werken met verticale en horizontale bereiken.
  • De INDEX-functie is niet hoofdlettergevoelig.
  • Het rijnummer gaat voor op het kolomnummer (als u beide nummers nodig hebt) in de INDEX-formule.

Voor een echt grote database met meerdere rijen en kolommen is het voor ons zeker niet handig om de formule met een exact rijnummer en kolomnummer toe te passen. En dit is het wanneer we het gebruik van de MATCH-functie moeten combineren.

Laten we nu eerst de basis van de MATCH-functie leren kennen.


Gebruik van de MATCH-functie in Excel

De MATCH-functie in Excel retourneert een numerieke waarde, de locatie van een specifiek item in het opgegeven bereik. De syntaxis van de MATCH-functie is als volgt:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (vereist) verwijst naar het cellenbereik waarin u wilt dat MATCH zoekt.
  • match_type (optioneel), 1, 0 or -1:
  • 1(standaard), MATCH vindt de grootste waarde die kleiner is dan of gelijk is aan de opzoekwaarde. De waarden in de lookup_array moet in oplopende volgorde worden geplaatst.
  • 0, MATCH vindt de eerste waarde die exact gelijk is aan de opzoekwaarde. De waarden in de lookup_array kan in willekeurige volgorde. (Voor de gevallen waarin het zoektype is ingesteld op 0, kunt u jokertekens gebruiken.)
  • -1, MATCH vindt de kleinste waarde die groter is dan of gelijk is aan de opzoekwaarde. De waarden in de lookup_array moet in aflopende volgorde worden geplaatst.

Om bijvoorbeeld te weten de positie van Vera in de namenlijst, kunt u de MATCH-formule als volgt gebruiken:

=VERGELIJKEN("vera",C2:C11,0) >>> retouren 4

Excel-indexovereenkomst 03

√ Opmerking: de MATCH-functie is niet hoofdlettergevoelig. Het resultaat "4" geeft aan dat de naam "Vera" op de 4e positie van de lijst staat. De "0" in de formule is het zoektype dat de eerste waarde in de opzoekarray vindt die exact gelijk is aan de opzoekwaarde "Vera".

Weten de positie van de score "96" in de rij van B2 tot E2, kunt u MATCH als volgt gebruiken:

=VERGELIJKEN(96,B2:E2,0) >>> retouren 4

Excel-indexovereenkomst 04

☞ Dingen die we moeten weten over de MATCH-functie in Excel:

  • De MATCH-functie retourneert de positie van de opzoekwaarde in de opzoekarray, niet de waarde zelf.
  • De MATCH-functie retourneert de eerste overeenkomst in het geval van duplicaten.
  • Net als de INDEX-functie, kan de MATCH-functie ook werken met verticale en horizontale bereiken.
  • MATCH is ook niet hoofdlettergevoelig.
  • Als de opzoekwaarde van de MATCH-formule de vorm van tekst heeft, plaatst u deze tussen aanhalingstekens.

Nu we het basisgebruik van de INDEX- en MATCH-functies in Excel kennen, stropen we de mouwen op en maken we ons klaar om de twee functies te combineren.


INDEX- en MATCH-functies samen gebruiken

In dit deel zullen we het hebben over verschillende omstandigheden om de INDEX- en MATCH-functies te gebruiken om aan verschillende behoeften te voldoen.

Voorbeeld om INDEX en MATCH te combineren

Bekijk het onderstaande voorbeeld om erachter te komen hoe we de INDEX- en MATCH-functies kunnen combineren:

Om bijvoorbeeld te weten Evelyns eindexamenscore, moeten we de formule gebruiken:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> retouren 90

Excel-indexovereenkomst 05

Omdat de formule er misschien ingewikkeld uitziet, laten we elk deel ervan doornemen.

Excel-indexovereenkomst 06

Zoals je hierboven kunt zien, is de grote INDEX formule bevat drie argumenten:

  • reeks: A2: D11 vertelt INDEX om de overeenkomende waarde van de cellen tot en met . te retourneren A2 tot D11.
  • rij_nummer: MATCH("evelyn",B2:B11,0) vertelt INDEX de exacte rij van de waarde.
  • Over de MATCH-formule kunnen we het als volgt uitleggen: om de positie van de eerste waarde die precies gelijk is aan "evelyn" in de cellen van B2 tot B11 in een numerieke waarde, die is 5.
  • column_num: MATCH("eindexamen",A1:D1,0) vertelt INDEX de exacte kolom van de waarde.
  • Over de MATCH-formule kunnen we het als volgt uitleggen: om de positie van de eerste waarde die precies gelijk is aan "eindexamen" in de cellen van A1 tot D1 in een numerieke waarde, die is 4.

Dus je kunt de grote formule zo eenvoudig zien als degene die we hieronder hebben laten zien:

=INDEX(A2: D11,5,4)

In het voorbeeld gebruikten we hardcoded waarden, "evelyn" en "eindexamen". In zo'n grote formule willen we echter geen hardgecodeerde waarden, omdat we ze elke keer moeten veranderen als we naar iets nieuws gaan zoeken. In dergelijke omstandigheden kunnen we celverwijzingen gebruiken om de formule als volgt dynamisch te maken:

=INDEX(A2: D11,BIJ ELKAAR PASSEN(G2,B2:B11,0),BIJ ELKAAR PASSEN(F3,A1:D1,0))

Excel-indexovereenkomst 07


INDEX en MATCH om een ​​zoekopdracht naar links toe te passen

Laten we zeggen dat je Evelyns klas moet kennen, hoe kunnen we INDEX en MATCH gebruiken om het antwoord te weten? Als je goed hebt opgelet, zou je moeten opmerken dat de klassekolom zich aan de linkerkant van de naamkolom bevindt en dat dit buiten het vermogen van een andere krachtige zoekfunctie van Excel, de VERT.ZOEKEN, valt.

In feite is de mogelijkheid om naar links te zoeken een van de aspecten waar de combinatie van INDEX en MATCH superieur is aan VERT.ZOEKEN.

Weten Evelyns klas, het enige wat u hoeft te doen is de waarde in de cel F3 te wijzigen in "Klasse", en dezelfde formule te gebruiken als hierboven weergegeven, de INDEX- en MATCH-functies zullen u dan meteen het antwoord vertellen:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> retouren A

Excel-indexovereenkomst 08

Als je een installatie hebtLED Kutools for Excel, een professionele Excel-add-in ontwikkeld door ons team, u kunt ook een zoekopdracht naar links toepassen voor opgegeven waarden met zijn ZOEKEN van rechts naar links functie met een paar klikken. Om de functie te implementeren, ga naar de Kutools tabblad in uw Excel, zoek de Formule groep, en klik ZOEKEN van rechts naar links in de vervolgkeuzelijst van Super ZOEKEN. U ziet een pop-upvenster zoals dit:

Excel-indexovereenkomst 09

Klik hier voor concrete stappen om de functie voor het opzoeken van links toe te passen met Kutools voor Excel.


INDEX en MATCH om een ​​zoekopdracht in twee richtingen toe te passen

Kunt u nu de combinatieformule INDEX en MATCH maken met dynamische opzoekwaarden om tweerichtingszoekopdrachten toe te passen? Laten we oefenen met het maken van formules in de cellen G3, G4 en G5, zoals hieronder weergegeven:

Excel-indexovereenkomst 10

Hier zijn de antwoorden:

Cel G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Cel G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Cel G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Opmerking: nadat u de formules hebt toegepast, kunt u gemakkelijk de informatie van studenten krijgen door de naam in cel G2 te wijzigen.


INDEX en MATCH om een ​​hoofdlettergevoelige zoekopdracht toe te passen

Uit de bovenstaande voorbeelden weten we dat de functies INDEX en MATCH niet hoofdlettergevoelig zijn. In de gevallen waarin u uw formule echter nodig heeft om hoofdletters en kleine letters te onderscheiden, kunt u toevoegen EXACT functie voor uw formules als volgt:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter. Er verschijnt dan een paar accolades in de formulebalk.

Om bijvoorbeeld te weten JIMMY's examenscore, gebruik de functies als volgt:

Excel-indexovereenkomst 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> retouren 86

Of u kunt celverwijzingen gebruiken:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> retouren 86
Let op: vergeet niet in te vullen met Ctrl + Shift + Enter.


INDEX en MATCH om een ​​zoekopdracht met meerdere criteria toe te passen

Als je te maken hebt met een grote database met meerdere kolommen en rijbijschriften, is het altijd lastig om iets te vinden dat aan meerdere voorwaarden voldoet. Raadpleeg in dit geval de onderstaande formule om meerdere criteria op te zoeken:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter. Er verschijnt dan een paar accolades in de formulebalk.

Om bijvoorbeeld de te vinden eindexamenscore van Coco van klasse A die uit India komt, de formule is als volgt:

Excel-indexovereenkomst 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> retouren 88
√ Let op: vergeet niet mee te doen met Ctrl + Shift + Enter.

Nou, wat als je constant vergeet te gebruiken? Ctrl + Shift + Enter om de formule te voltooien zodat de formule onjuiste resultaten oplevert? Hier hebben we een complexere formule, waarmee je gewoon kunt voltooien met één simpele Enter key:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Voor hetzelfde voorbeeld hierboven om de . te vinden eindexamenscore van Coco van klasse A die uit India komt, de formule die alleen een gebruikelijke . nodig heeft Enter hit is als volgt:

Excel-indexovereenkomst 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> retouren 88

Hier zullen we geen hardgecodeerde waarden gebruiken, omdat we een universele formule willen in het geval met meerdere criteria. Alleen op deze manier kunnen we gemakkelijk het gewenste resultaat krijgen door de waarden in de cellen G2, G3, G4 in het bovenstaande voorbeeld te wijzigen.

met Kutools for Excel's Multi-condition Lookup-functie, u kunt met een paar klikken specifieke waarden opzoeken met meerdere criteria. Om de functie te implementeren, ga naar de Kutools tabblad in uw Excel, zoek de Formule groep, en klik Opzoeken in meerdere condities in de vervolgkeuzelijst van Super ZOEKEN. U ziet dan een pop-upvenster zoals hieronder weergegeven:

Excel-indexovereenkomst 14

Klik hier voor concrete stappen om de zoekfunctie voor meerdere voorwaarden toe te passen met Kutools voor Excel.


INDEX en MATCH om een ​​zoekopdracht toe te passen op meerdere kolommen

Als we een Excel-spreadsheet hebben met verschillende kolommen die één bijschrift delen, zoals hieronder weergegeven, hoe kunnen we dan de naam van elke leerling matchen met zijn/haar klas met INDEX en MATCH?

Excel-indexovereenkomst 15

Hier, laat me je de manier tonen om de taak te voltooien met onze professionele tool Kutools for Excel. Met zijn Formule Helper, je kunt studenten snel matchen met hun klassen volgens de onderstaande stappen:

1. Selecteer de doelcel waar u de functie wilt toepassen.

2. Onder de Kutools tab, ga naar Formule Helper, Klik Formule Helper in de vervolgkeuzelijst.

Excel-indexovereenkomst 16

3. Kiezen Opzoeken van formuletype, klik dan op Indexeer en match op meerdere kolommen.

Excel-indexovereenkomst 17

4. een. Klik op de 1e excel index match icoonknop aan de rechterkant van Lookup_col om de cellen te selecteren waarvan u een waarde wilt retourneren, dwz de klassenamen. (U kunt hier slechts één kolom of rij selecteren.)
    B. Klik op de 2e excel index match icoonknop aan de rechterkant van Table_rng om de cellen te selecteren die overeenkomen met de waarden in de geselecteerde Lookup_col, dat wil zeggen, de namen van de studenten.
    C. Klik op de 3e excel index match icoonknop aan de rechterkant van Opzoekwaarde om de cel te selecteren die moet worden opgezocht, dwz de naam van de leerling die u wilt koppelen aan zijn/haar klas.

Excel-indexovereenkomst 18

5. Klik op Ok, je zult de klasnaam van Jimmy zien verschijnen in de bestemmingscel.

Excel-indexovereenkomst 19

6. Nu kunt u de vulgreep naar beneden slepen om de klassen van andere studenten in te vullen.

Excel-indexovereenkomst 20

Klik om Kutools voor Excel te downloaden voor een gratis proefperiode van 30 dagen.



  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
kte tabblad 201905
  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.