De functie X.ZOEKEN

Met de functie X.Zoeken kun je in lijsten, tabellen en databases nagenoeg alles opzoeken. Van boven naar beneden, van links naar rechts en zelfs een reeks in een keer. Mits je weet hoe!

Dit artikel zal duidelijk maken wat je met X.Zoeken kunt doen en waarom X.Zoeken een functie is die iedere Excel gebruiker gewoon zou moeten beheersen!

X.Zoeken zoekt een waarde in een specifieke kolom van een tabel en geeft een waarde uit een andere kolom van de rij die die opgezochte waarde bevat, terug.

De Syntax is:

=X.ZOEKEN(Zoekwaarde; Zoeken_matrix; Matrix_retourneren; Indien_niet_gevonden; Overeenkomstmodus; Zoekmodus)

De functie kent zes argumenten waarvan de eerste drie verplicht moeten worden ingevoerd.

Verplicht:

• • Zoekwaarde: De waarde die moet worden gezocht (in een specifieke kolom van tabel)
  • Zoeken-Matrix: De kolom, waarin de Zoekwaarde moet worden gezocht
  • Matrix_retourneren: De kolom waaruit het resultaat moet worden opgehaald

Optioneel:

• • Indien_niet_gevonden: De waarde die wordt getoond als de Zoekwaarde niet wordt gevonden
  • Overeenkomstmodus:
    • 0 > Exacte overeenkomst (Standaard). Er wordt een exacte overeenkomst gezocht. Als die niet wordt gevonden, wordt #N/A getoond
    • -1 > Exacte overeenkomst, of eerstvolgende kleinere item. Er wordt een exacte overeenkomst gezocht. Als die niet wordt gevonden, wordt het eerstvolgende kleinere item getoond
    • 1 > Exacte overeenkomst, of eerstvolgende grotere item. Er wordt een exacte overeenkomst gezocht. Als die niet wordt gevonden, wordt het eerstvolgende grotere item  getoond
    • 2 > Overeenkomst met een jokerteken
  • Zoekmodus:
    • 1 > Van boven naar beneden, vanaf het eerste item (Standaard)
    • -1 > Van onder naar boven, vanaf het laatste item
    • 2 > Binair, waarbij de Zoeken-Matrix in oplopende volgorde gesorteerd moet zijn. Als de Zoeken-Matrix niet is gesorteerd, worden er ongeldige resultaten geretourneerd
    • -2 > Binair, waarbij de Zoeken-Matrix in aflopende volgorde gesorteerd moet zijn. Als de Zoeken-Matrix niet is gesorteerd, worden er ongeldige resultaten geretourneerd

Als je alles op een rijtje zet, begint het misschien een beetje te duizelen. Hoe werkt X.Zoeken precies? Wat vul ik in en heb ik alle argumenten echt nodig? Een aantal voorbeelden maakt het een en ander wellicht een hoop duidelijker!

Onderstaand voorbeeld toont een tabel met informatie over enkele landen. Met de functie X.Zoeken kun je, door een land te kiezen, het inwoneraantal vinden. Dit werkt als volgt.

In bovenstaand voorbeeld wordt met de functie X.Zoeken het aantal inwoners van Duitsland opgezocht. De functie staat in D8 en bevat de drie verplichte argumenten die de functie kent. Dit zijn:

• • Zoekwaarde > C8
  • Zoeken-Matrix > C11:C20
  • Matrix_retourneren > D11:D20

De functie =X.ZOEKEN(C8;C11:C20;D11:D20) in D8, kun je lezen als:

Zoek in C11:C20 naar de waarde van C8 en toon uit de rij die die zoekwaarde bevat, de waarde uit D11:D20.

• • De waarde in C8 is Duitsland
  • Duitsland is de tweede waarde van het bereik C11:C20
  • De tweede waarde van het bereik D11:D20 is 83.132.799
  • Het antwoord van de functie is dus 83.132.799

!! Indien je een land zoekt dat niet in de tabel voorkomt, bijvoorbeeld Zweden, wordt als resultaat van de functie de waarde #N/B (Niet Beschikbaar) getoond.

