Zoeken kan in Excel op allerlei manieren. Afhankelijk van wat u zoekt, wat u met die gegevens wilt doen en door hoeveel gegevens gezocht moet worden, kiest u voor een bepaalde methode. Cm: zet ZOEKEN, VERT.ZOEKEN, VERGELIJKEN EN INDEX op een rij.
De basis
De makkelijkste manier om te zoeken is met de sneltoetsencombinatie Ctrl-F te gebruiken. Excel opent dan een venster waarin u een woord, getal of tekencombinatie kunt zoeken. Gebruikt u liever de menu-structuur? Dan kiest u in het Lint Start => Zoeken en Selecteren => Zoeken. Hier vult u de waarde in die u zoekt, waarop Excel alle cellen met die exacte waarde selecteert. U gaat ze stuk voor stuk af, tot u de cel heeft gevonden die u bedoelt.
Vectorzoeken
De zoekfunctie zoals hierboven beschreven, doorzoekt de hele tabel. Maar het is in Excel ook mogelijk om alleen in een kolom, rij of bereik te zoeken. Daarvoor bestaat de functie ZOEKEN. Deze methode kent twee variaties: vectorzoeken en matrixzoeken. Bij vectorzoeken wordt gezocht naar een waarde in een specifieke rij of kolom. Matrixzoeken zoekt binnen een bereik. Maar matrixzoeken kan veel makkelijker op een andere manier, namelijk met VLOOKUP. Die methode we hier daarom niet behandelen.
>> De zoekfunctie X.ZOEKEN (XLOOKUP) wordt uitgelegd in dit artikel en Tony De Jonker illustreert de functie met dit voorbeeld.
Laten we beginnen met een voorbeeldtabel. In deze tabel willen we graag de voorraad bij een artikelnummer zoeken, met behulp van de ZOEKEN-functie.
Als eerste typen we nu in de cel H4:
=ZOEKEN(
Vervolgens geven we aan wat we zoeken. Dat kan een getal, een woord of een verwijzing naar een cel zijn. Als we eenmalig op zoek zijn naar deze gegevens kunnen we nu het artikelnummer 145644 invoeren. Maar als we vaker gaan zoeken kunnen we beter een verwijzing naar de cel H4 gebruiken, zodat we niet steeds opnieuw een formule hoeven te maken voor elke nieuwe zoekopdracht.
>> Lees ook: Excel: Dynamische matrixfuncties
De formule is nu:
=ZOEKEN(H4;
Vervolgens gaan we Excel vertellen in welk bereik deze waarde te vinden is. In dit geval is het een kleine voorbeeldtabel, waardoor het bereik A2:A21 is. De formule is nu:
=ZOEKEN((H4;A2:A21;
Nu gaan we opgeven welke kolom de waarde bevat die we vervolgens willen zien. In dit geval staat de voorraad in kolom D. Omdat we voor de A-kolom het bereik 2:21 hebben opgegeven, doen we dat nu ook. De formule is nu:
=ZOEKEN((H4;A2:A21;D2:D21)
Excel heeft daarbij gevonden dat de voorraad van dat artikel 39 stuks is.
VLOOKUP / Vert. Zoeken
Iets geavanceerder zoeken kan ook, door middel van de functie VLOOKUP, in het Nederland VERT.ZOEKEN. Daarbij wordt een cel gezocht bij een bekende waarde. Dat is bijvoorbeeld handig als we willen zoeken in een matrix. We nemen nogmaals het voorbeeld van artikelnummer 145644. We zoeken dus in de kolom naar de celinhoud achter een ingegeven waarde. In dit voorbeeld willen we graag weten hoeveel gele rokken in maat L er nog zijn. Daartoe heb ik een zoekveld gemaakt in de voorbeeldtabel. In H4 willen we straks een artikelnummer kunnen ingeven, waarmee we gaan zoeken. Het resultaat moet vervolgens in de cel H5 worden getoond.
>> Foutmelding bij VERT.ZOEKEN? Lees de derde tip uit dit artikel.
Als eerste gaan we nu naar het veld waar we het zoekresultaat willen zien, in dit geval de cel H5. Daar typen we
=VERT.ZOEKEN(
Excel vraagt nu als eerste naar de plaats waar de waarde staat die het moet gaan zoeken. In dit geval is dat de cel H4. We sluiten dit deel van de formule af met ;. De formule is dus
=VERT.ZOEKEN(H4;
Vervolgens wil Excel weten in welk gebied het moet zoeken. Voor dit voorbeeld is maar een kleine tabel tabel gemaakt, daarom is het bereik nu A2 tot en met E21. In de formule is dat A2:E21. De formule wordt daarmee
=VERT.ZOEKEN(H4;A2:E21;
Als laatste moeten we Excel nog vertellen op welke plek de cel staat die Excel moet tonen. In dit geval staat de voorraad in de vierde kolom vanaf het artikelnummer. Hier staat dus 4. We sluiten af met een haakje. De formule is nu
=VERT.ZOEKEN(H4;A2:E21;4)
Als we nu enter toetsen, toont Excel het getal 39 in de cel H5.
Er bestaat overigens volgens dezelfde syntax ook horizontaal zoeken. Daarbij wordt niet een waarde getoond in dezelfde rij, maar een waarde in dezelfde kolom.
Match / Vergelijken
Het belangrijkste nadeel van verticaal zoeken is de tijd. Naar mate de tabel groter wordt, duurt het langer om een zoekresultaat terug te krijgen. Voor financials, die vaak met duizenden rijen en enkele tientallen kolommen werken, kan dat erg tijdrovend worden. Zeker als er meerdere zoekopdrachten moeten worden uitgevoerd. Dan kan MATCH (in het Nederlands VERGELIJKEN) uitkomst bieden. Deze functie geeft geen waarde uit een kolom, maar het rijnummer waar de zoekwaarde staat.
>> Een financieel praktijkvoorbeeld van de functie VERGELIJKEN leest u in dit artikel van Tony De Jonker.
Typ in de cel waar u het resultaat wilt zien:
=VERGELIJKEN(
Als eerste vertellen we Excel weer wat we zoeken. In dit geval staat die waarde in de cel H4. De formule is nu:
=VERGELIJKEN(H4;
Vervolgens geven we weer het bereik door. In dit voorbeeld is dat A2:A21. Het artikelnummer staat immers in de rij A. 0De formule is nu
=VERGELIJKEN(H4,A2:A21
Als laatste moeten we aangeven of we een waarde lager dan het getal (-1), een exacte match (o) of een waarde hoger dan het getal (1) in H4 willen. Als u nu niets invult, kiest Excel automatisch voor 1. Wij willen de exacte waarde van het artikelnummer in H4, dus kiezen we 0. De formule is nu
=VERGELIJKEN(H4;A1:A18;0)
Als we nu Enter toetsen, geeft de formule het resultaat 10. Daarmee zien we dus dat de waarde in de cel H4 op de 10e plaats van het bereik staat. Omdat ons bereik A1:A21 was, staat de waarde in A10. Als we het bereik hadden verandert naar A2:A21, zou de Excel nu een 9 geven. De waarde staat dan namelijk op de 9e plaats in het bereik.
>> Lees ook: Excel-tutorial: Omzetscenario’s maken met Scenariobeheer (Scenario Manager)
Index
Nu we de basis van matchen / vergelijken kennen, is er nog een mogelijkheid: Index. Die zoekoptie is er niet altijd, want er zijn twee belangrijke voorwaarden aan verbonden: titels en kolomwaardes. De brongegevens moeten voorzien zijn van koppen waar we kunnen zoeken en er moet een kolom zijn met waardes waar we in kunnen zoeken. Laten we weer terug gaan naar het voorbeeld. We willen weer weten hoeveel gele rokken in maat L we hebben, maar dit keer gebruiken we de index-functie. Daarbij kunnen we nog steeds handig gebruik maken van de eerder opgezette Match-functie. Voor het gemak is die nu verplaatst naar de zijkant en is ook een match-functie gemaakt voor de kolom, zodat de index-functie zo begrijpt dat we willen weten wat de voorraad is, zie ook de afbeelding hieronder.
We beginnen nu met:
=INDEX(
Vervolgens geven we het bereik van de functie aan. In dit geval is dat A1:E21. De formule is nu:
=INDEX(A1:E21;
Nu kunnen we de eerder gemaakte MATCH-functies in de cellen K4 en K5 nesten. Daarmee wordt de formule:
=INDEX(A1:E21;K4;K5) of, als nesten ongewenst is:
=INDEX(A1:E21;VERGELIJKEN(H4;A1:A18;0);VERGELIJKEN(H5;A1:E1;0))
Excel geeft nu als resultaat: 39.
>> Lees ook: Excel tutorial: Begroting bijstellen met Doelzoeken (Goal Seek) en Oplosser (Solver)
Deze methode verdient misschien niet de schoonheidsprijs, maar heeft als groot voordeel dat het in grote tabellen aanzienlijk sneller werkt.
