I have two sets of data, each with entries that complement each other (i.e. entry from one dataset is a translation of the entry in the second dataset). The entries both start with the source word, so it is possible to alphabetically sort and pair them together.
However, some entries do not have their complementary entry in the other dataset, which means that even if I alphabetically sort both of them, they will not be completely mirroring each other.
The solution that I post here might require finding equivalent functions in Libre Calc (the functions below are from Excel), but it does work.
Create 2 helper columns containing the shared term split out from each of the two columns. Here is an example that uses data in column A and the separator being ^ instead of a bullet
=LEFT(A1,FIND("^",A1)-1)
Find the matches uses index(match()). This assumes your data columns are A and C and your helper columns are B and D.
=INDEX(C:C,MATCH(B1,$D$1:$D$5,0))
(Optional) Non-matches will show as #N/A. If you want you can handle that by surrounding the index(match()) with the following to make the non-matches blank cells
=IFERROR(<formula from 2>, “”)
Possible Error Handling: It looks like your data may contain whitespace. If the index(match()) isn’t returning a bunch of matches then it is likely due to mismatching whitespace. To handle this you can surround the formulas from part 1 in the following to remove both leading and trailing whitespace
=TRIM(<formula from 1>)
(Solution provided by FleetAdmiralFader link to OP)