Google-sheets – Display selected row for match in any columns from a dropdown list in Google Sheets

google sheets

I'd like to create a lookup from a dropdown with all unique sheet data that finds a match within any column and returns the selected row items.

Let's say that each row represents a single device with multiple components described in each column with a unique ID for all. How could I search for any ID and get all column IDs associated with that row selection?

I can do this for a specific single column or row with QUERY, VLOOKUP or INDEX, MATCH but perhaps there is a way to make this work for all without a single parent key.

Best Answer

You want to return a row based on a match in any cell from a dropdown value.

A query will generate the data that you need. Its design is an extension of:

WebApps question: Query syntax using cell reference and
the design of the where clause by joining multiple conditions using ... logical operators.

=query(A2:D4,"select A, B, C, D where (A= '"&$F$12&"' or B= '"&$F$12&"' or C= '"&$F$12&"' or D= '"&$F$12&"') ",1)

In this answer:

Cell F12 contains the dropdown
where clause consists of four logical conditions (one for each column) each equal to the dropdown value in F12
or applies for each condition

Screenshot

Code

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;
}

Explained

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.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google-sheets – Using MEDIAN with INDEX/MATCH pair to pick the row where a given number falls in between two values

I would enter

=array_constrain(filter(C$2:C$5, (A$2:A$5 <= A8) * (B$2:B$5 >= A8)), 1, 1)

in cell B8, and copy it down the results array. Explanation:

filter keeps only the cells from C where A8 falls between A and B.
array_constrain makes sure at most one result is returned (this may be unnecessary if you are sure the ranges in the lookup array do not overlap).

Best Answer

Related Solutions

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Google-sheets – Using MEDIAN with INDEX/MATCH pair to pick the row where a given number falls in between two values

Related Topic