Excel INDEX VERGELIJKEN: Basis- en geavanceerde opzoekacties

In Excel is het vaak essentieel om specifieke gegevens nauwkeurig op te halen. Hoewel de functies INDEX en VERGELIJKEN ieder op zich al krachtig zijn, vormt hun combinatie een nog veelzijdiger toolkit voor gegevensopzoeken. Samen bieden ze een breed scala aan zoekmogelijkheden: van eenvoudige horizontale en verticale opzoeken tot geavanceerde toepassingen zoals tweerichtings-, hoofdlettergevoelige en meercriteria-zoekopdrachten. In vergelijking met VERT.ZOEKEN levert de combinatie van INDEX en VERGELIJKEN uitgebreidere en flexibelere opzoekopties. In deze tutorial verkennen we dieper welke krachtige mogelijkheden deze dynamische combinatie biedt.

• INDEX-functie
• VERGELIJKEN-functie
• INDEX en VERGELIJKEN combineren

• Zoeken in twee richtingen
• Zoeken naar links
• Hoofdlettergevoelig zoeken
• Dichtstbijzijnde overeenkomst
• Zoeken met meerdere criteria
• Zoeken over meerdere kolommen
• Zoeken naar eerste niet-lege waarde
• Zoek naar de eerste numerieke waarde
• Opzoeken van MAX- of MIN-koppelingen
• Tip: Pas uw eigen #N/B-foutmeldingen aan

Hoe INDEX en VERGELIJKEN in Excel te gebruiken

Voordat we de functies INDEX en VERGELIJKEN gaan gebruiken, laten we eerst begrijpen hoe deze functies ons kunnen helpen bij het opzoeken van waarden.

Hoe gebruikt u de INDEX-functie in Excel?

De INDEX-functie in Excel retourneert de waarde op een specifieke locatie binnen een opgegeven bereik. De syntaxis van de INDEX-functie is als volgt:

• matrix (vereist) verwijst naar het bereik waaruit u de waarde wilt ophalen.
• rij_num(vereist, tenzij)kolom_num aanwezig is) verwijst naar het rijnummer van de matrix.
• kolom_num(optioneel, maar vereist als)rij_num is weggelaten) verwijst naar het kolomnummer van de matrix.

Bijvoorbeeld, om het cijfer van Jeffte weten, de 6e student op de lijst, kunt u de INDEX-functie als volgt gebruiken:

=INDEX(C2:C11,6)

√ Opmerking: Het bereik C2:C11bevat de cijfers, terwijl het getal 6het examencijfer ophaalt van de 6e student.

Laten we hier een kleine test doen. Wat geeft de formule =INDEX(A1:C1;2) terug? --- Precies! Deze retourneert Geboortedatum, de 2e waarde in de opgegeven rij.

We weten nu dat de INDEX-functie perfect werkt met zowel horizontale als verticale bereiken. Maar wat als we een waarde willen ophalen uit een groter bereik met meerdere rijen én kolommen? In dat geval moeten we zowel een rijnummer als een kolomnummer opgeven. Bijvoorbeeld: om Jeffs cijfer binnen het tabelbereik (in plaats van één enkele kolom) te vinden, kunnen we zijn cijfer lokaliseren met een rijnummer van 6 en een kolomnummer van 3 in het celbereik van A2 tot C11, als volgt:

=INDEX(A2:C11,6,3)

Bij een zeer grote database met meerdere rijen en kolommen is het echter onpraktisch om de formule handmatig in te voeren met exacte rij- en kolomnummers. Dan wordt het tijd om de VERGELIJKEN-functie te combineren met INDEX.

Hoe gebruikt u de VERGELIJKEN-functie in Excel?

De functie VERGELIJKEN in Excel retourneert een numerieke waarde: de positie van een specifiek item binnen het opgegeven bereik. De syntaxis van de functie VERGELIJKEN is als volgt:

