INDEX & MATCH Functies Combo in Excel (10 eenvoudige voorbeelden)

Excel heeft veel functies - ongeveer 450+ van hen.

En veel van deze zijn gewoon geweldig. De hoeveelheid werk die je kunt doen met een paar formules verbaast me nog steeds (zelfs na 10+ jaar Excel te hebben gebruikt).

En tussen al deze geweldige functies valt de combinatie van INDEX MATCH-functies op.

Ik ben een grote fan van de INDEX MATCH combo en ik heb het al vaak duidelijk gemaakt.

Ik heb zelfs een artikel geschreven over Index Match Vs VERT.ZOEKEN, wat tot wat discussie leidde (je kunt de opmerkingensectie bekijken voor wat vuurwerk).

En vandaag schrijf ik dit artikel dat uitsluitend is gericht op Index Match om u enkele eenvoudige en geavanceerde scenario's te laten zien waarin u deze krachtige formulecombinatie kunt gebruiken en het werk gedaan kunt krijgen.

Opmerking: Er zijn andere opzoekformules in Excel, zoals VERT.ZOEKEN en HORIZ.ZOEKEN en deze zijn geweldig. Veel mensen vinden VERT.ZOEKEN gemakkelijker te gebruiken (en dat is ook waar). Ik geloof dat INDEX MATCH in veel gevallen een betere optie is. Maar omdat mensen het moeilijk vinden, wordt het minder gebruikt. Dus ik probeer het te vereenvoudigen met behulp van deze tutorial.

Voordat ik u laat zien hoe de combinatie van INDEX MATCH de wereld van analisten en datawetenschappers verandert, wil ik u eerst kennis laten maken met de afzonderlijke onderdelen - INDEX- en MATCH-functies.

INDEX-functie: vindt de waarde op basis van coördinaten

De gemakkelijkste manier om te begrijpen hoe de Index-functie werkt, is door het te zien als een GPS-satelliet.

Zodra u de satelliet de coördinaten voor de lengte- en breedtegraad vertelt, weet hij precies waar hij heen moet om die locatie te vinden.

Dus ondanks het verbijsterende aantal lat-long combinaties, zou de satelliet precies weten waar hij moet kijken.

Ik ging snel op zoek naar mijn werklocatie en dit is wat ik kreeg.

Maar goed, genoeg aardrijkskunde.

Net zoals een satelliet breedte- en lengtecoördinaten nodig heeft, heeft de INDEX-functie in Excel het rij- en kolomnummer nodig om te weten naar welke cel u verwijst.

En dat is de Excel INDEX-functie in een notendop.

Dus laat me het in eenvoudige bewoordingen voor je definiëren.

De INDEX-functie gebruikt het rijnummer en kolomnummer om een ​​cel in het opgegeven bereik te vinden en de waarde erin terug te geven.

Op zich is INDEX een heel eenvoudige functie, zonder hulpprogramma. In de meeste gevallen kent u de rij- en kolomnummers immers niet.

Maar…

Het feit dat je het kunt gebruiken met andere functies (hint: MATCH) die het rijnummer en het kolomnummer kunnen vinden, maakt INDEX tot een extreem krachtige Excel-functie.

Hieronder staat de syntaxis van de INDEX-functie:

=INDEX (matrix, rij_num, [col_num]) =INDEX (matrix, rij_num, [col_num], [area_num])

• reeks - een celbereik of een matrixconstante.
• rij_num - het rijnummer waaruit de waarde moet worden opgehaald.
• [col_num] - het kolomnummer waaruit de waarde moet worden opgehaald. Hoewel dit een optioneel argument is, maar als row_num niet is opgegeven, moet het worden opgegeven.
• [gebied_num] - (Optioneel) Als het array-argument uit meerdere bereiken bestaat, wordt dit nummer gebruikt om de verwijzing uit alle bereiken te selecteren.

INDEX-functie heeft 2 syntaxis (alleen ter informatie).

De eerste wordt in de meeste gevallen gebruikt. De tweede wordt alleen gebruikt in geavanceerde gevallen (zoals het doen van een zoekactie in drie richtingen), die we later in deze zelfstudie in een van de voorbeelden zullen behandelen.

Maar als deze functie nieuw voor u is, onthoud dan de eerste syntaxis.

Hieronder vindt u een video waarin wordt uitgelegd hoe u de INDEX-functie gebruikt

MATCH-functie: vindt de positie op basis van een opzoekwaarde

Terugkomend op mijn vorige voorbeeld van lengte- en breedtegraad, is MATCH de functie die deze posities kan vinden (in de Excel-spreadsheetwereld).

In eenvoudige taal kan de Excel MATCH-functie de positie van een cel in een bereik vinden.