Het vierde argument van de functie X.Zoeken is Indien_niet_gevonden. Dit argument is niet verplicht en geeft je de mogelijkheid een opmerking te tonen indien de functie geen resultaat oplevert. Deze opmerking vervangt in dat geval de standaard waarde #N/B.

In het onderstaande voorbeeld bevat het vierde argument Indien_niet_gevonden de waarde “—“. Zweden staat niet in de lijst met landen en dus is het resultaat van de functie “—“, in plaats van de standaard waarde #N/B.

Het vijfde argument van de functie X.Zoeken is Overeenkomstmodus. Met dit argument kun je aangeven welke waardes er als resultaat kunnen verschijnen bij een zoekopdracht. Als je het argument weglaat, interpreteert de functie X.Zoeken dat alsof je de standaardwaarde 0 hebt gekozen. In dat geval moet de functie X.Zoeken altijd een exacte overeenkomst vinden.

Het argument Overeenkomstmodus kent vier waardes:

• • 0 > Exacte overeenkomst (standaard). Er wordt een exacte overeenkomst gezocht. Als die er niet is, wordt #N/A getoond (of de waarde van Indien_niet_gevonden)
  • -1 > Exacte overeenkomst, of het eerstvolgende kleinere item. Er wordt een exacte overeenkomst gezocht. Als er geen exacte overeenkomst is, wordt het eerstvolgende kleinere item getoond
  • 1 > Exacte overeenkomst, of het eerstvolgende grotere item. Er wordt een exacte overeenkomst gezocht. Als er geen exacte overeenkomst is, wordt het eerstvolgende grotere item  getoond
  • 2 > Overeenkomst met een jokerteken

Het kiezen van een Overeenkomstmodus is belangrijk wanneer er met grenswaarden wordt gewerkt. Denk hierbij aan de belastingschijven of een bonus tabel.

Het voorbeeld in Figuur 1‑3 toont een bonus tabel. Op basis van een omzet wordt een bonus percentage verkregen. In dit voorbeeld is de omzet € 165.500 en is de verkregen bonus 5%. Hoe komt de functie X.Zoeken aan dit resultaat?

De functie X.Zoeken in D8 bevat vijf argumenten. Dit zijn:

• • Zoekwaarde > C8
  • Zoeken-Matrix > D11:D15
  • Matrix_retourneren > C11:C15
  • Indien_niet_gevonden > “—”
  • Overeenkomstmodus > -1

De functie =X.ZOEKEN(C8;D11:D15;C11:C15;”—“;-1) in D8, kun je lezen als:

Zoek in D11:D15 naar de waarde van C8 en toon uit de rij die die waarde of de eerstvolgende kleinere waarde bevat, de waarde uit C11:C15.

• • De waarde in C8 is € 165.500
  • € 165.500 komt niet voor in bereik D11:D15. Omdat de Overeenkomstmodus -1 is, wordt er met de eerstvolgende kleinere waarde (dan de zoekwaarde) gerekend. Dit is € 100.000
  • € 100.000 is de tweede waarde van het bereik D11:D15
  • De tweede waarde van het bereik C11:C15 is 5%

Het antwoord is dus 5%.

Dit voorbeeld toont ook de flexibiliteit van de functie X.Zoeken aan. Het maakt namelijk niet uit in welke kolom de Zoekwaarde staat. De functie X.Zoeken kan zowel in een kolom links, als rechts van de kolom met de Zoekwaarde zoeken! Hierdoor verschilt de functie X.Zoeken van de functie Vert.Zoeken. Bij de laatstgenoemde functie moet de kolom die de zoekwaarde bevat altijd de meest linker kolom van de tabel zijn!

Het zesde argument van de functie X.Zoeken is een argument waar weinig over geschreven wordt, maar waar je op het gebied van verwerking en efficiency veel mee kan winnen.

De Zoekmodus kent vier waardes:

• • 1 > Van boven naar beneden, vanaf het eerste item (Standaard)
  • -1 > Van onder naar boven, vanaf het laatste item
  • 2 > Binair, waarbij de Zoeken-Matrix in oplopende volgorde moet zijn gesorteerd. Als de Zoeken-Matrix niet is gesorteerd, worden er ongeldige resultaten geretourneerd
  • -2 > Binair, waarbij de Zoeken-Matrix in aflopende volgorde moet zijn gesorteerd. Als de Zoeken-Matrix niet is gesorteerd, worden er ongeldige resultaten geretourneerd

