CODEWITHSUNDEEP
google-sheets-formula
JAK
JAK

Reputation: 97

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

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

Upvotes: 1

Views: 491

Answers (1)

GoranK
GoranK

Reputation: 1668

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.

enter image description here

Upvotes: 1

Related Questions