Google Sheet Select items by column that contains few parameters separated by comma

Question:

Hi everyone, I am trying to create some sort of inventory database using google sheets. I have one sheet 'Inventory' that lists all the items in my database. In another sheet 'Search' I want to have an ability to display items that qualify size dispersion. The problem is that items have multiple sizes listed in one cell separated by comma.

Timestamp	Name	Weight	Price	Tire sizes
07.10.2015 11:04:01	Chain 1	220	100	200, 300
07.10.2015 14:04:44	Chain 2	135	350	100
07.10.2015 14:05:27	Chain 3	453	560	100, 200, 300

I need to come up with formula that would take following input

Size from	Size to
50	120

And would display items that qualify this input dispersion like

Timestamp	Name	Weight	Price	Tire sizes
07.10.2015 14:04:44	Chain 2	135	350	100
07.10.2015 14:05:27	Chain 3	453	560	100, 200, 300

Solution:

Following is the screenshot of Sheet "Inventory"

Now, put the following script in the script editor of your spreadsheet:

function inventory() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName('Inventory');

  var r = s.getRange('A:E');

  var v = r.getValues();

  var lastrow  = getLastPopulatedRow(v);

  var arr = new Array();

  arr.push(v[0]);

  

  for(var i=1;i<lastrow;i++) {

    var e = v[i][4].toString().split(',');

    for(var j=0;j<e.length;j++){

      arr.push( [ v[i][0],v[i][1],v[i][2],v[i][3],e[j].trim()*1 ] );

    }

  }

  return arr;

};

function getLastPopulatedRow(data) {

  for (var i=data.length-1;i>=0;i--)

    for (var j=0;j<data[0].length;j++)

      if (data[i][j]) return i+1;

  return 0;

};

save the above code and then refresh your spreadsheet.

Now, just try the following custom function in a new sheet in your spreadsheet:

=inventory()

The above custom function will give you the following output:

Now, we can use this output in a Query formula, try the following formula in cell A2 of sheet "Search":
=query(inventory();"select * where Col5>="&A2&" and Col5<="&B2;1)

Following is the screenshot of Sheet "Search":