Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

Google spreadsheets query range get item to the right

I am trying to get data from a "flexible" sheet.

The idea is to add in a row a string, and next to it add a formula that from another sheet, it looks up the string and returns the cell just at the right.

Example:

Data Sheet: DataCollection

|     |    A    |   B  |    C   |    D   |   
|   1 |  Pepper |   2  |  Sugar |    5   |
|   2 |  Carbon |   3  |  Toy   |   34   |

so if in my other sheet, the "Summary" I add to A1 Sugar I would like to see in A2 a 5.

What I have tried so far

VLOOKUP function 

=VLOOKUP(A1,'DataCollection'!A2:B&'DataCollection'!C2:D,1,false)

didn't work! I keep receiving a parse formula error.

QUERY function

=QUERY(DataCollection, "SELECT B WHERE A = A1")

that does not work either, I need many different Named Ranges and add a different query for each of them.

Here is a test spreadsheet in case it explains better that my wall of text: https://docs.google.com/spreadsheets/d/15L5nPGfZ8OXS5Rhl3PdIVhtF7D3QzerkARskflDiJL4/edit?usp=sharing

Solution

  • you almost had it. try:

    =VLOOKUP(A1, {'DataCollection'!A2:B; 'DataCollection'!C2:D}, 2, 0)

    for an array use:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A1:A, {'DataCollection'!A2:B; 'DataCollection'!C2:D}, 2, 0)))

    0