Google sheets Query, How to use query to select a value in rows based on header dates

Question

I have sheet with two tabs and named a ranged where I'm trying to match and select a value in the budget tab based values from the month tab using query. The challenge I'm running into is how to use the dates in the header row of the budget tab in the Where of the query.

Find the value from named range Budget WHERE Category in Month tab matches Category in Budget AND date from Month tab matches date from budget tab, else value in Budget tab column lookup.

I think the sample sheets does a better job of showing this. Thanks in advance for any help.

https://docs.google.com/spreadsheets/d/1heN3I1tWiqBJ0LdHRK-26Koafk_8EudqfMy0cFWxFjI/edit?usp=sharing

I was thinking as opposed to query should index and match be a better choice perhaps? — JAK
– JAK, Commented Oct 20, 2021 at 19:09

GoranK · Accepted Answer · 2021-10-24 20:37:08Z

1

I think in this case and the sheet layout it is better to use VLOOKUPs.

Just use this arrayformula across all months =ARRAYFORMULA(if($H$16:$H="","",if(VLOOKUP($H$16:$H,budget,ifna(match(I$15,Budget!$E$1:$K$1,0),COLUMNS(budget)),false)="", VLOOKUP($H16:$H,budget,columns(budget),false), VLOOKUP($H$16:$H,budget,ifna(match(I$15,Budget!$E$1:$K$1,0),COLUMNS(budget)),false))))

Please note that I expanded the budget named range to cover the entire range, including Category and Lookup columns.

answered Oct 24, 2021 at 20:37

GoranK

1,6782 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JAK Over a year ago

thank you I'll give this a go but might want to come back to the app script anyway.

Collectives™ on Stack Overflow

Google sheets Query, How to use query to select a value in rows based on header dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related