0

In an Excel spreadsheet I have a column (column A) with e.g. the following elements: Audi, VW, BMW Group, Toyota and I can do a search of an element like this:

match("*" & b1 & "*", a1:a5, 0)

which will either return the relative number of where the element is found or N/A. So, for example, if I put Audi in the cell B1, the formula will return '1', and it I put 'BMW' in the cell B1, the formula returns the value '3' (since the formula uses wildcards).

However, in a LibreOffice spreadsheet this does not work. When putting the term 'BMW' into cell B1, the formula here returns 'N/A'.

How to change the formula to have it work the same as the Excel spreadsheet?

Community
  • 1
  • 1
Alex
  • 41,580
  • 88
  • 260
  • 469

2 Answers2

2

Wildcard charter in query string may work.

Try following function and put 'BMW' to the cell B1.

match(b1 & "*", a1:a5, 0)

'BMW*' matches 'BMW', 'BMW group', 'BMW car' and other cell which contents begin by 'BM'.

If you want to match any string which contains 'BMW', use wildcard character at both end of value.

match("*" & b1 & "*", a1:a5, 0)
Fumu 7
  • 1,091
  • 1
  • 7
  • 8
0

In my LibreOffice (OOO - openoffice), in Calc, THIS DOES NOT work. I found that wildcard "" is working only with this expression "."

For Example: =MATCH(G1&".*";A1:$A$165;0)

(note: depending on your country, computer, use "," instead of ";" for expression seperation. )

Estatistics
  • 874
  • 9
  • 24