Google-sheets – Google Sheets queries: using WHERE IN

google sheetsgoogle-sheets-query

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.

So, I tried:

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

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?

Best Answer

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)'")

Related Solutions

Google Sheets – Use COUNTIFS in Complex Formulas

You can do a count for each month by transforming an array formula to only count based on that month:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"01")

change the "01" for each month so february would look like this:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"02")

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.

Example:

Adaptation of the code taken from the answer by Vidar S. Ramdal

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}