Google Sheets – Lookup Value to Retrieve Entire Column

google sheetsgoogle-sheets-query

Here is my problem:

I have a table starting in A1 with D

D C B A
1 2 3 4
5 6 7 8

I want a function that returns me the column of data of whatever letter give it.

ie input:=function("C")

output:

2
6

input: =function("A")

output:

4
8

I can't just query because the column is changing.

Hlookup? but how do you get the whole column as a return?

Best Answer

If I'm understanding correctly:

=FILTER(A2:D3,A1:D1=A5)

Code

function onSearch1(searchString) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON164");
  var values = sh.getDataRange().getValues();

  for(var i=0, iLen=values.length; i<iLen; i++) {
    if(values[i][0] == searchString) {
      return values[i][1];
    }
  }     
}

Explained

The value var will retrieve all values at once. The single result var will capture the first index found in the range. After that, the result of the adjacent column will be shown, analogue to values[i][0].

Usage

=onSearch1("test")

Note

The findIndex function is none-exsisting in Google Apps Script. If you want to seperate them, then use this code:

function onSearch2(searchString) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON164");
  var values = sh.getDataRange().getValues();
  var i = findIndex(values, searchString);

  return values[i][1]; 
}

function findIndex(values, searchString) {
  for(var i=0, iLen=values.length; i<iLen; i++) {
    if(values[i][0] == searchString) {
      return i;
    }
  }
}

Example

I've created an example file for you: findIndex

Google Sheets Query – Sum Single Column Based on Criteria

I think, you need the combination of formulas. The answer is:

={QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1"),{"filtered sum";ArrayFormula(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")),QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1") ,2,0),0))}}

Explanation

It's not hard if you'll take it by parts:

={basic query, {"header"; vlookup(a, help query, 2, 0) }}

Basic query

QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1")

It's simple, I've used Col1, Col2... notation to make it work with any range.

Vlookup

IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")), help query ,2,0),0))

We count sums with criteria (c = 'yes') in the help query.

UNIQUE(FILTER(A2:A,A2:A<>"")) part of the formula gives you a list from column 'a'.

Help query

QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1")

Here you may enter any conditions what you want. In this case it's Col3 ='yes'