En op welke basis zou het de positie van een cel vinden?

Op basis van de opzoekwaarde.

Als u bijvoorbeeld een lijst heeft zoals hieronder weergegeven en u wilt de positie van de naam 'Mark' daarin vinden, dan kunt u de MATCH-functie gebruiken.

De functie retourneert 3, want dat is de positie van de cel met de naam Mark erin.

MATCH-functie begint van boven naar beneden te zoeken naar de opzoekwaarde (die 'Mark' is) in het opgegeven bereik (wat in dit voorbeeld A1:A9 is). Zodra het de naam vindt, retourneert het de positie in dat specifieke bereik.

Hieronder vindt u de syntaxis van de MATCH-functie in Excel.

=VERGELIJKEN(zoekwaarde, zoekarray, [overeenkomst_type])

• opzoekwaarde - De waarde waarvoor u een overeenkomst zoekt in de lookup_array.
• lookup_array - Het celbereik waarin u zoekt naar de lookup_value.
• [match_type] - (Optioneel) Dit geeft aan hoe Excel moet zoeken naar een overeenkomende waarde. Het kan drie waarden aannemen -1, 0 of 1.

Overeenkomsttypeargument in MATCH-functie begrijpen

Er is nog iets dat u moet weten over de MATCH-functie, en het gaat over hoe deze door de gegevens gaat en de celpositie vindt.

Het derde argument van de MATCH-functie kan 0, 1 of -1 zijn.

Hieronder wordt uitgelegd hoe deze argumenten werken:

• 0 - er wordt gezocht naar een exacte overeenkomst met de waarde. Als een exacte overeenkomst wordt gevonden, retourneert de MATCH-functie de celpositie. Anders wordt er een fout geretourneerd.
• 1 - dit vindt de grootste waarde die kleiner is dan of gelijk is aan de opzoekwaarde. Om dit te laten werken, moet uw gegevensbereik in oplopende volgorde worden gesorteerd.
• -1 - dit vindt de kleinste waarde die groter is dan of gelijk is aan de opzoekwaarde. Om dit te laten werken, moet uw gegevensbereik in aflopende volgorde worden gesorteerd.

Hieronder vindt u een video waarin wordt uitgelegd hoe u de MATCH-functie gebruikt (samen met het argument voor het matchtype)

Om het samen te vatten en in eenvoudige bewoordingen te zeggen:

• INDEX heeft de celpositie nodig (rij- en kolomnummer) en geeft de celwaarde.
• MATCH vindt de positie met behulp van een opzoekwaarde.

Laten we ze combineren om een ​​krachtpatser te creëren (INDEX + MATCH)

Nu je een basiskennis hebt van hoe de INDEX- en MATCH-functies afzonderlijk werken, laten we deze twee combineren en leren over alle geweldige dingen die het kan doen.

Om dit beter te begrijpen, heb ik een paar voorbeelden die de combinatie INDEX MATCH gebruiken.

Ik zal beginnen met een eenvoudig voorbeeld en u vervolgens ook enkele geavanceerde use-cases laten zien.

Klik hier om het voorbeeldbestand te downloaden

Voorbeeld 1: Een eenvoudige opzoeking met INDEX MATCH Combo

Laten we een eenvoudige opzoeking doen met INDEX/MATCH.

Hieronder staat een tabel waarin ik de cijfers voor tien leerlingen heb.

Van deze tabel wil ik de cijfers voor Jim vinden.

Hieronder is de formule die dit gemakkelijk kan doen:

=INDEX($A$2:$B$11,MATCH("Jim",$A$2:$A$11,0),2)

Nu, als je denkt dat dit gemakkelijk kan worden gedaan met behulp van een VERT.ZOEKEN-functie, dan heb je gelijk! Dit is niet het beste gebruik van INDEX MATCH awesomeness. Ondanks dat ik een fan ben van INDEX MATCH, is het iets moeilijker dan VERT.ZOEKEN. Als het ophalen van gegevens uit een kolom aan de rechterkant alles is wat u wilt doen, raad ik u aan VERT.ZOEKEN te gebruiken.

De reden dat ik dit voorbeeld heb laten zien, dat ook gemakkelijk kan worden gedaan met VERT.ZOEKEN, is om u te laten zien hoe INDEX MATCH werkt in een eenvoudige setting.

Laat me nu een voordeel van INDEX MATCH laten zien.

Stel dat u dezelfde gegevens hebt, maar in plaats van in kolommen, maar in rijen (zoals hieronder weergegeven).

Weet je wat, je kunt nog steeds de INDEX MATCH-combo gebruiken om Jim's punten te krijgen.

Hieronder is de formule die u het resultaat zal geven:

=INDEX($B$1:$K$2,2,MATCH(“Jim”,$B$1:$K$1,0))

Merk op dat u het bereik moet wijzigen en de rij-/kolomdelen moet wisselen om deze formule ook voor horizontale gegevens te laten werken.

Dit kan niet worden gedaan met VERT.ZOEKEN, maar u kunt dit nog steeds gemakkelijk doen met HORIZ.ZOEKEN.

INDEX MATCH-combinatie kan zowel horizontale als verticale gegevens gemakkelijk verwerken.

Klik hier om het voorbeeldbestand te downloaden

Voorbeeld 2: Opzoeken naar links

Het komt vaker voor dan je denkt.

Vaak moet u de gegevens ophalen uit een kolom aan de linkerkant van de kolom met de opzoekwaarde.

Iets zoals hieronder weergegeven:

Om de verkopen van Michael te weten te komen, moet u aan de linkerkant een zoekopdracht uitvoeren.

Als je denkt aan VERT.ZOEKEN, laat me je daar stoppen.

VERT.ZOEKEN is niet gemaakt om de waarden aan de linkerkant te zoeken en op te halen.

Kun je het nog steeds doen met VERT.ZOEKEN?

Ja, dat kan!

Maar dat kan een lange en lelijke formule worden.

Dus als u wilt opzoeken en gegevens wilt ophalen uit de kolommen aan de linkerkant, kunt u beter de INDEX MATCH-combo gebruiken.

Hieronder vindt u de formule waarmee het verkoopnummer van Michael wordt verkregen:

=INDEX($A$2:$C$11,MATCH("Michael",C2:C11,0),2)

Nog een punt hier voor INDEX MATCH. VERT.ZOEKEN kan de gegevens alleen ophalen uit de kolommen rechts van de kolom met de opzoekwaarde.

Voorbeeld 3: Tweerichtingszoeken

Tot nu toe hebben we de voorbeelden gezien waarin we de gegevens wilden ophalen uit de kolom naast de kolom met de opzoekwaarde.

Maar in het echte leven beslaan de gegevens vaak meerdere kolommen.

INDEX MATCH kan gemakkelijk een zoekopdracht in twee richtingen aan.

Hieronder vindt u een dataset van de cijfers van de student in drie verschillende vakken.

Wil je snel de cijfers van een student halen voor alle drie de vakken, dan kan dat met INDEX MATCH.

De onderstaande formule geeft je de cijfers voor Jim voor alle drie de onderwerpen (kopieer en plak in één cel en sleep om andere cellen te vullen of kopieer en plak in andere cellen).

=INDEX($B$2:$D$11,MATCH($F$3,$A$2:$A$11,0),MATCH(G$2,$B$1:$D$1,0))

Laat me deze formule ook snel uitleggen.

INDEX-formule gebruikt B2:D11 als het bereik.

De eerste MATCH gebruikt de naam (Jim in cel F3) en haalt de positie ervan op in de namenkolom (A2:A11). Dit wordt het rijnummer waaruit de gegevens moeten worden opgehaald.

De tweede MATCH-formule gebruikt de onderwerpnaam (in cel G2) om de positie van die specifieke onderwerpnaam in B1:D1 te krijgen. Wiskunde is bijvoorbeeld 1, Natuurkunde is 2 en Scheikunde is 3.

Aangezien deze MATCH-posities worden ingevoerd in de INDEX-functie, wordt de score geretourneerd op basis van de naam van de student en het onderwerp.

Deze formule is dynamisch, wat betekent dat als je de studentennaam of de vaknamen wijzigt, deze nog steeds werkt en de juiste gegevens ophaalt.

Een groot voordeel van het gebruik van INDEX/MATCH is dat zelfs als je de namen van de onderwerpen verwisselt, het je het juiste resultaat blijft geven.

Voorbeeld 4: Opzoekwaarde van meerdere kolommen/criteria

Stel je hebt een dataset zoals hieronder weergegeven en je wilt de markeringen voor ‘Mark Long’ ophalen.

Omdat de gegevens in twee kolommen staan, kan ik Mark niet opzoeken en de gegevens ophalen.

Als ik het op die manier doe, krijg ik de markeringsgegevens voor Mark Frost en niet voor Mark Long (omdat de MATCH-functie me het resultaat geeft voor het MARK waaraan het voldoet).

Een manier om dit te doen is door een hulpkolom te maken en de namen te combineren. Zodra u de helperkolom hebt, kunt u VERT.ZOEKEN gebruiken en de markeringsgegevens ophalen.

Als je geïnteresseerd bent om te leren hoe je dit kunt doen, lees dan deze tutorial over het gebruik van VERT.ZOEKEN met meerdere criteria.

Maar met de combinatie INDEX/MATCH heeft u geen hulpkolom nodig. U kunt een formule maken die meerdere criteria verwerkt in de formule zelf.

De onderstaande formule geeft het resultaat.

=INDEX($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))

Laat me snel uitleggen wat deze formule doet.

Het MATCH-gedeelte van de formule combineert de opzoekwaarde (Mark en Long) en de volledige opzoekarray. Wanneer $A$2:A11&”|”&$B$2:$B$11 wordt gebruikt als de opzoekarray, wordt de opzoekwaarde vergeleken met de gecombineerde string van voor- en achternaam (gescheiden door het pijpsymbool).

Dit zorgt ervoor dat u het juiste resultaat krijgt zonder hulpkolommen.

U kunt dit soort zoekopdrachten (wanneer er meerdere kolommen/criteria zijn) ook met VERT.ZOEKEN doen, maar u moet een hulpkolom gebruiken. INDEX MATCH combo maakt het enigszins gemakkelijk om dit te doen zonder hulpkolommen.

Voorbeeld 5: Waarden ophalen uit hele rij/kolom

In de bovenstaande voorbeelden hebben we de functie INDEX gebruikt om waarde uit een specifieke cel te halen. U geeft het rij- en kolomnummer op en het retourneert de waarde in die specifieke cel.

Maar je kunt meer doen.

U kunt ook de INDEX-functie gebruiken om de waarden uit een hele rij of kolom te halen.

En hoe kan dit nuttig zijn, vraagt ​​u zich af!

Stel dat je de totale score van Jim op alle drie de vakken wilt weten.

U kunt de INDEX-functie gebruiken om eerst alle cijfers van Jim te krijgen en vervolgens de SOM-functie gebruiken om een ​​totaal te krijgen.

Laten we eens kijken hoe dit te doen.

Hieronder heb ik de scores van alle studenten in drie vakken.

De onderstaande formule geeft me de totale score van Jim in alle drie de onderwerpen.

=SOM(INDEX($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0))

Laat me uitleggen hoe deze formule werkt.

De truc hier is om 0 als kolomnummer te gebruiken.

Wanneer u 0 gebruikt als het kolomnummer in de INDEX-functie, worden alle rijwaarden geretourneerd. Evenzo, als u 0 als rijnummer gebruikt, worden alle waarden in de kolom geretourneerd.

Dus het onderstaande deel van de formule retourneert een reeks waarden - {97, 70, 73}

INDEX($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0)

Als u deze bovenstaande formule gewoon in een cel in Excel invoert en op Enter drukt, ziet u een #WAARDE! fout. Dit komt omdat het geen enkele waarde retourneert, maar een reeks waarden.

Maar maak je geen zorgen, de reeks waarden is er nog steeds. U kunt dit controleren door de formule te selecteren en op de F9-toets te drukken. Het toont u het resultaat van de formule, in dit geval een array van drie waarden - {97, 70, 73}

Als u nu deze INDEX-formule in de SOM-functie plaatst, krijgt u de som van alle door Jim gescoorde punten.

Je kunt hetzelfde ook gebruiken om de hoogste, laagste en gemiddelde cijfers van Jim te krijgen.

Net zoals we dit voor een student hebben gedaan, kun je dit ook voor een vak doen. Als u bijvoorbeeld de gemiddelde score in een onderwerp wilt, kunt u het rijnummer op 0 houden in de INDEX-formule en krijgt u alle kolomwaarden van dat onderwerp.

Klik hier om het voorbeeldbestand te downloaden

Voorbeeld 6: Vind het cijfer van de student (geschatte matchtechniek)

Tot nu toe hebben we de MATCH-formule gebruikt om de exacte overeenkomst van de opzoekwaarde te krijgen.

Maar je kunt het ook gebruiken om een ​​geschatte match te maken.

Wat is in godsnaam Approximate Match?

Laat het me uitleggen.

Wanneer u op zoek bent naar dingen zoals namen of ID's, bent u op zoek naar een exacte overeenkomst. Maar soms moet u het bereik weten waarin uw opzoekwaarden liggen. Dit is meestal het geval bij cijfers.

Als klassenleraar wil je bijvoorbeeld misschien weten wat het cijfer is van elke leerling voor een vak, en het cijfer wordt bepaald op basis van de score.

Hieronder is een voorbeeld, waarbij ik het cijfer voor alle studenten wil en de beoordeling wordt bepaald op basis van de tabel aan de rechterkant.

Dus als een leerling minder dan 33 krijgt, is het cijfer F en als hij/zij minder dan 50 maar meer dan 33 krijgt, is het E, enzovoort.