• zoek_waarde (vereist) verwijst naar de waarde die gezocht moet worden in de zoek_matrix.
• zoek_matrix (vereist) verwijst naar het celbereik waarin VERGELIJKEN moet zoeken.
• vergelijk_type(optioneel):1,0of -1.
  • 1 (standaard), dan zoekt VERGELIJKEN de grootste waarde die kleiner dan of gelijk is aan de opzoek_waarde. De waarden in de opzoek_matrix moeten in oplopende volgorde staan.
  • 0 zoekt VERGELIJKEN de eerste waarde die exact gelijk is aan de opzoek_waarde. De waarden in de opzoek_matrix mogen in willekeurige volgorde staan. (Wanneer het vergelijkingstype is ingesteld op 0, kunt u jokertekens gebruiken.)
  • -1, dan zoekt VERGELIJKEN de kleinste waarde die groter is dan of gelijk aan de opzoek_waarde. De waarden in de opzoek_matrix moeten in aflopende volgorde staan.

Bijvoorbeeld, om de positie van Vera in de Naamlijstte weten, kunt u de Onderscheid formules als volgt gebruiken:

=MATCH("Vera",A2:A11,0)

√ Opmerking: Het resultaat „4” geeft aan dat de naam „Vera” op de vierde positie in de lijst staat.

Nu we de basis van de INDEX- en VERGELIJK-functies in Excel onder de knie hebben, gaan we onze mouwen oprollen en deze twee functies combineren.

Hoe combineert u INDEX en VERGELIJKEN in Excel?

Bekijk het onderstaande voorbeeld om te begrijpen hoe we de functies INDEX en VERGELIJKEN kunnen combineren:

Om Evelyns cijfer te vinden — wetende dat de examencijfers in de 3e kolom staan — kunnen we de VERGELIJKEN-functie gebruiken om automatisch de rijpositie te bepalen, zonder handmatig te tellen. Vervolgens halen we met de INDEX-functie de waarde op het kruispunt van de gevonden rij en de 3e kolom op:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Omdat de formule misschien wat ingewikkeld lijkt, bespreken we elk onderdeel ervan.

De INDEX-formule bevat drie argumenten:

