formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup
How do I use the script editor to search a string in one cell, and return all results from another sheet?
For Example:
I have my string in cell B1 – which is the item number Data to be searched in a different sheet called "Open PO" Col1 in the data is PO#, Col2 in the data is item number, which should be where to search my string.
How do I create a script to search for all PO's and show them in a list that contains the item number in B1?
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Instead of just using the ranges, you will have to add the sheet name..
e.g:
=ArrayFormula({query(query({'SHEETNAME'!A3:D,value('SHEETNAME'!B3:B)}....ETC
Best Answer
VLOOKUPorQUERY- depending on the extent