# Write a formula to get Department Budget for a Month [Homework]

Excel Challenges - 31 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:

 Written by Chandoo Tags: downloads, homework, INDEX(), MATCH(), vlookup, vlookup or index+match, xlookup Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 31 Responses to “Write a formula to get Department Budget for a Month [Homework]”

1. TheQ47 says:

I'd use nested XLOOKUPs, as follows:

2. Finnur says:

Old school:

=HLOOKUP(D19;budgets[#All];MATCH(D18;budgets[[#All];[Department]];0);0)

3. Prashant says:

4. Prashant says:

5. Daniel says:

6. Tan Yann Lin says:

7. Jomili says:

Way old school:

8. Frank McCraw says:

9. Craig says:

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...

10. Denys Calvin says:

=vlookup(Dept,Budgets,match(Month,index(Budgets,1),0)-1,0)

• Denys Calvin says:

Using cell references, rather than range names:

=vlookup(D18,Budgets,match(D19,index(Budgets,1),0)-1,0)

• Denys Calvin says:

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)

11. mohammed mustafa says:

Using the old Index and Match function

And using the new Xlookup

Using Filter

12. fethi ben yahia says:

{=INDEX(budgets[#Tout];EQUIV(D18;budgets[Department];0);EQUIV(D19;budgets[[#En-têtes];[Jan]:[Jun]];0))}

13. fethi ben yahia says:

{=INDEX(budgets[[Jan]:[Jun]];EQUIV(D18;budgets[Department];0);EQUIV(D19;budgets[[#En-têtes];[Jan]:[Jun]];0))}

14. John Johnston says:

Just to be different....

I also found that the following works:

15. Michael says:

Will get you the answer

16. SAM MO says:

Name Monthly Ranges first

=XLOOKUP(D18,budgets[Department],INDIRECT(D19))

17. Eric Surdez says:

19. Zaig says:

20. Borg says:

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

• Borg says:

Sorry not using table address language. I prefer using classic addresses.

21. ari says:

well sumproduct is powerfull
=SUMPRODUCT((C4:C11=D18)*(D3:I3=D19)*D4:I11)

22. Sanditon needs a Season2 says:

23. Adem Kür?at Karaçil says:

=+?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),)

25. venky says:

Assuming table starts in A1

26. R1DZ says:

Using Index and Matching Look Up row & column wise

27. Guido says:

1. define rangenames for the rows and columns
2. then use the fomule =Marketing Apr

28. Parshwa says:

 « How to embed Excel files, calculators on your website? – Step by step instructions What is XLOOKUP? 13 formula examples to really understand it »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.