Ask Your Question

How do I check if the contents in one cell exist in another cell?

asked 2019-05-25 23:43:05 +0200

Ed_one gravatar image

I am trying to check the name in one cell for a match in another cell which has a list of names. I want to check if the name in cell A is one the list of names in cell B.

edit retag flag offensive close merge delete


Please explain to the detail in what way you got one "other cell" to contain a list of names. In other words: What's your list syntax?
Calc still not has implemented a standard function to split a list given as a single string.

Lupp gravatar imageLupp ( 2019-05-26 11:35:35 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-05-26 11:33:00 +0200

m.a.riosv gravatar image

Several function can help, maybe COUNTIF() it's the simplest for this case.
If you find troubles to have them working fine, verify it is not an issue with regular expression or wildcards as part of the strings inadvertently, Menu/Tools/Options/LibreOffice calc/Calculate.


edit flag offensive delete link more


If my assumption concerning a list "in cell B" holds, the functions named here won't be useful.
Instead you need to know the syntax (most likely a specific delimiter assured to not occur inside a list element) and to look for a respective portion of the list with the help of FIND() or SEARCH() or REGEX().

Lupp gravatar imageLupp ( 2019-05-26 11:41:14 +0200 )edit

answered 2019-05-26 12:13:46 +0200

Lupp gravatar image

updated 2019-05-26 14:16:15 +0200

Delimiter: || (2 consecutive pipe symbols) e.g.
Check for: myName in cell A2
List: tomsName||yourName||myName||King James IX in cell L2
Formula: =ISNUMBER(FIND("||" & A2 & "||"; "||" & L2 & "||"))
will return TRUE.
Change A2 to My_Name
and the formula will return FALSE.
Change A2 to King James and the result will still be FALSE.
With A2 = King James IX you will get TRUE again.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-25 23:43:05 +0200

Seen: 1,650 times

Last updated: May 26 '19