Hoe controleer je of een cel een van meerdere waarden bevat in Excel?
In veel zakelijke, analytische of gegevenscontrole situaties heb je mogelijk een lijst met tekstreeksen in kolom A en wil je controleren of elke cel een van een aantal opgegeven waarden bevat, zoals die vermeld zijn in bereik D2:D7. Bijvoorbeeld in enquêtegegevens, logs of productlijsten is het belangrijk om te bepalen of er relevante trefwoorden, productcodes of verboden termen voorkomen in de invoer. Als een cel een van de items uit jouw opgegeven lijst bevat, kun je Excel laten Ja (True) weergeven; zo niet, dan Nee (False), zoals te zien is in de onderstaande screenshot. Dit artikel legt praktische manieren uit om te controleren of een cel een van meerdere waarden in een ander bereik bevat, en geeft verschillende methodes voor verschillende Excel-versies en gebruikersbehoeften.
Controleer of een cel een van meerdere waarden uit een lijst bevat met formules
Om vast te stellen of een cel een van de tekstwaarden uit een ander bereik bevat, kun je een matrixformule gebruiken die ook bij grote datasets efficiënt werkt. Deze methode is vooral nuttig als je uitkomsten als logische waarden (Ja/Nee) wilt gebruiken, bijvoorbeeld in verdere formules, voor filtering of logische testen. Deze techniek werkt in de meeste moderne Excel-versies.
Typ de volgende formule in een lege cel (bijvoorbeeld in cel B2 naast je oorspronkelijke gegevens) en sleep vervolgens de vulgreep naar beneden om deze toe te passen op andere cellen waar nodig. Als de cel een van de tekstwaarden in jouw opgegeven bereik bevat, wordt Ja weergegeven; zo niet, dan Nee. Zie screenshot:
Tips en opmerkingen:
- Deze formule controleert niet hoofdlettergevoelig (case-insensitive). Mocht je hoofdlettergevoelig willen controleren, gebruik dan een hulpkolom of combineer formules op een meer geavanceerde manier.
- Wil je een “Ja” of “Nee” resultaat in plaats van Ja/Nee, gebruik dan de volgende aangepaste formule:
- D2:D7 is je waardenbereik (de “lijst”); A2 is de te testen cel.
- Let op bij lege cellen of niet-tekstgegevens: de SEARCH-functie vereist een geldige tekstinvoer, en lege waarden kunnen tot onverwachte uitkomsten (“Ja”) leiden.
Toon de overeenkomende waarden als een cel een van meerdere waarden uit een lijst bevat met formules
Soms is het nuttiger om te tonen welke waarden uit de lijst daadwerkelijk voorkomen in elke cel, in plaats van alleen een Ja/Nee resultaat te laten zien. Bijvoorbeeld bij het scannen van productomschrijvingen of opmerkingen voor specifieke trefwoorden, wil je mogelijk alle gevonden waarden weergeven voor verdere analyse of rapportage. Met de onderstaande formule kun je alle overeenkomende waarden tonen, gescheiden door komma's, zoals hier weergegeven:
Voer deze formule in een lege cel in (bijvoorbeeld B2) en deze geeft alle waarden uit D2:D7 weer die gevonden zijn in A2, gescheiden door komma’s:
Opmerking: Hierbij is D2:D7 de verzameling waarden waarop wordt gezocht, en A2 de cel waarin wordt gezocht.
Druk na het invoeren van de formule op Ctrl + Shift + Enter. Sleep daarna de vulgreep naar beneden om de formule toe te passen op andere rijen, zoals te zien is in de resultaat-screenshot.
- De TEXTJOIN-functie is alleen beschikbaar in Excel2019 en Office365. In eerdere Excel-versies gebruik je de volgende matrixformule door deze in een lege cel in te voeren en vervolgens op Ctrl + Shift + Enter te drukken:
Sleep de formule naar rechts om zoveel mogelijk kolommen te vullen en zo alle mogelijke overeenkomsten te tonen, daarna naar beneden voor iedere rij. Extra kolommen blijven leeg als er minder matches zijn. Dit opmaaktype is vooral geschikt als je de overeenkomende waarden in aparte kolommen wilt tonen:
Krijg je fouten? Controleer dan het bereik, zorg dat het D2:D7 gebied klopt en dat je het juiste scheidingsteken (komma of puntkomma) gebruikt voor je regio.
Markeer de overeenkomende waarden als een cel een van meerdere waarden uit een lijst bevat met een handige functie
Wil je visueel de trefwoorden of zinnen markeren die overeenkomen met een waarde uit je lijst binnen elke cel, dan helpt dit bij het snel onderscheiden van belangrijke data voor beoordeling of actie. De functie Markeer trefwoorden in Kutools voor Excel is hier speciaal voor ontwikkeld, zodat je snel elke instantie van je opgegeven woorden in het gegevensbereik kunt markeren zonder formules of VBA-code te hoeven schrijven. In grote bladen of complexe datasets is deze functie bijzonder handig wanneer handmatige controle onpraktisch is.
Na installatie van Kutools voor Excel, volg je deze stappen:
1. Ga naar Kutools > Tekst > Markeer trefwoorden om het dialoogvenster te openen, zoals getoond:
2. Voer in het dialoogvenster Markeer trefwoorden het volgende uit:
- Selecteer je doelgegevensbereik via het Bereik-veld.
- Kies, of voer handmatig, de trefwoorden in het Trefwoord-veld in (gescheiden door komma's).
- Geef een markeringskleur op via de optie Lettertypekleur van trefwoord.
3. Klik op OK. Alle overeenkomende woorden binnen het geselecteerde bereik worden gemarkeerd met jouw gekozen lettertypekleur:
- Deze functie past direct de weergave-opmaak van de gemarkeerde trefwoorden aan, waardoor het controleren of delen van de resultaten eenvoudiger en intuïtiever wordt voor teams die mogelijk geen formules begrijpen.
- De tool is extra effectief als de trefwoordenlijst of het tekstbereik erg groot is, of als je meerdere criteria tegelijk wilt markeren.
- Controleer altijd je gekozen bereik en trefwoorden zorgvuldig voordat je bevestigt om onbedoelde markeringen te voorkomen.
Controleer of een cel een van meerdere waarden bevat via Voorwaardelijke opmaak gebruiken
Excel’s ingebouwde Voorwaardelijke opmaak gebruiken is een andere effectieve aanpak om cellen te markeren die een waarde uit jouw opgegeven lijst bevatten. Deze oplossing is handig om met één oogopslag relevante rijen te herkennen, vooral bij gegevensanalyse, foutcontrole of compliance-taken. In tegenstelling tot de Markeer trefwoorden-functie van Kutools vereist deze methode geen extra invoegtoepassing en is dus beschikbaar in standaard Excel-installaties.
Dit is hoe je Voorwaardelijke opmaak gebruiken toepast met een formule:
- Selecteer de cellen in je gegevensbereik (bijvoorbeeld A2:A20) die je wilt traceren.
- Ga naar het tabblad Start, klik op Voorwaardelijke opmaak gebruiken > Nieuwe regel.
- Kies in het dialoogvenster Nieuwe opmaakregel de optie 'Een formule gebruiken om te bepalen welke cellen worden opgemaakt'.
- Voer deze formule in, waarbij D2:D7 je waarden bevat en A2 de eerste datacel is:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Klik op Opmaak, stel vervolgens je gewenste opmaak in (zoals vulkleur) en klik op OK.
Alle cellen die een van de items uit de D2:D7-lijst bevatten worden nu automatisch gemarkeerd.
- Deze methode is dynamisch: als je het bereik D2:D7 bijwerkt, past de opmaak zich automatisch aan.
- Voorwaardelijke opmaak gebruiken is alleen visueel: het markeert cellen, maar levert geen resultaat op in een aparte kolom of voor verdere berekeningen.
- Formule-gebaseerde voorwaardelijke opmaak is krachtig, maar bij hele grote datasets kan de snelheid afnemen door herhaaldelijke berekeningen.
- Let op: SEARCH is niet hoofdlettergevoelig. Wil je het hoofdlettergevoelig maken, dan zijn geavanceerdere technieken of hulpkolommen nodig.
Meer gerelateerde artikelen:
- Vergelijk twee of meer tekstreeksen in Excel
- Wil je twee of meer tekstreeksen vergelijken in een werkblad, waarbij je kunt kiezen voor hoofdlettergevoelig of niet-hoofdlettergevoelig, zoals getoond in de onderstaande screenshot, dan behandelt dit artikel enkele handige formules om deze taak in Excel uit te voeren.
- Als cel tekst bevat, toon dan in Excel
- Als je een lijst met tekstreeksen in kolom A hebt, en een rij met trefwoorden, controleer dan of de trefwoorden voorkomen in de tekstreeks. Verschijnen de trefwoorden, dan worden ze getoond; zo niet, blijft de cel leeg, zoals in de screenshot hieronder.
- Aantal trefwoorden tellen in een cel op basis van een lijst
- Wil je tellen hoeveel trefwoorden in een cel voorkomen op basis van een cellenlijst, dan kun je een combinatie van de functies SUMPRODUCT, ISNUMBER en SEARCH gebruiken om dit probleem in Excel op te lossen.
- Meerdere waarden zoeken en vervangen in Excel
- Normaal gesproken kun je met Zoeken en Vervangen een specifieke tekst vinden en vervangen door een andere. Soms wil je echter meerdere waarden tegelijk zoeken en vervangen. Bijvoorbeeld alle tekst “Excel” door “Excel2019”, “Outlook” door “Outlook2019”, enzovoorts zoals te zien is in de onderstaande screenshot. In dit artikel introduceer ik een formule om deze taak in Excel op te lossen.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen