Generieke formule
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Samenvatting
Als u een cel wilt testen op een van de vele strings, terwijl u andere uitsluit, kunt u een formule gebruiken die is gebaseerd op de functies SEARCH, ISNUMBER en SUMPRODUCT. In het getoonde voorbeeld is de formule in C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
waar "include" het benoemde bereik E5: E9 is, en "exclude" het benoemde bereik G5: G6.
Uitleg
In de kern gebruikt deze formule de functie ZOEKEN om naar meerdere tekenreeksen in een cel te zoeken. Binnen het linker SOMPRODUCT zoekt SEARCH naar alle strings in het genoemde bereik "include".
In het rechter SOMPRODUCT zoekt SEARCH naar alle strings in het benoemde bereik "exclude".
In beide delen van de formule retourneert SEARCH numerieke posities wanneer tekenreeksen worden gevonden, en fouten wanneer dat niet het geval is. De functie ISGETAL converteert de getallen naar WAAR en fouten naar ONWAAR, en het dubbele negatieve zet de waarden WAAR ONWAAR om in 1 en 0.
Het resultaat ziet er op dit punt als volgt uit:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Vervolgens:
=(1>0)*(0=0) =TRUE*TRUE =1
Opmerking: deze formule retourneert 1 of nul, die worden afgehandeld als WAAR en ONWAAR in formules, voorwaardelijke opmaak of gegevensvalidatie.
