CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
RamonBoza
RamonBoza

Reputation: 9038

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

Upvotes: 1

Views: 39

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 2

Related Questions