De Excel functies INDEX (NL/EN) en VERGELIJKEN (NL) ofwel MATCH (EN) zijn samen een krachtig stuk gereedschap. Deze combinatie gaat een stap verder dan de VERT.ZOEKEN functie. Het is namelijk mogelijk om gegevens in kolommen voor de kolom waarbinnen je zoekt weer te geven.
Onder andere te gebruiken in de volgende situaties:
- Als de functies VERT.ZOEKEN of HORIZ.ZOEKEN niet kunnen werken
- Bij grote uitgebreide rekenmodellen (kost minder tijd om te berekenen)
- Tabel met dynamische kopteksten
- Binnen een formulier (weergeven van bijvoorbeeld NAW gegevens).
- Binnen een tabel waarin je een omschrijving mist van een artikelnummer, klantnummer, enz.
- Binnen meerdere tabellen om de volledigheid te controleren.
Er is een video instructie Index en Vergelijken beschikbaar binnen de gratis cursus "Zoeken en Vinden met Microsoft Office Excel". Deze cursus bevat meerdere video instructies en Excelbestanden met de voorbeelden die besproken worden binnen dit artikel. Onderaan dit artikel is te vinden hoe jij 10 dagen GRATIS toegang krijgt!
Doordat we binnen dit artikel 2 functies combineren is het goed om te weten wat deze precies doen. Daarom beginnen we eerst met de separate uitleg van de functionaliteiten. Als laatste bundelen we deze kennis waardoor je de echte kracht van deze combinatie kunt zien. Voordat je hiermee aan de slag gaat is het verstandig om onze eerdere artikelen te lezen:
Verticaal Zoeken Excel functie
Horizontaal Zoeken Excel functie
Invullen van de functie VERGELIJKEN (Syntax)
=VERGELIJKEN( [I] ; [II] ; [III]*)
* dikgedrukt is optioneel
I. Waar naar gezocht moet worden; Kan een tekst, waarde of een celverwijzing zijn.
II. Bereik van de functie; is een bereik (bijvoorbeeld: “A1:D4”)
III. Dient er gezocht te worden naar de best mogelijke overeenkomst minder dan, groter dan of exact gelijk zijn aan. (optioneel) [indien weggelaten standaard 1]
3 mogelijkheden:
1 (minder dan)
0 (exact)
-1 (groter dan)
Als we de werking uitspreken dan is dat als volgt:
Zoek volgens de methode [III] naar [I] in het bereik van [II] en geef aan op de hoeveelste rij of kolom de zoekwaarde is gevonden binnen [II].
Voorbeelden VERGELIJKEN
Hieronder worden 8 verschillende voorbeelden waarbij er steeds 1 van de argumenten aangepast wordt. Op deze manier wordt het duidelijk wat voor effect deze aanpassingen hebben op de uitkomst.
Dit bestand is een onderdeel van de Excel mini cursus Zoeken en Vinden met Excel functies
De afbeelding geeft een schermafdruk van het Excel bestand weer. Waarbij bereik A1 t/m D2 is gevuld met harde teksten. Het bereik A4 t/m A11 is gevuld met variaties van deze functie. Kolom B laat zien welke formule er in de cel ervoor is ingevoerd.
Voorbeeld 1.1
=VERGELIJKEN( "aa1" ; $A$1:$A$2 ; 0 )
Er wordt exact gezocht naar de tekst “aa1” binnen het absoluut gemaakte bereik “A1:A2”. Indien de tekst “aa1” gevonden wordt, zal de uitkomst dus zijn in de hoeveelste rij de tekst gevonden wordt. In de cel “A1” wordt al direct de tekst “aa1” gevonden en dat is dus de 1ste rij van het bereik “A1:A2”. Daarom is het antwoord in dit geval dus 1.
Voorbeeld 1.2
=VERGELIJKEN( "aa1" ; $A$1:$D$1 ; 0 )
Er wordt exact gezocht naar de tekst “aa1” in het bereik “A1:D1. Dus zoekt de functie naar de tekst “aa1” binnen rij 1. Eenmaal gevonden zal de hoeveelste kolom worden weergegeven als antwoord. In cel A1 wordt al direct deze tekst gevonden, daarom is het resultaat van deze functie de 1ste kolom van het bereik A1:D1 ofwel 1.
Voorbeeld 1.3
=VERGELIJKEN( "aa1" ; $A$1:$D$2 ; 0 )
Dit keer is het bereik aangepast naar de volledige tabel. Het antwoord is in dit geval de foutmelding #N/B (in de Engelse versie is dat #N/A ). Doordat de functie als doel heeft de hoeveelste rij of kolom als resultaat te geven, wordt deze foutmelding veroorzaakt. Excel weet namelijk niet welke uitkomst je wil hebben de hoeveelste rij of de hoeveelste kolom.
Voorbeeld 1.4
=VERGELIJKEN( "ee1" ; $A$1:$D$1 ; 0 )
Het bereik is nu weer terug naar enkel rij 1 maar dit keer zoeken we naar de tekst “ee1”. We zoeken exact, dat is af te lezen aan de 0 bij [III]. Omdat de tekst “ee1” niet te vinden is binnen het geselecteerde bereik en we exact zoeken geeft de functie een foutmelding.
Voorbeeld 1.5
=VERGELIJKEN( "ee1" ; $A$1:$D$1 ; 1 )
Nu hebben we het 3de argument aangepast zodat er gezocht wordt bij benadering naar de minder dan uitkomst. Omdat van de alfabetische volgorde van dd1, ee1 de dd1 eerder voorkomt zal de functie dus “dd1” zien als beste benadering. De tekst “dd1” bevind zich in de 4de kolom van het bereik A1:D1, daarom is de uitkomst ook 4.
Voorbeeld 1.6
=VERGELIJKEN( "ee1" ; $A$1:$D$1 ; -1 )
Als we het 3de argument aanpassen naar -1 krijgen we een foutmelding. Deze foutmelding ontstaat simpelweg doordat de volgorde van de waarden binnen het bereik A1:D1 niet aflopend is.
Voorbeeld 1.7
=VERGELIJKEN( "aa2" ; $A$2:$D$2 ; -1 )
De teksten op rij 2 zijn wel aflopend en doordat er niet exact gezocht wordt naar de maximale “waarde” krijgen we het antwoord 4. Omdat aa2 voor aa3 komt wordt de maximale waarde dus aa3 ofwel kolom 4.
Voorbeeld 1.8
=VERGELIJKEN( "aa4" ; $A$2:$D$2 ; -1 )
Omdat “aa4” na “aa3” maar voor “bb3” komt wordt de groter dan waarde dus bb3. Dat is de reden dat de zoekwaarde in kolom 3 staat van het geselecteerde bereik. Het antwoord is dus 3 zoals je ook kunt zien aan het voorbeeld.
Invullen van de functie INDEX (Syntax)
=INDEX( [I] ; [II] ; [III])
* dikgedrukt is optioneel
I Het bereik waarvan je een bepaalde waarde of tekst wil weergeven.
II In welke rij de waarde of tekst zich bevindt
III In welke kolom de waarde of tekst zich bevindt
Als we de werking uitspreken dan is dat als volgt:
Geef de uitkomst van cel op het kruispunt van de [II]ste rij en [III]ste kolom van het bereik [I]
Voorbeelden INDEX
Hieronder worden 3 verschillende voorbeelden waarbij er, net als bij de vorige voorbeelden, steeds 1 van de argumenten aangepast wordt.
Dit bestand is een onderdeel van de Excel mini cursus Zoeken en Vinden met Excel functies
De afbeelding geeft een schermafdruk van het Excel bestand weer. Waarbij bereik B2 t/m E4 is gevuld met harde teksten. Het bereik A6 t/m A8 is gevuld met variaties van deze functie. Kolom B laat zien welke formule er in de cel ervoor is ingevoerd.
Voorbeeld 2.1
=INDEX( $B$2:$E$4 ; 1 ; 1 )
We zoeken de uitkomst van de cel die in de eerste rij en eerste kolom staat binnen het bereik B2:E4. De uitkomst van deze functie is dan ook “bb2”omdat de cel B2 de eerste cel is die we vinden binnen het bereik B2:E4.
Voorbeeld 2.2
=INDEX( $B$2:$E$4 ; 1 ; A3 )
Nu is het derde argument aangepast naar een celverwijzing naar cel A3. De cel A3 bevat de waarde 2 waardoor de functie opzoek gaan naar de 1ste rij en de 2de kolom van het bereik B2:E4. De uitkomst is dus in dit geval “cc2”.
Voorbeeld 2.3
=INDEX( $B$2:$E$4 ; C1 ; A3 )
Nu is het tweede argument aangepast naar een celverwijzing. De waarde van cel C1 is 2 waardoor de functie dus opzoek gaat naar het kruispunt van de 2de rij en 2de kolom binnen het bereik B2:E4. Nu is het resultaat dus “cc3”.
De krachten bundelen
Nu je weet hoe de Vergelijken en Index functies werken is het tijd geworden om deze te bundelen tot 1 krachtige formule. Met de vergelijken functie kan je een tekst of waarde opzoeken binnen een rij of een kolom. Doordat het resultaat van de functie aangeeft in de hoeveelste rij of kolom de informatie te vinden is, kan je dit gebruiken binnen een Index functie. Ofwel door een “nested” formule te maken kan je dus informatie weergeven die in dezelfde rij of kolom staat.
Klinkt allemaal nog wat ingewikkeld misschien? Laten we het aan de hand van de onderstaande voorbeelden bekijken wat de toegevoegde waarde is. 
Dit bestand is een onderdeel van de Excel mini cursus Zoeken en Vinden met Excel functies
Voorbeeld 3.1
=INDEX( $B$2:$E$4 ; A2 ; E1 )
We beginnen met een Index functie waarbij we de tekst van het kruispunt van de 1ste rij en de 4de kolom willen weergeven. Hierbij gaan we natuurlijk uit van het bereik B2:E4. Het antwoord is dan ook “ee2”. Doordat het een kleine tabel betreft weten we dus waar de cel zich bevindt. Maar wat als dit een tabel zou zijn geweest met 100.000 rijen? Dan hadden we niet zo snel geweten welke waarde we in het 2de argument hadden moeten plaatsen.
Voorbeeld 3.2
=VERGELIJKEN( "ee2" ; $E$2:$E$4 ; 0)
Gezien we weten dat “ee2” zich in kolom E bevindt kunnen we opzoeken in welke rij deze tekst gevuld is. Het antwoord is hier 1 omdat de tekst “ee2” te vinden is in cel E2. Deze cel is te vinden op de eerste rij van het bereik E2:E4.
Voorbeeld 3.3
=VERGELIJKEN( "ee2" ; $B$2:$E$2 ; 0)
Maar andersom is ook mogelijk, het kan zijn dat we weten binnen welke rij de tekst “ee2” aanwezig zal zijn. Daarom hebben we rij 2 gebruikt voor het bereik. De uitkomst is nu 4 want de tekst “ee2” vinden we in de 4de kolom van het bereik B2:E2.
Voorbeeld 3.4
=INDEX( $B$2:$E$4 ; VERGELIJKEN( "ee2" ; E2:E4 ; 0 ) ; E1 )
Even een stapje terug, dus uitgaande dat we weten binnen welke kolom de tekst “ee2” zich bevind, ofwel in kolom E. Met deze informatie kunnen we dus gebruik maken van de index functie van voorbeeld 3.1. Alleen vervangen we het 2de argument nu met de functie uit voorbeeld 3.2. Wat we nu in feite hebben gecreëerd is een Verticaal zoeken functie, kijk maar eens naar het volgende voorbeeld.
Voorbeeld 3.5
=VERT.ZOEKEN( "ee2" ; $E$2:$E$4 ; 1 ; 0 )
We weten dat de tekst “ee2” zich bevindt in kolom E dus gaan we exact zoeken naar deze tekst binnen het bereik E2:E4. En omdat dit bereik maar uit 1 kolom bestaat kiezen we ervoor om het 3de argument van deze functie te voorzien van een 1. Dus binnen voorbeeld 3.4 en 3.5 doen we in feite hetzelfde.
Voorbeeld 3.6
=INDEX( $B$2:$E$4 ; A2 ; VERGELIJKEN( "ee2" ; B2:E2 ; 0 ) )
Maar wat nu als we niet weten binnen welke kolom de zoekwaarde zicht bevind maar wel de rij? De horizontaal zoeken functie zou dan de uitkomst kunnen bieden. Maar in dit voorbeeld gebruiken we de index functie van voorbeeld 3.1. Als we het 3de argument vervangen met de functie in voorbeeld 3.3 wordt ook de tekst “ee2” gevonden.
Voorbeeld 3.7
=HORIZ.ZOEKEN( "ee2" ; B2:E2 ; 1 ; 0 )
Laten we het nu nog eens met de horizontaal zoeken functie proberen. We weten dat de tekst “ee2” zich in de 1ste rij bevindt van het bereik. Daarom selecteren we het bereik B2:E2 in het tweede argument en gebruiken we een 1 in het 3de argument. De uitkomst wordt ook met op deze manier gevonden.
Voorbeeld 3.8
=VERT.ZOEKEN("ee2" ; $E$2:$E$4 ; -3 ; 0 )
Maar nu komt het! Je bent op zoek naar een tekst of waarde die zich in dezelfde rij maar wel in een andere kolom bevind. Dat komt vaak voor als je bijvoorbeeld de naam van een klant wil weten en je hebt alleen een klantnummer. Maar binnen dit voorbeeld zijn we opzoek naar een tekst die 3 kolommen vóór de kolom waar we in zoeken. Als we in het 3de argument -3 gebruiken, 3 kolommen voor de zoekkolom, dan krijgen we een foutmelding. Bij de Verticaal Zoeken functie moet de kolom waar je in wil zoeken de eerste kolom zijn van het bereik in het 3de argument. Daarnaast kan je alleen waarden of teksten opzoeken die zich in dit bereik bevinden.
Voorbeeld 3.9
=INDEX( $B$2:$E$4 ; VERGELIJKEN( "ee2" ; E2:E4 ; 0 ) ; 1 )
Nu je geleerd hebt hoe je de index en vergelijken functies kan combineren heb je de oplossing voor dit probleem in je Excel gereedschapskist zitten. Binnen de Index functie geef je het complete bereik in, ofwel B2:E4. De rij is onbekend maar je weet dat de zoekwaarde zich in kolom E bevindt. Dus gebruik je de vergelijken functie om de rij op te zoeken. In het 3de argument geef je aan in welke kolom de gegevens staan die je wil weergeven.
Deze laatste variatie in voorbeeld 3.8 en 3.9 kan natuurlijk ook voorkomen voor de horizontaal zoeken variant. Wij dagen je uit om deze mogelijkheid zelf uit te testen.
Tips
Gebruik de +1 methode indien je een hele brede tabel hebt INDEX
Voeg een extra kolom in links van de tabel en voorzie deze van een telling. Zo wordt het makkelijker om te bepalen welke waarde je moet invullen binnen het 2de of 3de argument van de index functie.
Gebruik altijd “0” bij [III] VERGELIJKEN
Zo zie je altijd of er een uitkomst is die 100% overeenkomt met de zoekwaarde. Mocht de foutmelding #N/B (Engels = #N/A) verschijnen dan is de waarde of tekst niet gevonden. Indien je geen foutmeldingen wil laten zien, of deze zou willen vervangen met een waarde of een tekst, gebruik dan de functie ALS.FOUT.
Gebruik altijd absolute celverwijzingen bij [I] INDEX of [II] VERGELIJKEN
Zo voorkom je dat het bereik waarin gezocht wordt steeds met 1 rij verschuift indien je de functie kopieert en plakt.
Rekening houden met de juiste rij [II] of kolom [III] INDEX
Binnen [I] vul je het bereik in waarbinnen je zoekt naar de hoeveelste rij of kolom. Indien het bereik begint bij kolom B en je telt kolom A als de eerste kolom dan gaan het fout. Indien je bereik begint bij rij 2 en je telt rij 1 als 1 dan gaat het ook fout. Je moet dus de relatieve positie aangeven vanaf de eerste cel binnen het bereik.
Soortgelijke functies
Veel gemaakte fouten
Omdat we binnen dit artikel meerdere fucnties bespreken hebben we ervoor gekozen om de valkuilen per functie weer te geven.
Veel gemaakte fouten met VERGELIJKEN
- Niet absoluut maken van het bereik [II] van de functie.
- Vergeten om bij [III] een “0” in te vullen.
- Vergeten te sorteren op de rij of kolom van het bereik [II] indien [III] is gevuld met “-1” of “1”.
- In het zoekveld [I] wordt als een tekst gevuld en de eerste kolom binnen [II] bevat alleen waarden.
Veel gemaakte fouten met INDEX
- Niet absoluut maken van het bereik [I] van de functie.
- Verkeerde kolom of rij invoegen (geen rekening gehouden met de relatieve positie van de eerste cel van het bereik onder [I].
Veel gemaakte fouten met INDEX(VERGELIJKEN)
- Alle fouten die hierboven zijn vermeld
- Het bereik van INDEX [I] niet afstemmen op het gebruikte bereik van VERGELIJKEN [II].
- Als het bereik van INDEX [I] A1:D4 is dan zal het bereik van VERGELIJKEN [II] dus A1:A4, B1:B4, C1:C4 of D1:D4 moeten zijn indien je opzoek bent naar de kolom.
- Als het bereik van INDEX [I] A1:D4 is dan zal het bereik van VERGELIJKEN [II] dus A1:D1, A2:D2, A3:D3 of A4:D4 moeten zijn indien je opzoek bent naar de rij.
Gratis Cursus
Gefeliciteerd! je hebt het einde van dit 2500 woorden tellende artikel gehaald!
De volgende stap is: jezelf inschrijven op de website met de rode aanmelden knop recht bovenaan je scherm. Er gaat dan een wereld voor je open zoals je hieronder kan lezen:
Als je de informatie ook nog eens vanuit je luie stoel wil volgende dan kan je gebruik maken van de gratis cursus "Zoeken en vinden met Mictrosoft Office Excel". Deze bevat alle informatie welke jij zojuist hebt gelezen. Daarnaast worden ook de soortgelijke functies volledig besproken. En als bonus zijn ook de gebruikte voorbeeldbestanden te downloaden, zodat je de kennis die je opdoet gelijk kunt oefenen. Tip: Binnen deze cursus zie je ook nog een aantal handige truckjes voorbij komen evenals nog een aantal extra fucnties. Om toegang te krijgen tot de cursus meld je jezelf aan op onze website. Nadat je jouw email adres hebt geverifieerd krijg je toegang tot onze leeromgeving. Deze is op te roepen door te klikken op de rode knop met de tekst “NAAR LEEROMGEVING” binnen “MIJN ACCOUNT”. Eenmaal binnen de leeromgeving vind je deze cursus onder de tab Demo.
Mocht je de fucntionaliteit van onze leeromgeving willen uittesten kies dan voor de proefles: "Slimmer en Efficenter werken met Microsoft Office". Deze Proefles bevat een uur aan lesmateriaal waarbij zowel Office in zijn algemeendheid wordt besproken. En 2 video's waarbij de applicaties Excel en Word verder worden besproken.
Veel Leerplezier!
Richard Koolman
Click and Know
