Hello,
please see the following sample file: FindandShow.ods
Prerequites for this to work:
- Don’t pre-format cells as text (Format Code:
@
) if you intend to put formulas into the cells. You will see the formulas as text, but no evaluation will occur.
- Set
Tools -> Options -> LibreOffice Calc -> Calculate -> Section: Formula wildcards -> Option: [x] Enable regular expressions in formulas
- There must be a “To_Find” value, if you put a formula into adjacent cell in column
C
. If you enter the formula in column C
without a search criterion, you’ll get the first entry of column A
(Where). You’d need to make a more complex formula to deal with this.
Formula
The formula in C2
is: =IF(ISNA(MATCH(".*"& B2 &".*";A$2:A$1000;0));"not found";OFFSET($A$1;MATCH(".*"& B2 &".*";A$2:A$1000;0);0;1;1))
Note The formula will find the first match in case of ambiguous search criteria in "To_Find" column.
Tested using LibreOffice:
Version: 7.0.0.3; Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US; Calc: threaded
Hope that helps.