Excel-formule: Controleer of een cel sommige teksten bevat maar niet andere

Om te controleren of een cel sommige teksten in bereik A bevat, maar niet de teksten in bereik B, kunt u een matrixformule gebruiken die de functies COUNT, SEARCH en AND combineert in Excel. Deze handleiding legt uit hoe u deze matrixformule gebruikt.

Algemene formule:

Argumenten

Retourwaarde:

Deze formule retourneert een logische waarde. Als de cel ten minste één van de argumenten Inclusief bevat, maar geen van de argumenten Exclusief bevat, zal het WAAR retourneren, anders ONWAAR.

Hoe deze formule werkt

Stel dat u wilt controleren of cel B3 één van de waarden in het bereik E3:E5 bevat, maar tegelijkertijd geen van de waarden in het bereik F3:F4 bevat, gebruik dan onderstaande formule.

Druk op Shift + Ctrl + Enter toets om de cel te controleren.

Uitleg

Deel 1: ( COUNT(SEARCH($E$3:$E$5;B3))>0 controleert of cel B3 één van de waarden in het bereik E3:E5 bevat.

SEARCH-functie: de SEARCH-functie retourneert de positie van het eerste teken van de tekststring binnen een andere. Als de SEARCH-functie de overeenkomende tekst vindt, retourneert het de relatieve positie, zo niet, dan retourneert het een #VALUE! fout. Hier zoekt de formule SEARCH($E$3:$E$5;B3) elke waarde in het bereik E3:E5 in cel B3 en retourneert de locatie van elke tekststring in cel B3. Het retourneert een array-resultaat zoals dit: {1;7;#VALUE!}.

COUNT-functie: de COUNT-functie telt het aantal items in een bereik of een array. COUNT(SEARCH($E$3:$E$5;B3)) retourneert 2 omdat het aantal items in de array {1;7;#VALUE!} twee is.

COUNT(SEARCH($E$3:$E$5;B3))>0, vergelijk het resultaat van de COUNT-functie met nul. Zolang het resultaat van de COUNT-functie groter is dan 0, zal het resultaat WAAR zijn, anders ONWAAR. Hier retourneert het WAAR.

Deel 2: COUNT(SEARCH($F$3:$F$4;B3))=0 controleert of cel B3 geen van de waarden in het bereik F3:F4 bevat.

SEARCH -functie: de SEARCH-functie retourneert de positie van het eerste teken van de tekststring binnen een andere. Als de SEARCH-functie de overeenkomende tekst vindt, retourneert het de relatieve positie; zo niet, dan retourneert het een #VALUE! fout. Hier zoekt de formule SEARCH($F$3:$F$4;B3) elke waarde van het bereik F3:F4 in cel B3 en retourneert de locatie van elke tekststring in cel B3. Het retourneert een array-resultaat zoals dit: {#VALUE!; #VALUE!;#VALUE!}.

COUNT-functie: de COUNT-functie telt het aantal items in een bereik of een array. COUNT(SEARCH($F$3:$F$4;B3)) retourneert 0 omdat er geen items in de array {#VALUE!; #VALUE!;#VALUE!} zijn.

COUNT(SEARCH($F$3:$F$4;B3))>0, vergelijk het resultaat van de COUNT-functie met nul. Zolang het resultaat van de COUNT-functie gelijk is aan 0, zal het WAAR retourneren, anders ONWAAR. Hier retourneert het WAAR.

Deel 3: AND-functie

=AND(COUNT(SEARCH($E$3:$E$5;B3))>0;COUNT(SEARCH($F$3:$F$4;B3))=0)

=AND(WAAR;WAAR)

=WAAR

Voorbeeldbestand

Klik om voorbeeldbestand te downloaden

Gerelateerde Formules

• Controleer of een cel een specifieke tekst bevat
  Om te controleren of een cel sommige teksten in bereik A bevat, maar niet de teksten in bereik B, kunt u een matrixformule gebruiken die de functies COUNT, SEARCH en AND combineert in Excel.
• Controleer of een cel één van verschillende waarden bevat maar andere waarden uitsluit
  Deze handleiding biedt een formule om snel de taak uit te voeren om te controleren of een cel één van de dingen bevat, maar andere waarden uitsluit in Excel en legt de argumenten van de formule uit.
• Controleer of cel één van de dingen bevat
  Stel dat in Excel er een lijst met waarden in kolom E staat, en u wilt controleren of de cellen in kolom B alle waarden in kolom E bevatten en WAAR of ONWAAR retourneren.
• Controleer of cel een getal bevat
  Soms wilt u mogelijk controleren of een cel numerieke tekens bevat. Deze handleiding biedt een formule die WAAR retourneert als de cel een getal bevat, en ONWAAR als de cel geen getal bevat.