Hieronder is de formule die dit zal doen.

=INDEX($F$3:$F$8,MATCH(B2,$E$3:$E$8,1),1)

Laat me uitleggen hoe deze formule werkt.

In de MATCH-functie hebben we 1 gebruikt als het [match_type]-argument. Dit argument retourneert de grootste waarde die kleiner is dan of gelijk is aan de opzoekwaarde.

Dit betekent dat de MATCH-formule door het markeringsbereik gaat, en zodra het een markeringsbereik vindt dat gelijk is aan of kleiner is dan de opzoekmarkeringswaarde, stopt het daar en keert het terug naar zijn positie.

Dus als de opzoekmarkeringswaarde 20 is, zou de MATCH-functie 1 retourneren en als deze 85 is, zou deze 5 opleveren.

En de INDEX-functie gebruikt deze positiewaarde om het cijfer te krijgen.

BELANGRIJK: om dit te laten werken, moeten uw gegevens in oplopende volgorde worden gesorteerd. Als dit niet het geval is, kunt u verkeerde resultaten krijgen.

Merk op dat het bovenstaande ook kan worden gedaan met behulp van de onderstaande VERT.ZOEKEN-formule:

=VERT.ZOEKEN(B2,$E$3:$F$8,2,TRUE)

Maar de MATCH-functie kan een stap verder gaan als het gaat om geschatte match.

U kunt ook aflopende gegevens hebben en de INDEX MATCH-combo gebruiken om het resultaat te vinden. Als ik bijvoorbeeld de volgorde van de cijfertabel wijzig (zoals hieronder weergegeven), kan ik de cijfers van de studenten nog steeds vinden.

Om dit te doen, hoef ik alleen het argument [match_type] te wijzigen in -1.

Hieronder de formule die ik heb gebruikt:

=INDEX($F$3:$F$8,MATCH(B2,$E$3:$E$8,-1),1)

VERT.ZOEKEN kan ook bij benadering overeenkomen, maar alleen wanneer gegevens in oplopende volgorde zijn gesorteerd (maar het werkt niet als de gegevens in aflopende volgorde zijn gesorteerd).

Voorbeeld 7: Hoofdlettergevoelige zoekopdrachten

Tot nu toe zijn alle zoekopdrachten die we hebben gedaan niet hoofdlettergevoelig.

Dit betekent dat het niet uitmaakte of de opzoekwaarde Jim of JIM of jim was. Je krijgt hetzelfde resultaat.

Maar wat als u wilt dat de zoekopdracht hoofdlettergevoelig is.

Dit is meestal het geval als je grote datasets hebt en de mogelijkheid van herhaling of verschillende namen/id's (met als enige verschil het geval)

Stel dat ik de volgende dataset van studenten heb waarbij er twee studenten zijn met de naam Jim (het enige verschil is dat de ene wordt ingevoerd als Jim en de andere als jim).

Merk op dat er twee studenten zijn met dezelfde naam - Jim (cel A2 en A5).

Aangezien een normale zoekopdracht niet zou werken, moet u een hoofdlettergevoelige zoekopdracht uitvoeren.

Hieronder vindt u de formule die u het juiste resultaat geeft. Aangezien dit een matrixformule is, moet u Control + Shift + Enter gebruiken.

=INDEX($B$2:$B$11,MATCH(TRUE,EXACT(D3,A2:A11),0),1)

Laat me uitleggen hoe deze formule werkt.

De EXACT-functie controleert of de opzoekwaarde exact overeenkomt (in dit geval 'jim'). Het gaat door alle namen en retourneert ONWAAR als het geen overeenkomst is en WAAR als het een overeenkomst is.

