Ask Your Question

Finding if content of one cell (text) is listed in a collumn/row/array

asked 2018-04-25 12:01:23 +0200


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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-04-25 12:04:51 +0200

edit flag offensive delete link more


Oh... Bloody hell... I apologise for the stupid question... Now I'm embarrassed...


Corbeau gravatar imageCorbeau ( 2018-04-25 12:20:00 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-04-25 14:11:05 +0200 )edit

Could be a bit simple =SUMPRODUCT(EXACT($A$1:$A$100;D5))>0 ?

m.a.riosv gravatar imagem.a.riosv ( 2018-04-25 23:36:06 +0200 )edit

@m.a.riosv: 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.)

Lupp gravatar imageLupp ( 2018-04-26 11:18:00 +0200 )edit

Nice soul @Lupp, It's really great to see a teacher teaching.

m.a.riosv gravatar imagem.a.riosv ( 2018-04-26 16:40:36 +0200 )edit

answered 2018-04-25 14:30:13 +0200

Lupp gravatar image

See this demo containing examples for the snares I mentione in my comment on the answer by @Mike Kaganski .

You may also use
{=(COUNTIF(EXACT($A$1:$A$100;D5);TRUE())>0)} instead of
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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-25 12:01:23 +0200

Seen: 24 times

Last updated: Apr 25 '18