So I don’t know how to explain my problem very well, so I’m going to make an example, I think this is the best way to understand it, but basically I’m trying to return orders that match words in a list, and those words can contain special characters, example below:
I have a list of references like:
| A |
|---|
| Grass |
| Dirt |
| Water |
| Grass+ |
Now I have a list of orders like:
| B | C |
|---|---|
| 1 | Grass |
| 2 | Grass+ |
| 3 | Potato |
| 4 | Grass+ |
| 5 | Water |
Now what I want to do is query though the orders list, and get all the orders that match the list of references, so what i did is:
=QUERY(
B1:C,
"SELECT Col1, Col2
WHERE Col1 IS NOT NULL AND Col2 MATCHES '"&TEXTJOIN("|";TRUE;A1:A)&"' ORDER BY Col1 ASC",0
)
This returns me this:
| D | E |
|---|---|
| 1 | Grass |
| 5 | Water |
Instead of this:
| D | E |
|---|---|
| 1 | Grass |
| 2 | Grass+ |
| 4 | Grass+ |
| 5 | Water |
Why is the “Grass” with the + in the end being ignored? is there a way to resolve this problem? Am i doing something wrong?
Thanks for helping guys!
Certain characters (* , + , ?, ^ , $) needs to escaped since they have a meaning within regex match. Try changing this
TEXTJOIN("|";TRUE;A1:A)
to
substitute(TEXTJOIN("|";TRUE;A1:A);"+";"+")
Alternative formula
=sort(filter(B:C;xmatch(C:C;A:A)))
1