Dus de uitvoer van de functie EXACT in dit voorbeeld is - {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Merk op dat er maar één TRUE is, en dat is wanneer de EXACT-functie een perfecte match heeft gevonden.

De MATCH-functie vindt vervolgens de positie van TRUE in de array die wordt geretourneerd door de EXACT-functie, die in dit voorbeeld 4 is.

Zodra we de positie hebben, gebruikt de INDEX-functie deze om de markeringen te vinden.

Voorbeeld 8: Vind de dichtstbijzijnde match

Laten we nu een beetje gevorderd worden.

Stel dat u een dataset heeft waarin u de persoon wilt vinden met de werkervaring die het dichtst bij de vereiste ervaring ligt (vermeld in cel D2).

Hoewel opzoekformules niet zijn gemaakt om dit te doen, kunt u het combineren met andere functies (zoals MIN en ABS) om dit voor elkaar te krijgen.

Hieronder vindt u de formule waarmee de persoon met de ervaring die het dichtst bij de vereiste ervaring ligt, wordt gevonden en de naam van de persoon wordt geretourneerd. Merk op dat de ervaring het dichtst bij moet zijn (wat minder of meer kan zijn).

=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Aangezien dit een matrixformule is, moet u Control + Shift + Enter gebruiken.

De truc in deze formule is om de opzoekwaarde en opzoekarray te wijzigen om het minimale ervaringsverschil in vereiste en werkelijke waarden te vinden.

Laten we, voordat ik de formule uitleg, begrijpen hoe u dit handmatig zou doen.

Je gaat door elke cel in kolom B en vindt het verschil in de ervaring tussen wat nodig is en degene die een persoon heeft. Als je eenmaal alle verschillen hebt, zul je degene vinden die minimaal is en de naam van die persoon ophalen.

Dat is precies wat we doen met deze formule.

Laat het me uitleggen.

De opzoekwaarde in de MATCH-formule is MIN(ABS(D2-B2:B15)).

Dit deel geeft je het minimale verschil tussen de gegeven ervaring (die 2,5 jaar is) en alle andere ervaringen. In dit voorbeeld retourneert het 0.3

Merk op dat ik ABS heb gebruikt om ervoor te zorgen dat ik op zoek ben naar de dichtstbijzijnde (wat meer of minder kan zijn dan de gegeven ervaring).

Nu wordt deze minimumwaarde onze opzoekwaarde.

De opzoekarray in de MATCH-functie is ABS(D2-$B$2:$B$15).

Dit geeft ons een reeks getallen waarvan 2,5 (de vereiste ervaring) is afgetrokken.

Dus nu hebben we een opzoekwaarde (0,3) en een opzoekarray ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

MATCH-functie vindt de positie van 0.3 in deze array, wat ook de positie is van de naam van de persoon die de dichtstbijzijnde ervaring heeft.

Dit positienummer wordt vervolgens gebruikt door de INDEX-functie om de naam van de persoon te retourneren.

Gerelateerd lezen: Zoek de dichtstbijzijnde overeenkomst in Excel (voorbeelden met opzoekformules)

Klik hier om het voorbeeldbestand te downloaden

Voorbeeld 9: Gebruik INDEX MATCH met jokertekens

Als u een waarde wilt opzoeken wanneer er een gedeeltelijke overeenkomst is, moet u jokertekens gebruiken.

Hieronder vindt u bijvoorbeeld een dataset van de bedrijfsnaam en hun marktkapitalisaties en u wilt de marktkapitalisatie weten. gegevens voor de drie bedrijven aan de rechterkant.

Aangezien dit geen exacte overeenkomsten zijn, kunt u in dit geval niet regelmatig opzoeken.

Maar u kunt nog steeds de juiste gegevens krijgen door een asterisk (*) te gebruiken, een jokerteken.

Hieronder vindt u de formule die u de gegevens geeft door de bedrijfsnamen uit de hoofdkolom te matchen en het marktkapitalisatiecijfer ervoor op te halen.

=INDEX($B$2:$B$10,MATCH(D2&”*”,$A$2:$A$10,0),1)

Laat me uitleggen hoe deze formule werkt.

Omdat er geen exacte overeenkomst is met opzoekwaarden, heb ik gebruikt D2&”*” als de opzoekwaarde in de MATCH-functie.

Een asterisk is een jokerteken dat een willekeurig aantal tekens vertegenwoordigt. Dit betekent dat Apple* in de formule elke tekenreeks betekent die begint met het woord Apple en een willekeurig aantal tekens erachter kan hebben.

Dus wanneer Appel* wordt gebruikt als de opzoekwaarde en de MATCH-formule zoekt ernaar in kolom A, het retourneert de positie van 'Apple Inc.', aangezien het begint met het woord Apple.

U kunt ook jokertekens gebruiken om tekstreeksen te vinden waar de opzoekwaarde tussenin ligt. Als u bijvoorbeeld *Apple* als opzoekwaarde gebruikt, zal het elke tekenreeks vinden waar het woord appel ergens in voorkomt.

Opmerking: deze techniek werkt goed wanneer u slechts één exemplaar van overeenkomsten hebt. Maar als u meerdere overeenkomende instanties hebt (bijvoorbeeld Apple Inc en Apple Corporation), dan zou de MATCH-functie alleen de positie van de eerste overeenkomende instantie retourneren.

Voorbeeld 10: Opzoeken met drie richtingen

Dit is een geavanceerd gebruik van INDEX MATCH, maar ik zal het nog steeds behandelen om u de kracht van deze combinatie te laten zien.

Onthoud dat ik zei dat de INDEX-functie twee syntaxis heeft:

=INDEX (matrix, rij_num, [col_num]) =INDEX (matrix, rij_num, [col_num], [area_num])

Tot nu toe hebben we in al onze voorbeelden alleen de eerste gebruikt.

Maar voor een zoekactie in drie richtingen moet u de tweede syntaxis gebruiken.

Laat me eerst uitleggen wat een three-way look betekent.

In een tweerichtingszoekopdracht gebruiken we de INDEX MATCH-formule om de cijfers te krijgen wanneer we de naam van de student en de naam van het onderwerp hebben. Het ophalen van de tekens van Jim in Math is bijvoorbeeld een zoekopdracht in twee richtingen.

Een three-way look zou er nog een dimensie aan toevoegen. Stel dat u een gegevensset heeft zoals hieronder weergegeven en u wilt weten wat de score is van Jim in wiskunde in het tussentijdse examen, dan is dit een opzoekfunctie in drie richtingen.

Hieronder staat de formule die het resultaat zal geven.

=INDEX(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),MATCH($F$5,$A$3:$A$7,0),MATCH(G$4 ,$B$2:$D$2,0),(IF(G$3="Eenheidstest",1,IF(G$3="Midden termijn",2,3))))

