Google-sheets – Select a value from range

There are two things that you need to take into consideration.

Consideration

1. The IMPORTRANGE function requires two parameters (strings):
   • spreadsheet key
   • data range
     The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure.
2. There are two distinct ways to use a parameter:
   • numbers
   • strings

Data

Number

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col3=" & C2, 0)                   // query
)

String

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col2=" & "'" & B2 & "'"           // query
)

Example

I've created an example file for you:

• Test data for QUERY
• Result for QUERY

A custom function will always receive either a rectangular array or a scalar value (number, string, boolean, or datetime) as each of its arguments. So the conversion from rectangular to jagged array has to happen inside the function. For example, by calling jagged(arr) on the input, where jagged is the following function:

function jagged(arr) {
  var rowLength = arr.map(function(row) {
    return row.reduce(function (a, b, i) {
      return b === '' ? a : i+1;
    }, 0);
  });
  return arr.map(function(row, i) {
    return row.slice(0, rowLength[i]);
  });
}

This function first computes the actual length of each row and then truncates the rows accordingly.

I emphasize that the call to jagged needs to happen from within the custom function that will use it, like so:

function myFunction (arr) {
  jarr = jagged(arr); 
  // proceed to work with the jagged array
}  

Putting =myFunction(jagged(A1:F3)) in a spreadsheet cell won't do it: the Sheets will automatically inflate the return values of jagged to a rectangular array before passing them to myFunction.