• rij_num:VERGELIJKEN(„Evelyn",A2:A11,0)geeft INDEX de rijpositie van de waarde "Evelyn" in het bereik A2:A11, namelijk5.
• kolom_num: 3 geeft aan dat INDEX de 3e kolom moet gebruiken om de score op te halen binnen de matrix.
• matrix: A2:C11 instrueert INDEX om de overeenkomende waarde terug te geven op het kruispunt van de opgegeven rij en kolom binnen het bereik A2 tot C11. Uiteindelijk krijgen we het resultaat 90.

In de bovenstaande formule gebruikten we een vaste waarde: „Evelyn”. In de praktijk zijn vaste waarden echter onhandig, omdat u ze telkens moet aanpassen wanneer u andere gegevens wilt opzoeken—bijvoorbeeld het cijfer van een andere student. In dergelijke gevallen kunt u celverwijzingen gebruiken om dynamische formules te maken. In dit geval vervang ik „Evelyn” door F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Voorbeelden van INDEX en Onderscheid formules

In dit gedeelte behandelen we diverse scenario’s waarin u de functies INDEX en VERGELIJKEN kunt inzetten om aan uiteenlopende behoeften te voldoen.

INDEX en VERGELIJKEN voor tweerichtingsopzoeken

In het vorige voorbeeld kenden we het kolomnummer en gebruikten we een Onderscheid-formule om het rijnummer te vinden. Maar wat als we het kolomnummer ook niet weten?

In dergelijke gevallen kunt u een tweerichtingsopzoeken (ook wel matrixopzoeken genoemd) uitvoeren met twee VERGELIJKEN-functies: één om het rijnummer te vinden en één om het kolomnummer te bepalen. Bijvoorbeeld, om Evelyns cijfer te weten te komen, gebruikt u de volgende formule:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX en VERGELIJKEN voor opzoeken naar links

Stel nu dat u Evelyns klas moet achterhalen. U hebt waarschijnlijk opgemerkt dat de kolom „Klas” zich links van de kolom „Naam” bevindt – een situatie die buiten het bereik van een andere krachtige Excel-opzoekfunctie, VERT.ZOEKEN, valt.

Het vermogen om opzoeken naar links uit te voeren is inderdaad één van de aspecten waarin de combinatie van INDEX en VERGELIJKEN VERT.ZOEKEN overtreft.

Om Evelyns klas te vinden, gebruikt u de volgende formule om Evelyn op te zoeken in B2:B11 en de overeenkomstige waarde op te halen uit A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX en VERGELIJKEN voor hoofdlettergevoelig opzoeken

De VERGELIJKEN-functie is standaard niet hoofdlettergevoelig. Wilt u wel onderscheid maken tussen hoofdletters en kleine letters? Combineer deze dan met de EXACT-functie. Door VERGELIJKEN en EXACT samen te gebruiken in een INDEX-formule, voert u effectief een hoofdlettergevoelige opzoeking uit, zoals hieronder wordt getoond:

• Matrix verwijst naar het bereik waaruit u de waarde wilt ophalen.
• zoek_waarde verwijst naar de waarde die – met inachtneming van hoofdletters en kleine letters – moet worden vergeleken in de zoek_matrix.
• zoek_matrix verwijst naar het celbereik waarin zoek_waarde moet worden vergeleken.

Bijvoorbeeld, om JIMMY’s examencijferte weten, gebruikt u de volgende formule:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter, behalve in Excel 365, Excel 2021 en nieuwere versies.

INDEX en VERGELIJKEN om de dichtstbijzijnde overeenkomst te vinden

In Excel kunt u situaties tegenkomen waarin u de dichtstbijzijnde of meest vergelijkbare waarde ten opzichte van een specifieke waarde in een dataset moet vinden. In dergelijke scenario’s kan een combinatie van de functies INDEX en VERGELIJKEN, samen met ABS en MIN, zeer nuttig zijn.

• matrix verwijst naar het bereik waaruit u de waarde wilt ophalen.
• zoek_matrix verwijst naar het bereik van waarden waarin u de dichtstbijzijnde overeenkomst met de zoek_waarde wilt vinden.
• zoek_waarde verwijst naar de waarde waarvan u de dichtstbijzijnde overeenkomst wilt vinden.

Bijvoorbeeld: om erachter te komen wiens cijfer het dichtst bij 85 ligt, gebruikt u de volgende formule om het cijfer te vinden dat het dichtst bij 85 ligt in C2:C11 en de overeenkomstige waarde op te halen uit A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter, behalve in Excel 365, Excel 2021 en nieuwere versies.

INDEX en VERGELIJKEN voor opzoeken met meerdere criteria

Om een waarde te vinden die aan meerdere voorwaarden voldoet – waarbij u in twee of meer kolommen moet zoeken – gebruikt u de volgende formule. Deze formule stelt u in staat een opzoeken met meerdere criteria uit te voeren door verschillende voorwaarden in verschillende kolommen op te geven, zodat u de gewenste waarde vindt die aan alle opgegeven criteria voldoet.

√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter. Er verschijnen dan accolades in de formulebalk.

• matrix verwijst naar het bereik waaruit u de waarde wilt ophalen.
• (zoek_waarde=zoek_matrix) vertegenwoordigt één voorwaarde. Deze voorwaarde controleert of een bepaalde zoek_waarde overeenkomt met de waarden in de zoek_matrix.

Bijvoorbeeld, om de score van Coco uit klas A te vinden, wiens geboortedatum 7/2/2008 is, kunt u de volgende formule gebruiken:

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

INDEX en VERGELIJKEN om een opzoekactie toe te passen over meerdere kolommen

Stel u voor dat u werkt met meerdere gegevenskolommen. De eerste kolom fungeert als sleutel om de gegevens in de andere kolommen te classificeren. Om de categorie of classificatie voor een specifieke invoer te bepalen, moet u zoeken in de gegevenskolommen en deze koppelen aan de relevante sleutel in de referentiekolom.

Bijvoorbeeld, in de onderstaande tabel: hoe kunnen we de leerling Shawn koppelen aan zijn bijbehorende klas met INDEX en VERGELIJKEN? Dit kunt u doen met een formule, maar die formule is behoorlijk lang en moeilijk te begrijpen, laat staan te onthouden en in te typen.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Daar komt de functie Kutools voor Excel Index en overeenkomst meerdere kolommen perfect om. Deze vereenvoudigt het proces en maakt het razendsnel en eenvoudig om specifieke items te koppelen aan hun bijbehorende categorieën. Om deze krachtige tool te gebruiken en Shawn moeiteloos aan zijn klas te koppelen, hoeft u alleen maar de add-in Kutools voor Excel te downloaden en installeren en daarna het volgende te doen:

1. Selecteer de doelcel waarin u de overeenkomende klasse wilt weergeven.
2. Klik op het tabblad Kutools en vervolgens op Formulehulp > Zoeken en verwijzen > Index en overeenkomst meerdere kolommen.
3. 
3. Voer de volgende stappen uit in het pop-updialoogvenster:
   1. Klik op de 1e -knop naast Zoek_kol om de kolom te selecteren met de belangrijkste informatie die u wilt ophalen: de klasnamen. (U kunt hier slechts één kolom selecteren.)
   2. Klik op de 2e -knop naast Tabel_bereik om de cellen te selecteren die overeenkomen met de waarden in de geselecteerde Zoek_kol, namelijk de namen van de leerlingen.
   3. Klik op de 3e knop naast Zoek_waarde om de cel te selecteren met de naam van de leerling die u wilt koppelen aan zijn of haar klas—in dit geval Shawn.
   4. Klik op OK.
   5. 

Resultaat

Kutools heeft automatisch de formule gegenereerd, en u ziet onmiddellijk de klasnaam van Shawn in de doelcel verschijnen.

INDEX en VERGELIJKEN om de eerste niet-lege waarde op te zoeken

Om de eerste niet-lege waarde op te halen, fouten negerend, uit een kolom of rij, kunt u een formule gebruiken op basis van de functies INDEX en VERGELIJKEN. Als u de fouten in uw bereik juist niet wilt negeren, voegt u de functie ISLEEG toe.

• Eerste niet-lege waarde ophalen in een kolom of rij, fouten negerend:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Eerste niet-lege waarde ophalen in een kolom of rij, inclusief fouten:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX en VERGELIJKEN om de eerste numerieke waarde op te zoeken

Gebruik de formule op basis van de functies INDEX, VERGELIJKEN en ISGETAL om de eerste numerieke waarde uit een kolom of rij op te halen.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX en VERGELIJKEN om koppelingen met MAX of MIN op te zoeken

Als u een waarde wilt ophalen die gekoppeld is aan de maximumwaarde of Minimale waarde binnen een bereik, kunt u de functies MAX of MIN combineren met INDEX en VERGELIJKEN.

• INDEX en VERGELIJKEN om een waarde op te halen die hoort bij de Maximale waarde:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX en VERGELIJKEN om een waarde op te halen die hoort bij de Minimale waarde:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• De bovenstaande formules bevatten twee argumenten:
• • matrix verwijst naar het bereik waaruit u de gerelateerde informatie wilt ophalen.
  • zoek_matrix vertegenwoordigt de verzameling waarden die wordt doorzocht op basis van specifieke criteria, zoals de maximale of minimale waarde.

Bijvoorbeeld, als u wilt bepalen wie de hoogste score heeft, gebruikt u de volgende formule:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tip: Pas uw eigen #N/B-foutmeldingen aan

Bij het gebruik van de functies INDEX en VERGELIJKEN in Excel krijgt u mogelijk de fout #N/B te zien wanneer er geen overeenkomend resultaat wordt gevonden. Zo verschijnt in de onderstaande tabel bijvoorbeeld een #N/B-fout bij het opzoeken van de score van een studente genaamd Samantha, omdat zij niet in de dataset voorkomt.

Om uw werkbladen gebruiksvriendelijker te maken, kunt u dit foutbericht aanpassen door uw INDEX-formule Onderscheid formules in de functie ALS.NB te plaatsen:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Hierboven vindt u alle relevante informatie over de functies INDEX en VERGELIJKEN in Excel. Ik hoop dat u deze handleiding nuttig vindt! Wilt u nog meer Excel-tips en -trucs ontdekken? Klik dan hier voor toegang tot onze uitgebreide verzameling van duizenden tutorials.