De bovenstaande formule controleerde drie dingen: de naam van de student, het onderwerp en het examen. Nadat het de juiste waarde heeft gevonden, retourneert het deze in de cel.

Laat me uitleggen hoe deze formule werkt door de formule in delen op te splitsen.

• reeks - ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23): In plaats van een enkele array te gebruiken, heb ik in dit geval drie arrays tussen haakjes gebruikt.
• row_num - MATCH($F$5,$A$3:$A$7,0): MATCH-functie wordt gebruikt om de positie van de naam van de student te vinden in cel $F$5 in de lijst met de naam van de student.
• col_num - MATCH(G$4,$B$2:$D$2,0): MATCH-functie wordt gebruikt om de positie van de onderwerpnaam in cel $B$2 in de lijst met de naam van het onderwerp te vinden.
• [area_num] - IF(G$3=”Eenheidstest”,1,IF(G$3=”Midden termijn”,2,3)): De oppervlaktenummerwaarde vertelt de INDEX-functie welke van de drie arrays moet worden gebruikt om de waarde op te halen. Als het onderzoek Unit Term is, zou de IF-functie 1 retourneren en zou de INDEX-functie de eerste array gebruiken om de waarde op te halen. Als het examen halverwege de looptijd is, zou de IF-formule 2 opleveren, anders 3 als resultaat.

Dit is een geavanceerd voorbeeld van het gebruik van INDEX MATCH, en het is onwaarschijnlijk dat u een situatie zult vinden waarin u dit moet gebruiken. Maar het is nog steeds goed om te weten wat Excel-formules kunnen doen.

Klik hier om het voorbeeldbestand te downloaden

Waarom is INDEX/MATCH beter dan VERT.ZOEKEN?

Of is het?

Ja, dat is het - in de meeste gevallen.

Ik zal mijn zaak zo dadelijk presenteren.

Maar voordat ik dat doe, wil ik dit zeggen - VERT.ZOEKEN is een uiterst nuttige functie en ik ben er dol op. Het kan veel in Excel en ik gebruik het zelf ook af en toe. Dat gezegd hebbende, betekent dit niet dat er niets beters kan zijn, en INDEX/MATCH (met meer flexibiliteit en functionaliteiten) is beter.

Dus als u wat basiszoekopdrachten wilt uitvoeren, kunt u beter VERT.ZOEKEN gebruiken.

INDEX/MATCH is VERT.ZOEKEN op steroïden. En als je eenmaal INDEX/MATCH hebt geleerd, wil je er misschien altijd de voorkeur aan geven om het te gebruiken (vooral vanwege de flexibiliteit die het heeft).

Zonder het te ver uit te rekken, wil ik je snel de redenen geven waarom INDEX/MATCH beter is dan VERT.ZOEKEN.

INDEX/MATCH kan naar links (en ook naar rechts) van de opzoekwaarde kijken

Ik heb het behandeld in een van de bovenstaande voorbeelden.

Als u een waarde heeft die links van de opzoekwaarde staat, kunt u dat niet doen met VERT.ZOEKEN

Tenminste niet met alleen VERT.ZOEKEN.

Ja, je kunt VERT.ZOEKEN combineren met andere formules en het voor elkaar krijgen, maar het wordt ingewikkeld en rommelig.

INDEX/MATCH daarentegen is gemaakt om overal op te zoeken (of het nu links, rechts, omhoog of omlaag is)

INDEX/MATCH kan werken met verticale en horizontale bereiken

