INDEX-MATCH is een populairder hulpmiddel voor Excel geworden omdat het de beperking van de VLOOKUP-functie oplost en gemakkelijker te gebruiken is. INDEX-MATCH-functie in Excel heeft een aantal voordelen ten opzichte van de VLOOKUP-functie:

1. INDEX en MATCH zijn flexibeler en sneller dan Vlookup
2. Het is mogelijk om horizontale zoekopdrachten, verticale zoekopdrachten, 2-weg zoekopdrachten, linkse zoekopdrachten, hoofdlettergevoelige zoekopdrachten en zelfs zoekopdrachten uit te voeren op basis van meerdere criteria.
3. Bij gesorteerde gegevens is INDEX-MATCH 30% sneller dan VLOOKUP. Dit betekent dat in een grotere dataset 30% sneller logischer is.

Laten we beginnen met de gedetailleerde concepten van elke INDEX en MATCH.

INDEX-functie

De INDEX-functie in Excel is zeer krachtig en tegelijkertijd een flexibel hulpmiddel dat de waarde op een bepaalde locatie in een bereik ophaalt. Met andere woorden: het retourneert de inhoud van een cel, gespecificeerd door rij- en kolomverschuiving.

Syntaxis:

=INDEX(reference, [row], [column])>

Parameters:

referentie: de reeks cellen waarin moet worden gecompenseerd. Het kan een enkel bereik zijn of een volledige gegevensset in een gegevenstabel. rij [optioneel]: het aantal offsetrijen. Het betekent dat als we een tabelreferentiebereik als A1:A5 kiezen, de cel/inhoud die we willen extraheren zich op de verticale afstand bevindt. Hier is rij voor A1 1, voor A2 rij = 2, enzovoort. Als we rij = 4 opgeven, wordt A4 geëxtraheerd. Omdat rij optioneel is, worden, als we geen rijnummer specificeren, hele rijen in het referentiebereik geëxtraheerd. Dat is in dit geval A1 t/m A5. kolom [optioneel]: het aantal offsetkolommen. Het betekent dat als we een tabelreferentiebereik als A1:B5 kiezen, de cel/inhoud die we willen extraheren zich op de horizontale afstand bevindt. Hier zal voor A1 de rij 1 zijn en de kolom 1, voor B1 zal de rij 1 zijn, maar de kolom zal op dezelfde manier 2 zijn voor A2 rij = 2, kolom = 1, voor B2 rij = 2, kolom = 2 enzovoort. Als we rij = 5 en kolom 2 opgeven, wordt B5 geëxtraheerd. Omdat de kolom optioneel is, dus als we geen rijnummer specificeren. Vervolgens wordt de gehele kolom in het referentiebereik geëxtraheerd. Als we bijvoorbeeld rij = 2 en kolom leeg opgeven, wordt (A2:B2) geëxtraheerd. Als we niet zowel Rij als Kolom specificeren, wordt de volledige referentietabel (A1:B5) geëxtraheerd.

Referentietabel: De volgende tabel zal worden gebruikt als referentietabel voor alle voorbeelden van de INDEX-functie. De eerste cel bevindt zich op B3 (FOOD) en de laatste diagonale cel bevindt zich op F10 (180).

Voorbeelden: Hieronder vindt u enkele voorbeelden van indexfuncties.

Zaak 1: Er worden geen rijen en kolommen vermeld.

Invoercommando: =INDEX(B3:C10)

Geval 2: Er worden alleen rijen vermeld.

Invoercommando: =INDEX(B3:C10,2)

Geval 3: Zowel rijen als kolommen worden vermeld.

Invoercommando: =INDEX(B3:D10,4,2)

Geval 4: Er worden alleen kolommen vermeld.

Invoercommando: =INDEX(B3 : D10 , , 2)

Probleem met INDEX-functie: Het probleem met de INDEX-functie is dat het nodig is rijen en kolommen op te geven voor de gegevens waarnaar we op zoek zijn. Laten we aannemen dat we te maken hebben met een machine learning-dataset van 10.000 rijen en kolommen, dan zal het erg moeilijk zijn om de gegevens te zoeken en te extraheren waarnaar we op zoek zijn. Hier komt het concept van de Match-functie, die rijen en kolommen identificeert op basis van een bepaalde voorwaarde.

MATCH-functie

Het haalt de positie van een item/waarde in een bereik op. Het is een minder verfijnde versie van VLOOKUP of HLOOKUP die alleen de locatie-informatie retourneert en niet de daadwerkelijke gegevens. MATCH is niet hoofdlettergevoelig en het maakt niet uit of het bereik horizontaal of verticaal is.

Syntaxis:

=MATCH(search_key, range, [search_type])>

Parameters:

