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.
42 Responses
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))}
Department Marketing
Month Apr
Budget Amount 41500
=XLOOKUP(D14:D18,budgets[Department],budgets[Apr])