CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
David Salomon
David Salomon

Reputation: 849

VLOOKUP within a query

I'm trying to convert a month name to a yyyy-mm format within a query.

I'm not sure how to do it with a single formula, but I did a table with all the months, because the source data is in yyyy-mm and the dashboard I'm building has the name of the month in plain English.

enter image description here

So I do a VLOOKUP, the user selects the name of the month in a dropdown list and I want to display the data related to that month as per the query.

=QUERY(data!$A$1:$R,"SELECT COUNT(H) WHERE C = '"&VLOOKUP($G$3,backend!$A$2:$B$13,2,0)&"'")

It returns nothing enter image description here

but if I highlight the formula, I get the expected value, but it's not rendering as I expect enter image description here

Upvotes: 0

Views: 883

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

If your data in C column are text then you can use below formula (without additional lookup table if it is for current year).

=QUERY(A1:H6,"select count(H) where C='" & 
TEXT(DATEVALUE("01-"&LEFT(G3,3)&"-"&YEAR(TODAY())),"YYYY-MM") & "'")

If your date in C column are true date then you can try below formula-

=QUERY(A1:H6,"select count(H) where C>= date '" & 
TEXT(DATEVALUE("01-"&LEFT(G3,3)&"-"&YEAR(TODAY())),"YYYY-MM-dd") & "' and C <= date '" & 
TEXT(EOMONTH(DATEVALUE("01-"&LEFT(G3,3)&"-"&YEAR(TODAY())),0),"YYYY-MM-DD") & "'")

Upvotes: 1

Related Questions