Generieke formule
=SUMPRODUCT(--(rng=values))>0
Samenvatting
Om te testen of een van de waarden in een celbereik bestaat, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in cel F5:
=SUMPRODUCT(--(rng=B5:D5))>0
waarbij "rng" het benoemde bereik H4: H10 is en de waarden bevat waarnaar moet worden gezocht.
Uitleg
Elk item in rng wordt vergeleken met elk item in waarden en het resultaat is een reeks TRUE of FALSE waarden.
Het dubbele negatief zal de waarden TRUE en FALSE forceren naar respectievelijk 1 en 0. Omdat SOMPRODUCT slechts één array ontvangt, telt het eenvoudig de items in de array op en retourneert het resultaat.
Logischerwijs betekent elk resultaat groter dan nul dat er ten minste één waarde in het bereik bestaat. Dus de laatste stap is om het SUMPRODUCT-resultaat te evalueren om te zien of het groter is dan nul. Elk resultaat groter dan nul geeft WAAR terug, en elk resultaat gelijk aan nul geeft ONWAAR terug.
Met hardgecodeerde waarden
U kunt de zoekwaarden ook hard coderen in de formule met behulp van een zogenaamde "matrixconstante". Als u bijvoorbeeld naar 3 waarden wilt zoeken: rood, cyaan en magenta binnen het bereik H2: H8, kunt u het volgende gebruiken:
=SUMPRODUCT(--(H2:H8=("red","cyan","magenta")))>0
In het bovenstaande voorbeeld ("rood", "cyaan", "magenta") is de matrixconstante, wat een manier is om meerdere waarden in één argument op te geven.
Gedeeltelijke overeenkomsten of substrings
De bovenstaande formule test alleen op gelijkwaardigheid en vindt geen gedeeltelijke overeenkomsten of subtekenreeksen in het bereik. Als u naar subtekenreeksen moet zoeken, kunt u in plaats daarvan deze formule gebruiken.
