2

I have a search Query where I'm looking for values in my table. And I want to dynamically search for a value, for example, "AMAZON".

="select B, C, L, E, O, P, Q WHERE B >= date '"&TEXT(A2; "yyy-mm-dd")&"' and B <= date '"&TEXT(B2; "yyy-mm-dd")&"' and L matches '.*"&C2&".*' and E LIKE '%"&D2&"%'"

"C2" is my empty cell, for searching for a string. I, for example, input "amazon" into "C2"

My problem is that is doesn't return anything as in my table it's written "AMAZON" or probably "Amazon" so it won't find anything as I have a direct link to "C2"I couldn't figure out that way of phrasing the regular expression to ignore the case sensitive. It would return anything as soon as I write "AMAZON" in "C2".

Goal:

I want to make it non case sensitive. Cause at the moment my formula won't print anything as soon as I don't write it in initial letters.

Rubén
  • 34,714
  • 9
  • 70
  • 166
BenjaminK
  • 653
  • 1
  • 9
  • 26

1 Answers1

2

Please try lower in all cells you reference as well as all depending, returning columns.
So your formula would be:

="select B, C, L, E, O, P, Q WHERE B >= date '"&TEXT(A2; "yyy-mm-dd")&"' and B <= date '"&TEXT(B2; "yyy-mm-dd")&"' and lower(L) matches '.*"&lower(C2)&".*' and lower(E) LIKE '%"&lower(D2)&"%'"
marikamitsos
  • 10,264
  • 20
  • 26
  • Thank you @marikamitsos the solution lied not only in the regular expression of the matches. Instead, I also had to change the input table as well. This made it work. – BenjaminK Apr 28 '20 at 14:57
  • 1
    Yes. It needs **all** cells, columns _"involved"_ to be wrapped by `LOWER`. Glad I could help. – marikamitsos Apr 28 '20 at 15:01