Hoe controleert u of een cel één van meerdere waarden bevat in Excel?
In veel zakelijke, analytische of gegevensbeoordelingsscenario’s hebt u waarschijnlijk een lijst met tekstreeksen in kolom A en wilt u controleren of elke cel één van een reeks opgegeven waarden bevat — zoals die in het bereik D2:D7 staan. Denk bijvoorbeeld aan enquêtegegevens, logbestanden of productlijsten: het is dan cruciaal om te achterhalen of vermeldingen relevante trefwoorden, productcodes of verboden termen bevatten. Als een cel één van de items uit uw opgegeven lijst bevat, retourneert Excel Waar; anders retourneert het Onwaar, zoals in de onderstaande schermafbeelding wordt geïllustreerd. Dit artikel legt praktische methodes uit om te controleren of een cel één van meerdere waarden bevat uit een ander bereik, en biedt diverse benaderingen voor verschillende Excel-versies en gebruikersbehoeften.
Controleer of een cel één van meerdere waarden uit een lijst bevat met formules
Om te bepalen of een cel één van de tekstwaarden uit een ander bereik bevat, kunt u een matrixformule gebruiken die efficiënt werkt, zelfs bij grote datasets. Deze methode is bijzonder handig wanneer u resultaten nodig hebt in de vorm van logische waarden ()Waar/Onwaar) voor gebruik in verdere formules, filters of logische tests. Deze techniek werkt in de meeste moderne Excel-versies.
Typ de volgende formule in een lege cel (bijvoorbeeld cel B2, naast uw oorspronkelijke gegevens) en sleep de vulgreep naar beneden om deze toe te passen op andere cellen indien nodig. De formule retourneert **Waar** als de cel een van de tekstwaarden uit uw beperkte bereik bevat, en anders **Onwaar**. Zie schermafbeelding:
Tips en opmerkingen:
- Deze formule voert een hoofdletterongevoelige controle uit. Hebt u juist hoofdlettergevoeligheid nodig? Overweeg dan om een hulpkolom te gebruiken of formules op een geavanceerdere manier te combineren.
- Gebruik de volgende aangepaste formule als u in plaats van Waar/Onwaar een resultaat „Ja” of „Nee” wilt:
- D2:D7 is uw bereik met waarden (de „lijst”); A2 is de cel die u wilt testen.
- Let op lege cellen of niet-tekstgegevens: de ZOEKEN-functie vereist geldige tekst als invoer, en lege waarden kunnen onverwachte resultaten (zoals „Waar”) opleveren.
Geef de overeenkomsten weer als een cel één van meerdere waarden uit een lijst bevat met formules
Soms is het informatiever om te tonen welke waarden uit de lijst daadwerkelijk in elke cel voorkomen, in plaats van alleen een Waar/Onwaar-resultaat weer te geven. Bijvoorbeeld bij het scannen van productbeschrijvingen of opmerkingen op specifieke trefwoorden: dan wilt u mogelijk alle gevonden waarden retourneren voor verdere analyse of rapportage. Gebruik de volgende formule om alle overeenkomende waarden weer te geven, gescheiden door komma’s, zoals hier wordt geïllustreerd:
Voer deze formule in een lege cel in (bijv. B2); deze geeft alle waarden uit D2:D7 die zijn gevonden in A2, gescheiden door komma’s:
Opmerking: Hier is D2:D7 de verzameling waarden waarin u zoekt, en A2 de cel waarin u zoekt.
Nadat u de formule hebt getypt, drukt u op Ctrl + Shift + Enter. Vervolgens sleept u de vulgreep naar beneden om de formule toe te passen op andere rijen, zoals wordt weergegeven op de resultaat-schermafbeelding:
- De TEKST.SAMENVOEGEN-functie is alleen beschikbaar in Excel 2019 en Office 365. Gebruik in oudere versies van Excel de volgende matrixformule: typ deze in een lege cel en druk daarna op Ctrl + Shift + Enter:
Sleep de formule zo ver mogelijk naar rechts om alle mogelijke overeenkomsten te vangen, en sleep deze vervolgens naar beneden voor elke rij. Extra kolommen blijven leeg wanneer er minder overeenkomsten zijn. Deze indeling is bijzonder handig als u de overeenkomsten in afzonderlijke kolommen wilt weergeven:
Als u fouten tegenkomt, controleer dan nogmaals de bereiken, zorg ervoor dat het gebied D2:D7 correct is en bevestig dat u het juiste scheidingsteken voor uw regio gebruikt (komma of puntkomma).
Markeer de overeenkomsten als een cel één van meerdere waarden uit een lijst bevat met een handige functie
Wilt u trefwoorden of zinnen die overeenkomen met een waarde uit uw lijst visueel markeren binnen elke cel? Dan laat deze functie belangrijke gegevens direct opvallen voor beoordeling of actie. De functie Trefwoordmarkering in Kutools voor Excel is speciaal ontworpen voor dit scenario en stelt u in staat om elk voorkomen van uw opgegeven woorden in het gegevensbereik snel te markeren—zonder dat u formules of VBA-code hoeft te schrijven. In grote werkbladen of complexe datasets is deze functie bijzonder waardevol, juist wanneer handmatige inspectie onpraktisch is.
Volg na installatie van Kutools voor Exceldeze stappen:
1. Ga naar Kutools > Tekst > Trefwoordmarkering om het dialoogvenster te openen, zoals getoond:
2. Voer het volgende uit in het Trefwoordmarkering-dialoogvenster:
- Selecteer uw doelgegevensbereik in het vak Bereik.
- Kies of typ handmatig de trefwoorden in het vak Trefwoord (gescheiden door komma’s).
- Geef een markeerkleur op met de optie Lettertypekleur van trefwoord.
3. Klik op OK. Alle overeenkomende woorden binnen het geselecteerde bereik worden gemarkeerd met uw gekozen letterkleur:
- Deze functie past de weergaveopmaak van overeenkomende trefwoorden direct aan, waardoor het voor teams die formule-uitvoer mogelijk niet begrijpen intuïtiever wordt om resultaten te beoordelen of te delen.
- Deze methode is bijzonder effectief bij een zeer lange lijst met trefwoorden of een uitgebreid tekstbereik, of wanneer u meerdere criteria tegelijkertijd wilt markeren.
- Controleer altijd nogmaals uw geselecteerde bereik en trefwoorden voordat u bevestigt, om onbedoelde markeringen te voorkomen.
Controleer of een cel één van meerdere waarden bevat met behulp van Voorwaardelijke opmaak gebruiken
De ingebouwde Excel-functie Voorwaardelijke opmaak gebruiken is een andere effectieve methode om cellen te markeren die een waarde bevatten uit uw opgegeven lijst. Deze oplossing is ideaal om relevante rijen in één oogopslag visueel te herkennen, vooral bij gegevenscontrole, foutopsporing of nalevingstaken. In tegenstelling tot de Trefwoordmarkering-functie van Kutools vereist deze methode geen add-ins en is dus beschikbaar in elke standaard Excel-installatie.
Zo gebruikt u Voorwaardelijke opmaak gebruiken met een formule:
- Selecteer de cellen in uw gegevensbereik (bijv.)A2:A20) die u wilt bewaken.
- Ga naar het tabblad Start, klik op Voorwaardelijke opmaak gebruiken > Nieuwe regel.
- Kies in het dialoogvenster Nieuwe opmaakregel de optie Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
- Voer deze formule in, ervan uitgaande dat D2:D7 uw waarden bevat en A2 de eerste gegevenscel is:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Klik op Opmaak, stel de gewenste opmaak in (zoals Vulkleur) en druk op OK.
Alle cellen die een item uit de lijst D2:D7 bevatten, worden nu automatisch gemarkeerd.
- Deze methode is dynamisch: wanneer u het bereik D2:D7 bijwerkt, past de opmaak zich automatisch aan.
- Voorwaardelijke opmaak is alleen-lezen: het markeert cellen visueel, maar levert geen resultaten op in een aparte kolom of voor verdere berekeningen.
- Het gebruik van formulegebaseerde voorwaardelijke opmaak is krachtig, maar bij zeer grote datasets kan de prestatie vertragen door herhaalde berekeningen.
- Houd er rekening mee dat ZOEKEN niet hoofdlettergevoelig is. Voor een hoofdlettergevoelige aanpak zijn geavanceerdere technieken of hulpkolommen vereist.
Meer gerelateerde artikelen:
- Vergelijk twee of meer tekstreeksen in Excel
- Als u twee of meer tekstreeksen in een werkblad wilt vergelijken – hoofdlettergevoelig of niet, zoals in de volgende schermafbeelding wordt weergegeven – bespreek ik in dit artikel enkele handige formules om deze taak eenvoudig uit te voeren in Excel.
- Als cel tekst bevat, dan weergeven in Excel
- Als u een lijst met tekstreeksen hebt in kolom A en een rij trefwoorden, moet u nu controleren of de trefwoorden voorkomen in de tekstreeks. Als de trefwoorden in de cel voorkomen, worden ze weergegeven; zo niet, dan wordt een lege cel weergegeven, zoals op de volgende schermafbeelding wordt getoond.
- Tel het aantal trefwoorden dat een cel bevat op basis van een lijst
- Wilt u het aantal trefwoorden tellen dat in een cel voorkomt op basis van een lijst met cellen? Dan helpt de combinatie van de functies SOMPRODUCT, ISGETAL en ZOEKEN u dit probleem eenvoudig oplossen in Excel.
- Zoeken en Vervangen Meerdere waarden in Excel
- Normaal gesproken helpt de functie Zoeken en Vervangen u om een specifieke tekst te vinden en deze door een andere te vervangen, maar soms wilt u mogelijk meerdere waarden tegelijkertijd zoeken en vervangen. Denk bijvoorbeeld aan het vervangen van alle „Excel”-teksten door „Excel2019”, „Outlook” door „Outlook2019”, enzovoort, zoals in de onderstaande schermafbeelding wordt weergegeven. In dit artikel introduceer ik een formule waarmee u deze taak in Excel eenvoudig kunt uitvoeren.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins