4

My problem

I have a table of ids and terms in Google Sheets. I would like to concatenate a few terms by their ids.

For example, (2,3) should yield Talla de zapato de mujer, Women’s shoe size. I want to take id list from a separate cell.

enter image description here

So, I tried:

=QUERY(A:B, "SELECT A,B WHERE A=2 OR A=3")

enter image description here

The query works fine, but it's hard to manually extract the 2's and 3's this way.

So I tried:

=QUERY(A:B, "SELECT A,B WHERE A IN (2,3)")

Which does not work (VALUE error).

My question

How can I select ids from a table using a GSheets query using a few ids written in a different cell?

3 Answers 3

8

The =QUERY() language WHERE clause support regex; so you can "simulate" IN functionality using a regex group - ie:

=QUERY(A:B, "SELECT A,B WHERE A MATCHES '(2|3)'")
3

To keep away the ID's use

=QUERY(A:B, "SELECT B WHERE A=2 OR A=3")

Then to get Talla de zapato de mujer, Women’s shoe size you could use

=JOIN(", ", QUERY(A:B, "SELECT B WHERE A=2 OR A=3"))
0

Based on David Laing's answer, I managed to get the query with a separate range of IDs working with a join like this:

QUERY(Transactions!$B5:$E, join("", "SELECT C WHERE C > 0 AND NOT E MATCHES '(", join("|", B28:B44), ")'"))

It joins the IDs using a pipe character join("|", B28:B44), and then joins the whole query string together with an empty string, join("", "SELECT C WHERE ... '(", join("|", B28:B44), ")'")

It might be hard to read because of all the quotes, but it should not require hard coded IDs within the query itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.