Menu
Write a formula to get Department Budget for a Month [Homework]
Excel Challenges - 40 comments
Time for another homework. You got a spreadsheet of department budgets and need to write a formula to get budget for a given department, month combination.
Homework Specifics:
Assume your data is in the table named budgets, D18 has department and D19 has month values.
You can use any formula in Excel to get the answer. Let’s get creative and have fun.
Post your solutions in the comments section. GO!!!
Need a sample file? Get it from here.
Need a hint? Check out my 2D lookups article.
Want more homework problems? See this page.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « How to embed Excel files, calculators on your website? – Step by step instructions | 10 Advanced IF formula tricks you must know » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
40 Responses to “Write a formula to get Department Budget for a Month [Homework]”
I'd use nested XLOOKUPs, as follows:
=XLOOKUP(D18,budgets[Department],XLOOKUP(D19,budgets[#Headers],budgets))
Almost the same, just accounted for "not found" and forced exact match.
=XLOOKUP(D19,budgets[[#Headers],[Jan]:[Jun]],XLOOKUP(D18,budgets[Department],budgets[[Jan]:[Jun]],"",0))
Verification pointed out that Administration was misspelled so I added Data Validation to D18 and D19.
Old school:
=HLOOKUP(D19;budgets[#All];MATCH(D18;budgets[[#All];[Department]];0);0)
=SUMPRODUCT(budgets[[Jan]:[Jun]]*(budgets[Department]=D18)*(budgets[[#Headers],[Jan]:[Jun]]=D19))
=INDEX(FILTER(budgets[[Jan]:[Jun]],budgets[Department]=D18),MATCH(D19,budgets[[#Headers],[Jan]:[Jun]],0))
=INDEX(FILTER(budgets,INDEX(budgets,,1)=D18),XMATCH(D19,budgets[#Headers],FALSE))
=XLOOKUP(D18,budgets[Department],XLOOKUP(D19,budgets[[#Headers],[Jan]:[Jun]],budgets[[Jan]:[Jun]]))
Way old school:
=VLOOKUP(D18,budgets[#All],MATCH(D19,budgets[#Headers],0),FALSE)
=INDEX(budgets[[Jan]:[Jun]],MATCH(D18,budgets[Department],0),MATCH(D19,budgets[[#Headers],[Jan]:[Jun]],0))
So many functions to choose from.
My first thought was an "old school" solution using OFFSET with MATCH. In trying to learn something new I came up with...
=FILTER(FILTER(budgets,budgets[Department]=D18),budgets[#Headers]=D19)
=vlookup(Dept,Budgets,match(Month,index(Budgets,1),0)-1,0)
Using cell references, rather than range names:
=vlookup(D18,Budgets,match(D19,index(Budgets,1),0)-1,0)
Whoops. Hadn't noticed "budgets" didn't include the header row. Third time lucky?
=VLOOKUP(D18,budgets,MATCH(D19,OFFSET(INDEX(budgets,1,),-1,0),0),0)
Using the old Index and Match function
=INDEX(budgets[#All],XMATCH(D18,budgets[[#All],[Department]],0),XMATCH(D19,budgets[#Headers],0))
And using the new Xlookup
=XLOOKUP(D18,budgets[Department],XLOOKUP(D19,budgets[[#Headers],[Jan]:[Jun]],budgets[[Jan]:[Jun]]))
Using Filter
=FILTER(FILTER(budgets,budgets[Department]=D18),budgets[#Headers]=D19)
{=INDEX(budgets[#Tout];EQUIV(D18;budgets[Department];0);EQUIV(D19;budgets[[#En-têtes];[Jan]:[Jun]];0))}
{=INDEX(budgets[[Jan]:[Jun]];EQUIV(D18;budgets[Department];0);EQUIV(D19;budgets[[#En-têtes];[Jan]:[Jun]];0))}
Just to be different....
=XLOOKUP(D19,budgets[#Headers],FILTER(budgets,budgets[Department]=D18,""),"Not Found")
I also found that the following works:
=INDEX(budgets,XMATCH(D18,budgets[Department]),XMATCH(D19,budgets[#Headers]))
=INDEX(budgets[[Jan]:[Jun]],MATCH(D18,budgets[Department],1),MATCH(D19,budgets[[#Headers],[Jan]:[Jun]],0))
Will get you the answer
Name Monthly Ranges first
=XLOOKUP(D18,budgets[Department],INDIRECT(D19))
=SUMPRODUCT(budgets[[Jan]:[Jun]],MMULT((budgets[Department]=D18)*1,(budgets[[#Headers],[Jan]:[Jun]]=D19)*1))
=VLOOKUP(D18,budgets[#All],MATCH(D19,budgets[#Headers],0),FALSE)
=INDEX(budgets,MATCH(D18,budgets[Department],0),MATCH(D19,budgets[#Headers],0))
This approach should be quite pretty :
in French
=SOMME(RECHERCHEX(D18;C4:C11;D4:I11) RECHERCHEX(D19;D3:I3;D4:I11))
in English, it should be :
=SUM(XLOOKUP(D18;C4:C11;D4:I11) XLOOKUP(D19;D3:I3;D4:I11))
with a space between the 2 XLOOKUP
Sorry not using table address language. I prefer using classic addresses.
well sumproduct is powerfull
=SUMPRODUCT((C4:C11=D18)*(D3:I3=D19)*D4:I11)
=SUMPRODUCT((budgets[[Jan]:[Jun]])*(budgets[[#Headers],[Jan]:[Jun]]=$D$19)*(budgets[Department]=$D$18))
=+?ND?S(budgets[[Jan]:[Jun]];KAÇINCI(D18;budgets[Department];0);KAÇINCI(D19;budgets[[#Üst Bilgiler];[Jan]:[Jun]];0))
=HLOOKUP(D19,budgets[#All],MATCH(D18,budgets[[#All],[Department]],1),)
=INDIRECT(ADDRESS(MATCH(D18,$A$1:$A$9,0),MATCH(D19,$A$1:$F$1,0)))
Assuming table starts in A1
Using Index and Matching Look Up row & column wise
=INDEX(budgets[[Jan]:[Jun]],MATCH(D18,budgets[Department],0),MATCH(D19,budgets[[#Headers],[Jan]:[Jun]],0))
1. define rangenames for the rows and columns
2. then use the fomule =Marketing Apr
=XLOOKUP(D18,budgets[Department],XLOOKUP(D19,budgets[[#Headers],[Jan]:[Jun]],budgets[[Jan]:[Jun]]))
=INDEX(D4:I11,MATCH(D18,C4:C11,0),MATCH(D19,D3:I3,0))
Please tell me if I am correct or wrong?
=INDEX(budgets[#All],MATCH(D18,budgets[[#All],[Department]],0),MATCH(D19,budgets[#Headers],0))
here's mine, I hope this is a viable solution
I used INDEX+MATCH+MATCH:
=INDEX(budgets[[Jan]:[Jun]],MATCH(D18,budgets[Department],0),MATCH(D19,budgets[[#Headers],[Jan]:[Jun]],0))
=VLOOKUP(D18,budgets[#All],5,FALSE)
=VLOOKUP(D18,budgets,5,0)
=INDICE(importes,COINCIDIR(D18,departamentos,0),COINCIDIR(D19,meses,0))
{=SUM(IF((M8=M2:M5)*(N1:P1=N7),N2:P5,0))}
{=SUM(IF((D19=budgets[[#Headers],[Jan]:[Jun]])*(D18=budgets[Department]),budgets[[Jan]:[Jun]],0))}