Als je het argument weglaat, interpreteert de functie dat alsof je de standaardwaarde 1 hebt gekozen. In dat geval doorzoekt de functie X.Zoeken altijd de gehele kolom met zoekwaardes.  Van boven naar beneden, vanaf het eerste item, tot dat een overeenkomst is gevonden.

Het doorzoeken van de gehele kolom is bij kleine tabellen niet echt een probleem. Bij grotere tabellen zal Excel echter meer resources moeten gebruiken om alle zoekwaardes met elkaar te vergelijken. Een tijdrovende klus, die sneller kan door anders te zoeken!

Hoe werkt dat?

Het zoeken in, of doorzoeken van, een lijst kan op verschillende manieren. De een is wellicht eenvoudiger dan de ander en het werkt niet allemaal even snel. Als verwerkingssnelheid een issue is, zijn er twee opties. Zoeken volgens een lineair of zoeken volgens een binair algoritme.

Het lineaire zoekalgoritme, ook wel sequentieel zoeken genoemd, is een methode om een zoekwaarde binnen een, al dan niet geordende, lijst te vinden. Het algoritme controleert van boven naar beneden elk element van de lijst op een doelwaarde totdat een overeenkomst is gevonden of totdat alle elementen zijn gecontroleerd. Er kan dus worden gezegd dat de hoeveelheid tijd die een lineaire zoekopdracht nodig heeft om een zoekwaarde te vinden, evenredig is met de positie van de zoekwaarde in de lijst.

Lineair zoeken is praktisch wanneer de lijst slechts een paar zoekwaarden bevat, wanneer een enkele zoekopdracht in een ongeordende lijst moet worden uitgevoerd, of wanneer je in een lijst zoekt die niet kan worden gesorteerd.

Microsoft Excel verwijst naar de lineaire methode als de Exacte Overeenkomst-methode.

Als de exacte overeenkomst met de Zoekwaarde wordt gevonden, zal een waarde uit een andere kolom als resultaat worden terug gegeven. Zo niet dan is het resultaat #N/B.

Het binaire zoekalgoritme, ook wel de half-interval-zoekactie genoemd, is een heel andere methode om een zoekwaarde in een geordende lijst te vinden. Het vergelijkt een zoekwaarde met het middelste element van een lijst. Als ze niet overeenkomen, wordt de helft waar de zoekwaarde niet kan bestaan geëlimineerd en wordt het proces herhaald totdat er een overeenkomst is, of totdat er geen zoekwaarden meer zijn om te vergelijken. Dit werkt op precies dezelfde wijze als waarop je naar een woord in een woordenboek zou zoeken.

Binair zoeken is alleen mogelijk als een lijst kan worden gesorteerd!

Microsoft Excel verwijst naar de binaire methode als de Bij-benadering methode.

Als de zoekwaarde niet in de lijst voorkomt, zal de binaire zoekopdracht een overeenkomst opleveren met het laatste element dat overblijft nadat alle eliminaties met een half interval hebben plaatsgevonden. Dit is altijd het grootste item kleiner dan de doelwaarde.

De conclusie van bovenstaande vergelijkingen is de volgende:

• • Zoeken via een lineaire zoekopdracht is praktisch, maar traag
  • Het sorteren van data in combinatie met een binaire zoekopdracht is iets lastiger te begrijpen, maar erg snel

In de praktijk zal dit naar mijn mening neerkomen op het volgende:

Wanneer je een kleine lijst of tabel hebt en zeker weet dat de zoekwaarde zich ergens in die lijst of tabel bevindt, werkt een lineaire zoekopdracht prima. Dat dat misschien iets minder snel werkt dan de binaire zoekopdracht is verwaarloosbaar klein.

Wanneer je een heel grote lijst of tabel moet analyseren en niet weet of de zoekwaarde daarin voorkomt, werkt een binaire zoekopdracht vele malen sneller. De voorwaarde is dan echter wel dat je die lijst of tabel kunt sorteren, al dan niet in oplopende volgorde!

