Hi,
I have a table like this:
ID CATEGORY CONDITIONS
1 workstation laptop,desktop
2 printers NetworkPrinter,LocalPrinter
...
I’m new with Сalc. I need a formula to search for a value in CONDITIONS (ie Notebook) and get the relative CATEGORY (Workstation).
This is not difficult. The MATCH() function will find the required row in the CONDITIONS column, and the INDEX() function will retrieve the value from the CATEGORY column by the row number. So formula will be
=INDEX(<CATEGORY column>;MATCH(".*"&<Find word>&".*";<CONDITIONS column>;0))
for example
=INDEX($B$2:$B$300;MATCH(".*"&G2&".*";$C$2:$C$300;0))
The main trick is to add a “dot-asterisk” before and after the search term. This is a regular expression that stands for “any multiple characters”.
In addition to what @JohnSUN already told, I want to hint that there may be some informations about the context of the task not yet delivered by the questioner, There are thinkable complications.
To illustrate the hint, I attach a slightly extended example:
ask272258resolveRevertManyToMany_1.ods
The contained (partial) many-to-many solution requires LibreOffice V6.2.y or higher.