search_key: De waarde waarnaar moet worden gezocht. Bijvoorbeeld 42, Katten of I24. bereik: de eendimensionale array waarin moet worden gezocht. Het kan een enkele rij of een enkele kolom zijn. Bijvoorbeeld->A1:A10 , A2:D2 etc. search_type [optioneel]: De zoekmethode. = 1 (standaard) vindt de grootste waarde kleiner dan of gelijk aan zoeksleutel wanneer het bereik in oplopende volgorde wordt gesorteerd.
• = 0 vindt de exacte waarde als het bereik ongesorteerd is.
• = -1 vindt de kleinste waarde groter dan of gelijk aan zoeksleutel wanneer het bereik in aflopende volgorde wordt gesorteerd.

Rijnummer of kolomnummer kunnen worden gevonden met behulp van de matchfunctie en kunnen worden gebruikt in de indexfunctie. Als er dus details over een item zijn, kan alle informatie over het item worden geëxtraheerd door de rij/kolom van het item te vinden met behulp van match. en vervolgens in de indexfunctie nesten.

Referentietabel: De volgende tabel zal worden gebruikt als referentietabel voor alle voorbeelden van de MATCH-functie. De eerste cel bevindt zich op B3 (FOOD) en de laatste diagonale cel bevindt zich op F10 (180)

Voorbeelden: Hieronder staan ​​enkele voorbeelden van de MATCH-functie-

Zaak 1: Zoektype 0, dit betekent exacte match.

Invoercommando: =MATCH(Zuid-Indiaas,C3:C10,0)

Geval 2: Zoektype 1 (standaard).

Invoercommando: =MATCH(Zuid-Indiaas,C3:C10)

java nulcontrole

Geval 3: Zoektype -1.

Invoercommando: =MATCH(Zuid-Indiaas,C3:C10,-1)

INDEX-MATCH Samen

In de voorgaande voorbeelden werden de statische waarden van rijen en kolommen opgegeven in de INDEX-functie. Laten we aannemen dat er geen voorkennis is over de positie van de rijen en kolommen, dan kunnen de posities van rijen en kolommen worden opgegeven met behulp van de functie MATCH. Dit is een dynamische manier om waarde te zoeken en te extraheren.

Syntaxis:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Referentietabel: Er zal gebruik worden gemaakt van de volgende referentietabel. De eerste cel bevindt zich op B3 (FOOD) en de laatste diagonale cel bevindt zich op F10 (180)

Voorbeeld: Laten we zeggen dat het de taak is om de kosten van Masala Dosa te vinden. Het is bekend dat kolom 3 de kosten van artikelen weergeeft, maar de rijpositie van Masala Dosa is niet bekend. Het probleem kan in twee stappen worden verdeeld:

Stap 1: Vind de positie van Masala Dosa met behulp van de formule:

 =MATCH('Masala Dosa',B3:B10,0)>

Hier vertegenwoordigt B3:B10 Kolomvoedsel en 0 betekent Exacte overeenkomst. Het retourneert het rijnummer van Masala Dosa.

Stap 2: Vind de kosten van Masala Dosa. Gebruik de INDEX-functie om de kosten van Masala Dosa te vinden. Door de bovenstaande MATCH-functiequery te vervangen binnen de INDEX-functie op de plaats waar de exacte positie van Masala Dosa vereist is, en het kolomnummer van de kosten 3 is, wat al bekend is.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Twee manieren opzoeken met INDEX-MATCH samen

In het vorige voorbeeld was de kolompositie van het attribuut Kosten hardgecodeerd. Het was dus niet volledig dynamisch.

Zaak 1: Laten we aannemen dat er ook geen kennis bestaat over het kolomnummer van Kosten, dan kan dit worden verkregen met behulp van de formule:

 =MATCH('Cost',B3:F3,0)>

Hier vertegenwoordigt B3:F3 de kopkolom.

Geval 2: Wanneer zowel de rij- als de kolomwaarde worden opgegeven via de MATCH-functie (zonder een statische waarde op te geven), wordt dit Two-Way Lookup genoemd. Het kan worden bereikt met behulp van de formule:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Links opzoeken

Een van de belangrijkste voordelen van INDEX en MATCH ten opzichte van de functie VERT.ZOEKEN is de mogelijkheid om links op te zoeken. Dit betekent dat het mogelijk is om de rijpositie van een item te extraheren uit elk attribuut aan de rechterkant, en dat de waarde van een ander attribuut aan de linkerkant kan worden geëxtraheerd.

Laten we bijvoorbeeld zeggen dat we voedsel kopen waarvan de kosten 140 Rs moeten zijn. Indirect zeggen we: koop Biryani. In dit voorbeeld zijn de kosten Rs 140/- bekend, er is behoefte om het voedsel te extraheren. Omdat de kolom Kosten rechts van de kolom Voedsel is geplaatst. Als VERT.ZOEKEN wordt toegepast, kan er niet in de linkerkant van de kolom Kosten worden gezocht. Daarom is het met VERT.ZOEKEN niet mogelijk om de Voedselnaam op te halen.

