Ontbrekende waarden zoeken
Er zijn situaties waarin u twee lijsten moet vergelijken om te controleren of een waarde uit lijst A ook voorkomt in lijst B in Excel. Stel dat u een lijst met producten hebt en wilt weten of deze producten ook staan in de productlijst die uw leverancier heeft verstrekt. Hiervoor staan hieronder drie methodes beschreven — kies gerust degene die het beste bij u past.
Ontbrekende waarden zoeken met VERGELIJKEN, ISNB en ALS
Ontbrekende waarden zoeken met VERT.ZOEKEN, ISNB en ALS
Ontbrekende waarden zoeken met AANTAL.ALS en ALS
Ontbrekende waarden zoeken met VERGELIJKEN, ISNB en ALS
Om erachter te komen of alle producten in uw lijst voorkomen in de lijst van uw leverancier, zoals weergegeven in de bovenstaande schermafbeelding, gebruikt u eerst de functie VERGELIJKEN om de positie op te halen van een product uit uw lijst (waarde van lijst A) in de lijst van uw leverancier (lijst B). VERGELIJKEN retourneert de fout #N/B wanneer een product niet wordt gevonden. Vervolgens geeft u dit resultaat door aan ISNB om de #N/B-fouten om te zetten in WAAR-waarden—wat aangeeft dat die producten ontbreken. Tot slot levert de ALS-functie het gewenste resultaat.
Algemene syntaxis
=IF(ISNA(MATCH()))„lookup_value",lookup_range,0)),"Missing",„Found")
√ Opmerking: U kunt ‘Ontbreekt’ en ‘Gevonden’ vervangen door waarden naar keuze.
- Zoekwaarde: De waarde die MATCH gebruikt om de positie op te halen als deze voorkomt in het zoekbereik, of fout #N/B als dat niet het geval is. In dit geval verwijst het naar de producten in uw lijst.
- Zoekbereik: Het bereik van cellen dat wordt vergeleken met de zoekwaarde. In dit geval verwijst het naar de productlijst van de leverancier.
Om erachter te komen of alle producten in uw lijst voorkomen in de lijst van uw leverancier, kopieert of typt u de onderstaande formule in cel H6 en drukt u op Enterom het resultaat te verkrijgen:
=IF(ISNA(MATCH()))30002,$B$6:$B$10,0)),"Missing",„Found")
Of gebruik Een celverwijzing om de formule dynamisch te maken:
=IF(ISNA(MATCH()))G6,$B$6:$B$10,0)),"Missing",„Found")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat het zoekbereik in de formule niet verandert wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de zoekwaarde, omdat u deze dynamisch wilt houden. Sleep na het invoeren van de formule de vulgreep omlaag om de formule op de cellen eronder toe te passen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),"Missing",„Found")
- MATCH(G8,$B$6:$B$10,0):Het argument match_type 0 dwingt de functie MATCH om een numerieke waarde te retourneren die de positie aangeeft van de eerste overeenkomst van 3004 — de waarde in cel G8 — in de matrix $B$6:$B$10. In dit geval kon MATCH de waarde echter niet vinden in de zoekmatrix, dus retourneert deze de #N/B-fout.
- ISNB()MATCH(G8,$B$6:$B$10,0))=ISNB()#N/B):De functie ISNB controleert of een waarde de foutmelding „#N/B” is. Als dat het geval is, retourneert de functie WAAR; als de waarde iets anders is dan „#N/B”, retourneert deze ONWAAR. Deze ISNB-formule geeft daarom WAAR terug.
- ALS()ISNB()MATCH(G8,$B$6:$B$10,0)),"Ontbreekt",„Gevonden") = ALS(WAAR,"Ontbreekt",„Gevonden"):De ALS-functie retourneert „Ontbreekt” wanneer de combinatie van ISNB en MATCH WAAR oplevert; anders geeft deze „Gevonden” terug. De formule levert dus Ontbreekt op.
Ontbrekende waarden zoeken met VLOOKUP, ISNA en IF
Om te controleren of alle producten in uw lijst ook voorkomen in de lijst van uw leverancier, kunt u de MATCH-functie hierboven vervangen door VLOOKUP. Deze werkt namelijk op vergelijkbare wijze: als een waarde niet in de andere lijst voorkomt, retourneert VLOOKUP de fout #N/B.
Algemene syntaxis
=IF(ISNA(VLOOKUP()))„lookup_value",lookup_range,1,FALSE)),"Missing",„Found")
√ Opmerking: U kunt ‘Ontbreekt’ en ‘Gevonden’ vervangen door waarden naar keuze.
- Zoekwaarde: De waarde die VLOOKUP gebruikt om de overeenkomstige gegevens op te halen wanneer deze voorkomt in het zoekbereik, of fout #N/B als dat niet het geval is. In dit geval verwijst dit naar de producten in uw lijst.
- Zoekbereik: Het bereik van cellen dat wordt vergeleken met de zoekwaarde. In dit geval verwijst het naar de productlijst van de leverancier.
Om te controleren of alle producten in uw lijst voorkomen in de lijst van uw leverancier, kopieert of typt u de onderstaande formule in cel H6 en drukt u op Enterom het resultaat te krijgen:
=IF(ISNA(VLOOKUP()))30002,$B$6:$B$10,1,FALSE)),"Missing",„Found")
Of gebruik Een celverwijzing om de formule dynamisch te maken:
=IF(ISNA(VLOOKUP()))G6,$B$6:$B$10,1,FALSE)),"Missing",„Found")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat het zoekbereikin de formule niet verandert wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de zoekwaardeomdat u deze dynamisch wilt houden. Sleep na het invoeren van de formule de vulgreep omlaag om de formule op de cellen eronder toe te passen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing",„Found")
- VLOOKUP(G8,$B$6:$B$10,1,ONWAAR): Het argument bereik_zoeken ONWAAR dwingt de functie VLOOKUP om exact de waarde op te zoeken die overeenkomt met 3004, de waarde in cel G8. Als de zoekwaarde 3004 voorkomt in de 1e kolom van het bereik $B$6:$B$10, retourneert VLOOKUP die waarde; anders geeft de functie de foutwaarde #N/B terug. Omdat 3004 niet in het bereik voorkomt, is het resultaat #N/B.
- ISNB()VLOOKUP(G8,$B$6:$B$10,1,ONWAAR))=ISNB()#N/B): De functie ISNB controleert of een waarde de foutmelding „#N/B” is. Als dat het geval is, retourneert de functie WAAR; als de waarde iets anders is dan de fout „#N/B”, retourneert deze ONWAAR. Deze ISNB-formule geeft daarom WAAR.
- ALS()ISNB()VLOOKUP(G8,$B$6:$B$10,1,ONWAAR)),"Ontbreekt",„Gevonden") = ALS(WAAR,"Ontbreekt",„Gevonden"):De ALS-functie retourneert „Ontbreekt” wanneer de combinatie van ISNB en VLOOKUP WAAR oplevert; anders retourneert ze „Gevonden”. De formule geeft dus Ontbreekt.
Ontbrekende waarden zoeken met COUNTIF en IF
Om te controleren of alle producten in uw lijst voorkomen in de lijst van uw leverancier, kunt u een eenvoudigere formule gebruiken met de functies COUNTIF en IF. De formule maakt gebruik van het feit dat Excel elk getal behalve nul (0) als WAAR evalueert. Als een waarde dus voorkomt in een andere lijst, geeft de COUNTIF-functie het aantal keren dat deze waarde in die lijst voorkomt; IF beschouwt dit getal dan als WAAR. Als de waarde niet in de lijst voorkomt, retourneert COUNTIF 0, en IF beschouwt dit als ONWAAR.
Algemene syntaxis
=IF(COUNTIF())„lookup_range",lookup_value),"Found",„Missing")
√ Opmerking: U kunt „Found” en „Missing” naar eigen wens wijzigen in andere waarden.
- Zoekbereik: Het bereik van cellen dat wordt vergeleken met de zoekwaarde. In dit geval verwijst het naar de productlijst van de leverancier.
- Zoekwaarde: De waarde die COUNTIF gebruikt om te bepalen hoe vaak deze voorkomt in het zoekbereik. In dit geval verwijst dat naar de producten in uw lijst.
Om te controleren of alle producten in uw lijst voorkomen in de lijst van uw leverancier, kopieert of typt u de onderstaande formule in cel H6 en drukt u op Enterom het resultaat te krijgen:
=IF(COUNTIF())$B$6:$B$10,30002),"Found",„Missing")
Of gebruik Een celverwijzing om de formule dynamisch te maken:
=IF(COUNTIF())$B$6:$B$10,G6),"Found",„Missing")
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat het zoekbereikin de formule niet verandert wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de zoekwaardeomdat u deze dynamisch wilt houden. Sleep na het invoeren van de formule de vulgreep omlaag om de formule op de cellen eronder toe te passen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF()COUNTIF($B$6:$B$10,G8),"Found",„Missing")
- AANTAL.ALS($B$6:$B$10,G8): De functie AANTAL.ALS telt hoe vaak 3004, de waarde in cel G8, voorkomt in het bereik $B$6:$B$10. Omdat 3004 niet voorkomt in dit bereik, is het resultaat 0.
- ALS()AANTAL.ALS($B$6:$B$10,G8),"Gevonden",„Ontbreekt") = ALS(0,"Gevonden",„Ontbreekt"):De ALS-functie evalueert 0 als ONWAAR. De formule retourneert daarom Ontbreekt, de waarde die wordt geretourneerd wanneer het eerste argument ONWAAR is.
Gerelateerde functies
De ALS-functie is een van de eenvoudigste en meest nuttige functies in een Excel-werkboek. Ze voert een eenvoudige logische test uit en retourneert, afhankelijk van het resultaat van die vergelijking, één waarde als het resultaat WAAR is en een andere waarde als het resultaat ONWAAR is.
De Excel-functie VERGELIJKEN zoekt naar een specifieke waarde in een celbereik en geeft de relatieve positie van die waarde terug.
De Excel-functie VERT.ZOEKEN zoekt een waarde door deze te vergelijken met de eerste kolom van een tabel en geeft de bijbehorende waarde uit een opgegeven kolom in dezelfde rij terug.
De functie AANTAL.ALS is een statistische functie in Excel waarmee u het aantal cellen telt dat voldoet aan een bepaald criterium. De functie ondersteunt logische operatoren (zoals > en <=) en jokertekens (? en *) voor gedeeltelijke overeenkomsten.
Gerelateerde formules
Zoek een waarde die specifieke tekst bevat met jokertekens
Om de eerste overeenkomst te vinden die een bepaalde tekstreeks bevat binnen een bereik in Excel, combineert u de functies INDEX en VERGELIJK met jokertekens – het sterretje (*) en het vraagteken (?).
Gedeeltelijke overeenkomst met VERT.ZOEKEN
Soms moet Excel gegevens ophalen op basis van slechts gedeeltelijke informatie. Dit lost u eenvoudig op met een VERT.ZOEKEN-formule in combinatie met jokertekens: het sterretje (*) en het vraagteken (?).
Benaderende overeenkomst met INDEX en VERGELIJKEN
Soms moeten we benaderende overeenkomsten vinden in Excel om de prestaties van medewerkers te beoordelen, cijfers van studenten te beoordelen, portokosten te berekenen op basis van gewicht, enzovoort. In deze handleiding bespreken we hoe u de functies INDEX en VERGELIJKEN kunt gebruiken om de gewenste resultaten op te halen.
Zoek de dichtstbijzijnde overeenkomende waarde met meerdere criteria
In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomende waarde opzoeken op basis van meer dan één criterium. Met een combinatie van de functies INDEX, VERGELIJKEN en ALS kunt u dit snel realiseren in Excel.
De beste Office-productiviteitshulpmiddelen
Kutools voor Excel – Helpt u om op te vallen tussen de massa
Kutools voor Excel Beschikt over meer dan 300 functies,zodat u alles wat u nodig heeft binnen één klik bereik heeft...
Office Tab – Schakel tabbladenlezen en -bewerken in Microsoft Office (inclusief Excel) in
- Schakel in één seconde tussen tientallen geopende documenten!
- Bespaar honderden muisklikken per dag en zeg vaarwel tegen muisarm.
- Verhoog uw productiviteit met 50 % bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabs naar Office (inclusief Excel), net als in Chrome, Edge en Firefox.