Search

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

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.

Need a sample file? Get it from here.

Need a hint? Check out my 2D lookups article.

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.

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

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

1. TheQ47 says:

I'd use nested XLOOKUPs, as follows:

• Kathi says:

Almost the same, just accounted for "not found" and forced exact match.

Verification pointed out that Administration was misspelled so I added Data Validation to D18 and D19.

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:

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:

21. ari says:

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

22. Sanditon needs a Season2 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:

29. Mohan Krishna says:

=INDEX(D4:I11,MATCH(D18,C4:C11,0),MATCH(D19,D3:I3,0))

Please tell me if I am correct or wrong?

30. andok says:

here's mine, I hope this is a viable solution

31. Sandra says:

I used INDEX+MATCH+MATCH:

32. Anjali says:

=VLOOKUP(D18,budgets[#All],5,FALSE)

33. Sathish says:

=VLOOKUP(D18,budgets,5,0)

34. jaime says:

=INDICE(importes,COINCIDIR(D18,departamentos,0),COINCIDIR(D19,meses,0))

35. MM says:

{=SUM(IF((M8=M2:M5)*(N1:P1=N7),N2:P5,0))}

36. Mudit says:

 « How to embed Excel files, calculators on your website? – Step by step instructions 10 Advanced IF formula tricks you must know »

### Get FREE Excel & Power-BI Newsletter

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