Nogmaals, met volledig respect voor VERT.ZOEKEN, het is niet gemaakt om dit te doen.

De V in VERT.ZOEKEN staat immers voor verticaal.

VERT.ZOEKEN kan alleen door gegevens gaan die verticaal zijn, terwijl INDEX/MATCH gegevens zowel verticaal als horizontaal kan doorlopen.

Natuurlijk is er de HORIZ.ZOEKEN-functie om voor horizontaal zoeken te zorgen, maar het is dan niet VERT.ZOEKEN … toch?

Ik vind het leuk dat de INDEX MATCH-combo flexibel genoeg is om met zowel verticale als horizontale gegevens te werken.

VERT.ZOEKEN werkt niet met aflopende gegevens

Als het gaat om de geschatte overeenkomst, bevinden VERT.ZOEKEN en INDEX/MATCH zich op hetzelfde niveau.

Maar INDEX MATCH neemt het punt omdat het ook gegevens in aflopende volgorde kan verwerken.

Ik laat dit zien in een van de voorbeelden in deze tutorial waar we het cijfer van studenten moeten vinden op basis van de beoordelingstabel. Als de tabel in aflopende volgorde is gesorteerd, zou VERT.ZOEKEN niet werken (maar INDEX MATCH wel).

INDEX/MATCH kan iets sneller zijn

Ik zal eerlijk zijn. Ik heb deze test niet zelf gedaan.

Ik vertrouw op de wijsheid van een Excel-meester - Charley Kyd.

Het verschil in snelheid in VLOOKUP en INDEX/MATCH is nauwelijks merkbaar als je kleine datasets hebt. Maar als u duizenden rijen en veel kolommen heeft, kan dit een beslissende factor zijn.

In zijn artikel stelt Charley Kyd:

“In het slechtste geval is de INDEX-MATCH-methode ongeveer net zo snel als VERT.ZOEKEN; op zijn best is het veel sneller.”

INDEX/MATCH is onafhankelijk van de werkelijke kolompositie

Als je een dataset hebt zoals hieronder weergegeven terwijl je de score van Jim in Physics ophaalt, kun je dat doen met VERT.ZOEKEN.

En om dat te doen, kunt u het kolomnummer opgeven als 3 in VERT.ZOEKEN.

Alles is goed.

Maar wat als ik de kolom Math verwijder.

In dat geval wordt de formule VERT.ZOEKEN verbroken.

Waarom? - Omdat het hardcoded was om de derde kolom te gebruiken, en als ik een kolom ertussen verwijder, wordt de derde kolom de tweede kolom.

Het gebruik van INDEX/MATCH is in dit geval beter omdat u het kolomnummer dynamisch kunt maken door MATCH te gebruiken. Dus in plaats van een kolomnummer, controleert het de naam van het onderwerp en gebruikt dat om het kolomnummer te retourneren.

Je kunt dat zeker doen door VERT.ZOEKEN te combineren met MATCH, maar als je het toch combineert, waarom zou je het dan niet doen met INDEX, dat een stuk flexibeler is.

Wanneer u INDEX/MATCH gebruikt, kunt u veilig kolommen in uw dataset invoegen/verwijderen.

Ondanks al deze factoren is er een reden waarom VERT.ZOEKEN zo populair is.

En het is een grote reden.

VERT.ZOEKEN is gemakkelijker te gebruiken

VERT.ZOEKEN heeft slechts een maximum van vier argumenten. Als je je hoofd om deze vier kunt wikkelen, ben je klaar om te gaan.

En aangezien de meeste basiszoekopdrachten ook door VERT.ZOEKEN worden afgehandeld, is het snel de populairste Excel-functie geworden.

Ik noem het de King of Excel-functies.

INDEX/MATCH is daarentegen iets moeilijker te gebruiken. Het kan zijn dat je vastloopt als je het begint te gebruiken, maar voor een beginner is VERT.ZOEKEN veel gemakkelijker uit te leggen en te leren.

En dit is geen nulsomspel.

Dus als je nieuw bent in de opzoekwereld en niet weet hoe je VERT.ZOEKEN moet gebruiken, kun je dat beter leren.

Ik heb een gedetailleerde handleiding over het gebruik van VERT.ZOEKEN in Excel (met veel voorbeelden)

Het is niet mijn bedoeling in dit artikel om twee geweldige functies tegen elkaar op te zetten. Ik wilde je de kracht van INDEX MATCH-combo laten zien en alle geweldige dingen die het kan doen.

Ik hoop dat je dit artikel nuttig vond.

Laat me je mening weten in het opmerkingengedeelte en als je een fout vindt in deze tutorial, laat het me dan weten.