Vanuit mijn trainingen weet ik dat VERT.ZOEKEN veel wordt gebruikt. Gelukkig gebruiken steeds meer mensen de verbeterde versie X.ZOEKEN, die ook geschikt is voor HOR.ZOEKEN. Maar in sommige situaties werken die niet. Dan is de FILTER-functie beter om te gebruiken.
Het gebruikte voorbeeld
Hieronder zie je een bereik in de vorm van een lijst: een beschrijving boven elke kolom en eronder de rijen met de gegevens (in dit voorbeeld heb ik er geen Excel-tabel van gemaakt).
In G2 staat een order-id. Van dat ID wil je H2 en I2 de naam van de verkoper en het orderbedrag ophalen uit de lijst.
Zoeken met VERT.ZOEKEN
Dat zal hier niet lukken! Waarom niet? Omdat het Order-id in de lijst achter de naam van de verkoper en het bedrag staat. Je kunt nooit vert.zoeken gebruiken als de zoekwaarde rechts van de resultaatkolom staat!
Zo staat het ook in de beschrijving:
Om deze functie te kunnen gebruiken moet je dus de tabel ombouwen, zodat het order-id links van de kolommen verkoper en orderbedrag komt! Niet handig!
Een alternatief zou zijn om een combinatie van de functies INDEX en VERGELIJKEN te gebruiken. Lastiger!
Daarom is het veel slimmer om X.ZOEKEN te gebruiken, want die heeft daar geen last van!
Zoeken met X.ZOEKEN
Zet maak je deze berekening H2.
① Hier zet je het celadres van de order die je wilt zoeken: G2.
② Hier geef je het gebied op wáár de zoekwaarde gezocht moet worden: E2:E22.
③ Hier geef je het gebied op waar je het resultaat uit wilt halen: B2:C22.
Je ziet, hier kun je ook 2 kolommen opgeven (dat kan sowieso niet bij vert.zoeken)!
④ Hier zie je de uitkomst in het werkblad.
Beperking!
Een beperking van X.ZOEKEN is dat zodra de zoekwaarde is gevonden er niet verder gezocht wordt.
VERT.ZOEKEN heeft diezelfde beperking.
Als een order-id vaker voorkomt (bijvoorbeeld 10800) zal alleen de eerstgevonden waarde worden getoond.
Zoeken met FILTER
De functie die dit wel kan oplossen is de FILTER-functie. Hieronder het resultaat:
- Bij ① zie je dat elke keer dat order-id 10800 voorkomt worden de naam van de verkoper en het bedrag weergegeven.
- Bij ② zie je dat het ook werkt als een order-id slechts één keer werkt het.
Hoe gebruik je deze functie FILTER?
Typ in de cel =FILTER( en daarna geef je de argumenten op gescheiden met puntkomma’s (;). Verheet bi het typen van =FILTER het haakje openen niet!!
Als je na het typen van =FILTER( op fx klikt (zie formulebalk), dan krijg je een invulscherm om de argumenten op te geven. Dat heb ik gebruikt in de afbeelding hieronder.
Bij ① Matrix geef je op wat je als resultaat wilt zien: B2:C22 (kolommen Verkoper en Orderbedrag).
Bij ② Opnemen geef je het gewenste filter op. Dat doe je door de kolom aan te geven (kolom Order-id , E2:E22) en waaraan die moet voldoen (=gevraagde Order-id, =G2).
En als het niet wordt gevonden?
Stel dat je een Order-id opgeeft dat niet voorkomt, wat gebeurt er dan?
Dan krijg je een #BEREKENEN!-foutmelding (Engels: #CALC!).
Om dat te voorkomen kun je ook het 3e argument Als_leeg gebruiken bij de filter-functie: wat moet er gebeuren als geen enkel item voldoet?
Als dit een tekst moet zijn, moet dat tussen “ “ komen zoals je ziet ②. In de cel zelf komen die dubbele aanhalingstekens “ “ niet voor zoals je ziet bij ①.
Eindconclusie
- Een ZOEKEN-functie kun je gebruiken om maximaal één item te zoeken in een cellenbereik en uit een resultaatbereik het bijbehorende op te halen. Als de zoekwaarde vaker voorkomt, zal het toch maar één resultaat geven!
- De FILTER-functie filtert in feite de lijst op alle items die voldoen aan het opgegeven criterium.
Dat kan één resultaat zijn, maar ook meer.
Zijn er ook nadelen aan de Filter-functie?
Deze functie is alleen te gebruiken in de nieuwere versies van Excel. Dat geldt overigens ook voor x.zoeken. Beide functies zijn te gebruiken in Excel 365 en vanaf versie 2021.
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Ze geeft voor Accountancy Vanmorgen en Fiscaal Vanmorgen de Excel Expert-trainingen. Deze nieuwe functie komt bijvoorbeeld aan de orde in de Expert-training Leer nieuwe geheimen van Excel.
Elke week plaatst Saskia een Snelle Korte Tip op YouTube. Er is bijvoorbeeld een video-uitleg over de FILTER-functie (https://youtu.be/wA9dKC4WTKE).
