Excel-formule: Controleren of een cel één van meerdere waarden bevat, maar andere waarden uitsluit

Stel dat u twee lijsten met waarden hebt. U wilt controleren of cel B3 één van de waarden uit het bereik E3:E5 bevat, maar tegelijkertijd geen van de waarden uit het bereik F3:F4 — zoals te zien is in de afbeelding hieronder. Deze handleiding biedt een formule om deze taak snel uit te voeren in Excel en legt de argumenten van die formule uit.

Algemene formule:

Argumenten

Retourneerwaarde:

De formule retourneert 1 of 0. Wanneer de cel één van de waarden bevat die moeten worden meegenomen en geen van de waarden bevat die moeten worden uitgesloten, retourneert de formule 1; anders retourneert deze 0. In deze formule worden 1 en 0 behandeld als logische waarden TRUE en FALSE.

Hoe deze formule werkt

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

Druk op Enter om het controle-resultaat te verkrijgen.

Uitleg

Deel 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) controleert of de cel waarden uit E3:E5 bevat

ZOEKEN-functie: de ZOEKEN-functie geeft de positie van het eerste teken van een tekstreeks binnen een andere tekst terug. Als de functie een overeenkomst vindt, retourneert ze de relatieve positie; anders krijg je de fout #WAARDE!. Bijvoorbeeld: de formule SEARCH($E$3:$E$5,B3) zoekt elke waarde uit het bereik E3:E5 in cel B3 en geeft de locatie van elke gevonden tekstreeks in cel B3 terug. Dit levert het volgende matrixresultaat op: {1;7;12}.

ISGETAL-functie: de ISGETAL-functie retourneert WAAR wanneer een cel een getal bevat. Zo retourneert ISNUMBER(SEARCH($E$3:$E$5,B3)) een matrixresultaat als {WAAR;WAAR;WAAR}, aangezien de ZOEKEN-functie drie getallen vindt.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) zet de waarde TRUE om naar 1 en FALSE naar 0, waardoor deze formule het matrixresultaat verandert in {1;1;1}.

PRODUCTSOM-functie: vermenigvuldigt bereiken of matrices en telt de resultaten op om de som van de producten te retourneren. De SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) retourneert 1+1+1=3.

Tot slot wordt de formule links SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) vergeleken met 0. Zolang het resultaat van deze formule groter is dan 0, is de uitkomst TRUE; anders is deze FALSE. In dit geval retourneert de formule TRUE.

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

De formule ZOEKEN($F$3:$F$4,B3) zoekt elke waarde uit het bereik F3:F4 in cel B3 en retourneert de locatie van elke tekstreeks in die cel. Dit levert het volgende matrixresultaat op: {#WAARDE!;#WAARDE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) retourneert een matrixresultaat als {onwaar;onwaar}, omdat de ZOEKEN-functie geen getal(len) vindt.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) converteert de waarde TRUE naar 1 en FALSE naar 0, waardoor deze formule het matrixresultaat omzet in {0;0}.

PRODUCTSOM-functie: vermenigvuldigt overeenkomstige elementen in opgegeven bereiken of matrices en geeft de som van die producten terug. De SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) retourneert 0+0=0.

Tot slot wordt de formule aan de linkerkant SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) vergeleken met 0. Zolang het resultaat van deze formule gelijk is aan 0, retourneert ze TRUE; anders retourneert ze FALSE. In dit geval is de uitkomst TRUE.

Deel 3: Vermenigvuldig de twee formules

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

In deze formule worden 1 en 0 geïnterpreteerd als de logische waarden TRUE en FALSE.

Voorbeeldbestand

Klik om het voorbeeldbestand te downloaden

Gerelateerde formules

• Controleren of een cel specifieke tekst bevat
  Gebruik een matrixformule in Excel die de functies AANTAL, ZOEKEN en EN combineert om te controleren of een cel bepaalde teksten uit bereik A bevat, maar geen teksten uit bereik B.
• Controleren of een cel één van meerdere waarden bevat
  Deze handleiding biedt een formule om te controleren of een cel één van verschillende waarden bevat in Excel. Daarnaast legt deze uit welke argumenten de formule gebruikt en hoe deze precies werkt.
• Controleren of een cel één van de waarden bevat
  Stel dat u in Excel een lijst met waarden heeft in kolom E. U wilt controleren of de cellen in kolom B één of meer van de waarden uit kolom E bevatten, en TRUE of FALSE retourneren.
• Controleren of een cel een getal bevat
  Wilt u soms weten of een cel een getal bevat? Deze handleiding biedt een formule die TRUE retourneert als de cel een getal bevat, en FALSE als dat niet het geval is.