Reputation: 97
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
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.
Upvotes: 1