Hello.
I have one list, spread out in, say, cells A1:A100. I have a cell D5. I need a logical expression that gives TRUE if content of cell D5 is identical as any of the cells A1:A100. Is it possible to do this? I’ve been looking around for a while and couldn’t find anything.
Oh… Bloody hell… I apologise for the stupid question… Now I’m embarrassed…
headdesks
Use =ISNUMBER(MATCH(TRUE();EXACT($A$1:$A$100;D5);0)) to make sure that not a bad setting or the case-insensitive evaluation of RegEx or a character being “special” under RegEx or Wildcards spoils the comparison for identical contents.
Could be a bit simple =SUMPRODUCT(EXACT($A$1:$A$100;D5))>0 ?
@mariosv: Of course, you are right: I obviously was trapped by the idea I should demonstrate the complcations COUNTIF or any function / operator depending on global settings would come with. I personally don’t like at all the concepts applied with any settings afflicting how formulae are evaluated. My proposal: Force users to be explicit or, at least, offer explicit control per use-case. (Sorry. My soul makes me act as a teacher, sometimes probably in unsuitable cases.)
See this demo containing examples for the snares I mentione in my comment on the answer by @mikekaganski .
You may also use
{=(COUNTIF(EXACT($A$1:$A$100;D5);TRUE())>0)} instead of
=ISNUMBER(MATCH(TRUE();EXACT($A$1:$A$100;D5);0)),
but you need to enter the COUNTIF() variant explicitly for array-evaluation because the force-array disposition of the first parameter of COUNTIF() is still not implemented. For the second parameter of MATCH() it is.