Om dit nadeel te ondervangen, kan de INDEX-MATCH-functie Links opzoeken worden gebruikt.
Stap 1: Extraheer eerst de rijpositie van Cost 140 Rs met behulp van de formule:

 =MATCH(140, D3:D10,0)>

Hier vertegenwoordigt D3: D10 de kolom Kosten waarin wordt gezocht naar het rijnummer Kosten 140 Rs.

Stap 2: Nadat u het rijnummer heeft verkregen, is de volgende stap het gebruik van de INDEX-functie om de voedselnaam te extraheren met behulp van de formule:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Hier vertegenwoordigt B3:B10 de voedselkolom en 140 de kosten van het voedselartikel.

Hoofdlettergevoelig opzoeken

Op zichzelf is de MATCH-functie niet hoofdlettergevoelig. Dit betekent dat als er een Voedselnaam DHOKLA is en de MATCH-functie wordt gebruikt met het volgende zoekwoord:

1. Dhokla
2. dhokla
3. DhOkLA

Ze geven allemaal de rijpositie van DHOKLA terug. De functie EXACT kan echter worden gebruikt met INDEX en MATCH om een ​​zoekopdracht uit te voeren waarbij zowel hoofdletters als kleine letters worden gebruikt.

Exacte functie: De Excel EXACT-functie vergelijkt twee tekstreeksen, rekening houdend met hoofdletters en kleine letters, en retourneert TRUE als ze hetzelfde zijn, en FALSE als dat niet het geval is. EXACT is hoofdlettergevoelig.

Voorbeelden:

EXACT(DHOKLA,DHOKLA): Dit retourneert Waar. EXACT(DHOKLA,Dhokla): Dit retourneert False. EXACT(DHOKLA,dhokla): Dit retourneert False. EXACT(DHOKLA,DhOkLA): Dit retourneert False.

Voorbeeld: Laten we zeggen dat het de taak is om naar het type voedsel Dhokla te zoeken, maar dan op een hoofdlettergevoelige manier. Dit kan gedaan worden met behulp van de formule

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Hier retourneert de functie EXACT True als de waarde in kolom B3:B10 overeenkomt met Dhokla met hetzelfde hoofdlettergebruik, anders retourneert deze False. Nu wordt de MATCH-functie toegepast in kolom B3:B10 en wordt gezocht naar een rij met de exacte waarde WAAR. Daarna haalt de INDEX-functie de waarde op van kolom C3:C10 (Voedseltypekolom) in de rij die wordt geretourneerd door de MATCH-functie.

Meerdere criteria opzoeken

Een van de lastigste problemen in Excel is een zoekopdracht op basis van meerdere criteria. Met andere woorden: een zoekopdracht die in meer dan één kolom tegelijk overeenkomt. In het onderstaande voorbeeld worden de INDEX- en MATCH-functie en Booleaanse logica gebruikt om op 3 kolommen te matchen:

1. Voedsel.
2. Kosten.
3. Hoeveelheid.

Om de totale kosten te extraheren.

Voorbeeld: Laten we zeggen dat het de taak is om de totale kosten van Pasta waar te berekenen

Voedsel: Pasta. Kosten: 60. Aantal: 1.

In dit voorbeeld zijn er dus drie criteria om een ​​Match uit te voeren. Hieronder vindt u de stappen voor het zoeken op basis van meerdere criteria-

Stap 1: Match eerst de Voedselkolom (B3:B10) met Pasta met behulp van de formule:

 'PASTA' = B3:B10>

Hierdoor worden B3:B10-waarden (Voedselkolom) geconverteerd naar Boolean. Dat is waar als eten pasta is, anders is het niet waar.

Stap 2: Match daarna de kostencriteria op de volgende manier:

 60 = D3:D10>

Hiermee worden D3:D10-waarden (kostenkolom) vervangen als Boolean. Dat is waar, waar kosten = 60, anders onwaar.

Stap 3: De volgende stap is om op de volgende manier aan het derde criterium, dat Aantal = 1 is, te voldoen:

 1 = E3:E10>

Dit vervangt kolom E3:E10 (kolom Hoeveelheid) als Waar, waarbij Aantal = 1, anders is het Onwaar.

Stap 4: Vermenigvuldig het resultaat van het eerste, tweede en derde criterium. Dit is het snijpunt van alle voorwaarden en converteert Boolean True/False naar 1/0.

Stap 5: Het resultaat is nu een kolom met 0 en 1. Gebruik hier de MATCH-functie om het rijaantal kolommen te vinden die 1 bevatten. Want als een kolom de waarde 1 heeft, betekent dit dat deze aan alle drie de criteria voldoet.

Stap 6: Nadat u het rijnummer heeft opgehaald, gebruikt u de INDEX-functie om de totale kosten van die rij te krijgen.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Hier vertegenwoordigt F3:F10 de kolom Totale kosten.