Ontbrekende waarden vinden
Soms moet je twee lijsten vergelijken om te controleren of een waarde uit lijst A voorkomt in lijst B in Excel. Stel, je hebt een lijst met producten en je wilt nagaan of deze producten ook voorkomen in de productlijst van je leverancier. Hieronder vind je drie methoden om dit te doen; kies gerust de methode die het beste bij je past.
Ontbrekende waarden vinden met MATCH, ISNA en IF
Ontbrekende waarden vinden met VLOOKUP, ISNA en IF
Ontbrekende waarden vinden met COUNTIF en IF
Ontbrekende waarden vinden met MATCH, ISNA en IF
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, zoals hierboven weergegeven, kun je eerst de MATCH-functie gebruiken om de positie van een product uit jouw lijst (waarde uit lijst A) in de leverancierslijst (lijst B) op te zoeken. Als een product niet gevonden wordt, geeft MATCH de foutmelding #N/A terug. Vervolgens kun je ISNA gebruiken om deze #N/A-fouten om te zetten naar WAAR, wat betekent dat deze producten ontbreken. De IF-functie geeft dan het verwachte resultaat terug.
Algemene syntaxis
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Ontbreekt","Gevonden")
√ Opmerking: Je kunt "Ontbreekt" en "Gevonden" aanpassen naar elke gewenste waarde.
- lookup_value: De waarde waarmee MATCH de positie bepaalt als deze voorkomt in lookup_range, of #N/A als dat niet zo is. In dit geval verwijst het naar de producten in jouw lijst.
- lookup_range: Het bereik van cellen waarmee je lookup_value vergelijkt. In dit geval is dat de productlijst van de leverancier.
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, kopieer of typ je de onderstaande formule in cel H6 en druk je op Enter om het resultaat te zien:
=IF(ISNA(MATCH(30002,$B$6:$B$10,0)),"Ontbreekt","Gevonden")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=IF(ISNA(MATCH(G6,$B$6:$B$10,0)),"Ontbreekt","Gevonden")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat lookup_range in de formule niet verandert als je de formule naar andere cellen kopieert of verplaatst. Er staan echter geen dollartekens bij lookup_value, omdat je wilt dat deze dynamisch blijft. Nadat je de formule hebt ingevoerd, kun je de vulgreep naar beneden slepen om de formule toe te passen op de onderliggende cellen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Ontbreekt","Gevonden")
- MATCH(G8,$B$6:$B$10,0): De match_type 0 zorgt ervoor dat de MATCH-functie een numerieke waarde teruggeeft die de positie aangeeft van de eerste overeenkomst van 3004, de waarde in cel G8, in het bereik $B$6:$B$10. In dit geval kan MATCH de waarde echter niet vinden in het zoekbereik, dus wordt de foutmelding #N/A teruggegeven.
- ISNA(ISNA(MATCH(G8,$B$6:$B$10,0))) = ISNA(ISNA(#N/A)): ISNA controleert of een waarde de foutmelding “#N/A” is. Als dat zo is, geeft de functie WAAR terug; als het iets anders is dan “#N/A”, geeft de functie ONWAAR terug. Deze ISNA-formule geeft dus WAAR terug.
- IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Ontbreekt","Gevonden") = IF(TRUE,"Ontbreekt","Gevonden"): De IF-functie geeft 'Ontbreekt' terug als de vergelijking van ISNA en MATCH WAAR is, anders geeft hij 'Gevonden' terug. In dit geval zal de formule dus 'Ontbreekt' retourneren.
Ontbrekende waarden vinden met VLOOKUP, ISNA en IF
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, kun je de MATCH-functie hierboven vervangen door VLOOKUP. Deze werkt namelijk op dezelfde manier: als een waarde niet voorkomt in de andere lijst, geeft VLOOKUP de foutmelding #N/A terug, oftewel het product ontbreekt.
Algemene syntaxis
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Ontbreekt","Gevonden")
√ Opmerking: Je kunt "Ontbreekt" en "Gevonden" aanpassen naar elke gewenste waarde.
- lookup_value: De waarde waarmee VLOOKUP de positie bepaalt als deze voorkomt in lookup_range, of #N/A als dat niet zo is. In dit geval verwijst het naar de producten in jouw lijst.
- lookup_range: Het bereik van cellen waarmee je lookup_value vergelijkt. In dit geval is dat de productlijst van de leverancier.
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, kopieer of typ je de onderstaande formule in cel H6 en druk je op Enter om het resultaat te zien:
=IF(ISNA(VLOOKUP(30002,$B$6:$B$10,1,FALSE)),"Ontbreekt","Gevonden")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=IF(ISNA(VLOOKUP(G6,$B$6:$B$10,1,FALSE)),"Ontbreekt","Gevonden")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat lookup_range in de formule niet verandert als je de formule naar andere cellen kopieert of verplaatst. Er staan echter geen dollartekens bij lookup_value, omdat je wilt dat deze dynamisch blijft. Nadat je de formule hebt ingevoerd, kun je de vulgreep naar beneden slepen om de formule toe te passen op de onderliggende cellen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Ontbreekt","Gevonden")
- VLOOKUP(G8,$B$6:$B$10,1,FALSE): De parameter FALSE zorgt ervoor dat VLOOKUP exact zoekt naar3004, de waarde in cel G8. Als lookup_value3004 voorkomt in de eerste kolom van het bereik $B$6:$B$10, geeft VLOOKUP die waarde terug; zo niet, dan wordt de foutmelding #N/A geretourneerd. In dit geval bestaat3004 niet in het bereik, dus het resultaat is #N/A.
- ISNA(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE))) = ISNA(ISNA(#N/A)): ISNA controleert of een waarde de foutmelding “#N/A” is. Als dat zo is, geeft de functie WAAR terug; als het iets anders is dan “#N/A”, geeft de functie ONWAAR terug. Deze ISNA-formule geeft dus WAAR terug.
- IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Ontbreekt","Gevonden") = IF(TRUE,"Ontbreekt","Gevonden"): De IF-functie geeft 'Ontbreekt' terug als de vergelijking van ISNA en VLOOKUP WAAR is, anders geeft hij 'Gevonden' terug. In dit geval zal de formule dus 'Ontbreekt' retourneren.
Ontbrekende waarden vinden met COUNTIF en IF
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, kun je een eenvoudigere formule gebruiken met de functies COUNTIF en IF. Deze formule maakt gebruik van het feit dat Excel elk getal behalve nul (0) als WAAR beschouwt. Als een waarde voorkomt in de andere lijst, geeft COUNTIF het aantal keren dat deze voorkomt terug, en IF beschouwt dit als WAAR. Als de waarde niet voorkomt, geeft COUNTIF0 terug en beschouwt IF dit als ONWAAR.
Algemene syntaxis
=IF(COUNTIF("lookup_range",lookup_value),"Gevonden","Ontbreekt")
√ Opmerking: Je kunt "Gevonden" en "Ontbreekt" aanpassen naar elke gewenste waarde.
- lookup_range: Het bereik van cellen waarmee je lookup_value vergelijkt. In dit geval is dat de productlijst van de leverancier.
- lookup_value: De waarde waarmee COUNTIF het aantal keren bepaalt dat deze voorkomt in lookup_range. In dit geval verwijst het naar de producten in jouw lijst.
Om te controleren of alle producten in jouw lijst ook in de lijst van je leverancier staan, kopieer of typ je de onderstaande formule in cel H6 en druk je op Enter om het resultaat te zien:
=IF(COUNTIF($B$6:$B$10,30002),"Gevonden","Ontbreekt")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=IF(COUNTIF($B$6:$B$10,G6),"Gevonden","Ontbreekt")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat lookup_range in de formule niet verandert als je de formule naar andere cellen kopieert of verplaatst. Er staan echter geen dollartekens bij lookup_value, omdat je wilt dat deze dynamisch blijft. Nadat je de formule hebt ingevoerd, kun je de vulgreep naar beneden slepen om de formule toe te passen op de onderliggende cellen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(COUNTIF($B$6:$B$10,G8),"Gevonden","Ontbreekt")
- COUNTIF($B$6:$B$10,G8): De COUNTIF-functie telt hoe vaak3004, de waarde in cel G8, voorkomt in het bereik $B$6:$B$10. Blijkbaar bestaat3004 niet in het bereik, dus het resultaat is0.
- IF(COUNTIF($B$6:$B$10,G8),"Gevonden","Ontbreekt") = IF(0,"Gevonden","Ontbreekt"): De IF-functie beschouwt 0 als ONWAAR. De formule geeft dus 'Ontbreekt' terug, de waarde die wordt weergegeven als het eerste argument ONWAAR is.
Gerelateerde functies
De IF-functie is een van de eenvoudigste en meest bruikbare functies in een Excel-werkmap. Hiermee kun je een eenvoudige logische test uitvoeren en afhankelijk van het resultaat geeft de functie één waarde terug als het resultaat WAAR is, of een andere waarde als het resultaat ONWAAR is.
De Excel MATCH-functie zoekt naar een specifieke waarde in een bereik met cellen en geeft de relatieve positie van die waarde terug.
De Excel VLOOKUP-functie zoekt een waarde op door te vergelijken met de eerste kolom van een tabel en geeft de bijbehorende waarde uit een bepaalde kolom in dezelfde rij terug.
De COUNTIF-functie is een statistische functie in Excel die wordt gebruikt om het aantal cellen te tellen dat aan een criterium voldoet. De functie ondersteunt logische operatoren (<>, =, >, en <) en jokertekens (? en *) voor gedeeltelijke overeenkomsten.
Gerelateerde formules
Een waarde opzoeken die specifieke tekst bevat met jokertekens
Om de eerste overeenkomst te vinden die een bepaalde tekst bevat in een bereik in Excel, kun je een INDEX- en MATCH-formule gebruiken met jokertekens: het sterretje (*) en het vraagteken (?).
Gedeeltelijke overeenkomst met VLOOKUP
Soms wil je in Excel gegevens opzoeken op basis van gedeeltelijke informatie. Om dit op te lossen kun je een VLOOKUP-formule gebruiken in combinatie met jokertekens: het sterretje (*) en het vraagteken (?).
Benaderende overeenkomst met INDEX en MATCH
Soms moeten we in Excel benaderende overeenkomsten vinden, bijvoorbeeld om de prestaties van medewerkers te beoordelen, cijfers van studenten te bepalen of verzendkosten op basis van gewicht te berekenen. In deze handleiding bespreken we hoe je de functies INDEX en MATCH gebruikt om de gewenste resultaten te verkrijgen.
Dichtstbijzijnde overeenkomst opzoeken met meerdere criteria
In sommige gevallen moet je de dichtstbijzijnde of benaderende overeenkomst opzoeken op basis van meer dan één criterium. Door INDEX-, MATCH- en IF-functies te combineren kun je dit snel in Excel uitvoeren.
De Beste Office-productiviteitstools
Kutools voor Excel - Helpt U Om Uit Te Blinken In De Menigte
Kutools voor Excel Beschikt Over Meer Dan 300 Functies, Waardoor Wat U Nodig Hebt Maar Een Klik Verwijderd Is...
Office Tab - Schakel Tabbladgestuurd Lezen en Bewerken in Microsoft Office (inclusief Excel)
- Eén seconde om te schakelen tussen tientallen open documenten!
- Verminder honderden muisklikken voor u elke dag, zeg vaarwel tegen muisarm.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt Efficiënte Tabbladen naar Office (inclusief Excel), Net Als Chrome, Edge en Firefox.