Een van de grote voordelen die de functie X.Zoeken biedt ten opzicht van, bijvoorbeeld, de functie Vert.Zoeken, is dat de functie X.Zoeken een reeks als resultaat kan genereren. Hier wordt mee bedoelt dat het invoeren van de functie in één cel, een resultaat kan opleveren in één of meerdere aaneengesloten cellen.

De functie kun je hierdoor typeren als een Dynamische Matrix Formule!

In bovenstaand voorbeeld is het de bedoeling om, door het kiezen van een land, de waardes voor het Inwoneraantal, BNP, Export en Import van dat betreffende land op te zoeken. Deze waardes moeten komen te staan in de cellen D8:G8. Zonder de functie X.Zoeken zou je dit resultaat kunnen verkrijgen door in elk van die cellen de functie Vert.Zoeken te gebruiken. Dat kan nog steeds, maar met de functie X.Zoeken hoeft dat niet meer. Het volstaat om in één cel de functie X.Zoeken op te nemen en daarin te verwijzen naar het bereik dat de kolommen bevat waarin de waardes staan die je zoekt. Dit bereik is D11:G20.

Dit werkt zo.

Voer in cel D8 de volgende functie in:

• • =X.ZOEKEN(C8;C11:C20;D11:G20;”—“;0;1)

De functie X.Zoeken in cel D8 bevat zes argumenten. Dit zijn:

• • Zoekwaarde > C8
  • Zoeken-Matrix > C11:C20
  • Matrix_retourneren > D11:G20
  • Indien_niet_gevonden > “—”
  • Overeenkomstmodus > 0
  • Zoekmodus > 1

De functie =X.ZOEKEN(C8;C11:C20;D11:G20;”—“;0;1) in cel D8, kun je lezen als:

Zoek in C11:C20 naar de waarde van C8 en toon uit de rij die die waarde bevat, de waardes uit de vier kolommen van bereik D11:G20.

Omdat de functie X.Zoeken verwijst naar een bereik dat vier kolommen betreft, in dit geval de kolommen D, E, F en G, weet de functie dat er resultaten moeten worden opgenomen in vier aaneengesloten cellen vanaf cel waarin de functie is ingevoerd.

Het doorvoeren van resultaten, in Excel noemt men dit overloop, komt tot uitdrukking in een blauwe rand om het bereik van de cellen die die resultaten bevatten. In dit geval is dat bereik D8:G8.

Naast het feit dat Excel een blauwe rand om die cellen toont, kun je die cellen ook niet zo maar wijzigen.

• • Klik je een dergelijke cel aan, dan wordt de tekst in de formulebalk licht grijs. Hiermee wordt aangegeven dat de functie of formule onderdeel is van een dynamische matrix formule
  • Klik je vervolgens in de formulebalk om de inhoud te wijzigen, dan verdwijnt de functie en het resultaat in de betreffende cel
  • Voer je een andere waarde in de cel in, dan verschijnt de melding #OVERLOOP!

Dit betekent dat Excel een bereik verwacht nodig te hebben om alle resultaten van de functie te kunnen tonen. Dit bereik wordt met een blauw gearceerde rand aangegeven.

!! #OVERLOOP wordt besproken in een andere artikel. Lees hier verder …

De functie X.Zoeken is een geweldige toevoeging op de al bestaande functies waarover Excel de beschikking heeft.

• • Het kunnen zoeken in een kolom ongeacht op welke positie die kolom in de tabel staat maakt de functie zeer flexibel. Het geeft de functie X.Zoeken een groot voordeel ten opzicht van de functie Vert.Zoeken
  • De mogelijkheid om een opmerking of waarde te tonen indien een zoekopdracht niet tot een overeenkomst lijdt is een praktisch voordeel dat veel functies niet hebben
  • Het kunnen ophalen van resultaten uit meerdere kolommen door de functie in een cel in te voeren (Matrix Formule) biedt de functie een ongekende vorm van dynamiek en efficiency

De veelzijdigheid en de extra’s die de functie biedt, maken de functie X.Zoeken tot een functie die elke Excel gebruiker zou moet beheersen.