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

Om te controleren of een cel bepaalde teksten uit bereik A bevat, maar géén teksten uit bereik B, kunt u een krachtige matrixformule in Excel gebruiken die de functies AANTAL, ZOEKEN en EN slim combineert. In deze handleiding leggen we helder uit hoe u deze formule effectief toepast.

Algemene formule:

Argumenten

Retourneerwaarde:

Deze formule retourneert een logische waarde: TRUE als de cel ten minste één van de waarden uit het argument Opnemen bevat én geen enkele van de waarden uit het argument Uitsluiten; anders FALSE.

Hoe deze formule werkt

Stel dat u wilt controleren of cel B3 één van de waarden uit het bereik E3:E5 bevat, maar geen van de waarden uit het bereik F3:F4. Gebruik dan de volgende formule:

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

Uitleg

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

ZOEKEN-functie: de ZOEKEN-functie geeft de positie van het eerste teken van een tekstreeks binnen een andere tekst. Als de functie overeenkomende tekst vindt, retourneert deze de relatieve positie; anders krijg je de fout #WAARDE!. In dit voorbeeld zoekt de formule SEARCH($E$3:$E$5,B3) elke waarde in het bereik E3:E5 op in cel B3 en retourneert de locatie van elke gevonden tekstreeks in die cel. Het resultaat is een matrix zoals deze: {1;7;#VALUE!}.

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

COUNT(SEARCH($E$3:$E$5,B3))>0, vergelijkt het resultaat van de AANTAL-functie met nul. Zolang dat resultaat groter is dan 0, retourneert de formule TRUE; anders FALSE. In dit geval is de uitkomst TRUE.

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

ZOEKEN-functie: de ZOEKEN-functie retourneert de positie van het eerste teken van een tekstreeks binnen een andere tekst. Als de functie overeenkomende tekst vindt, geeft ze de relatieve positie terug; anders retourneert ze de fout #WAARDE!. In dit geval zoekt de formule SEARCH($F$3:$F$4,B3) elke waarde uit het bereik F3:F4 in cel B3 en retourneert de locatie van elke tekstreeks in die cel. Dit levert een matrixresultaat op zoals dit:{#VALUE!; #VALUE!;#VALUE!}.

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

COUNT(SEARCH($F$3:$F$4,B3))>0, vergelijkt het resultaat van de AANTAL-functie met nul. Zolang het resultaat van de AANTAL-functie 0 is, retourneert de formule TRUE; anders FALSE. In dit geval retourneert deze TRUE.

Deel 3:ENfunctie

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

=AND(TRUE,TRUE)

=TRUE

Voorbeeldbestand

Klik om het voorbeeldbestand te downloaden

Gerelateerde formules

• Controleer of een cel een specifieke tekst bevat
  Gebruik een matrixformule met de functies AANTAL, ZOEKEN en EN in Excel om te controleren of een cel bepaalde teksten uit bereik A bevat, maar geen teksten uit bereik B.
• Controleer of een cel één van meerdere waarden bevat, maar andere waarden uitsluit
  In deze handleiding vind je een formule om deze taak snel uit te voeren: controleren of een cel één van de opgegeven waarden bevat, maar tegelijkertijd andere waarden uitsluit in Excel. Daarnaast leggen we de argumenten van de formule duidelijk uit.
• Controleer of een cel één van de items bevat
  Stel dat u in Excel een lijst met waarden hebt in kolom E en wilt controleren of de cellen in kolom B één van de waarden uit kolom E bevatten. In dat geval retourneert u TRUE of FALSE.
• Controleer of een cel een getal bevat
  Soms wil je controleren of een cel numerieke tekens bevat. Deze handleiding biedt een formule die TRUE retourneert als de cel een getal bevat en FALSE als dat niet het geval is.