Translating each result row of a Google Sheets QUERY operation via VLOOKUP

Question

I have the multi-column results of a QUERY in Google Sheets, where I want to translate the strings in one column based on a lookup table in another named range. I can accomplish this indirectly with an additional VLOOKUP call, but I'd like to do it all in one go, with no intermediary steps. See this sheet for an example of my format.

The initial query I make looks something like this:

=QUERY(votes, "SELECT B, SUM(C) GROUP BY B LABEL B 'Option', SUM(C) 'Votes'")

I can then translate each row with something like this in a new column...

=VLOOKUP(A2, options, 2, 0)

... and then just pick out the columns I need:

={C2:C4,B2:B4}

How can I combine all this? I think I need to do this with an ARRAYFORMULA, but this doesn't seem to be it:

=ARRAYFORMULA(VLOOKUP(options, QUERY(votes, "SELECT B, SUM(C) GROUP BY B LABEL B 'Option', SUM(C) 'Votes'"), 2, FALSE))

score 1 · Accepted Answer · answered Oct 03 '19 at 16:00

1

=ARRAYFORMULA({VLOOKUP(INDEX(
 QUERY(votes, "select B,sum(C) group by B LABEL sum(C)''"),,1), options, 2, 0), 
 QUERY(votes, "select   sum(C) group by B LABEL sum(C)''")})

answered Oct 03 '19 at 16:00

player0

124,011
12
67
124

1

My actual query out of the source data needed some additional filtering, so this one worked for me. Thank you so much! – Joost Schuur Oct 04 '19 at 11:54

score 1 · Answer 2 · answered Oct 03 '19 at 16:05

1

=ARRAYFORMULA(QUERY({IFERROR(VLOOKUP(Votes!B2:B, options, 2, 0)), Votes!C2:C}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''", 0))

answered Oct 03 '19 at 16:05

player0

124,011
12
67
124

Translating each result row of a Google Sheets QUERY operation via VLOOKUP

2 Answers2