Excel - Using Formulas As Formula Parameters (Nesting)
If you are reading this, you most likely know how to use an Excel formula. VLOOKUP(G2, A1:E21,2,FALSE), right? Well, that's just scratching the tip of the iceberg.
Every parameter inside an Excel function can be replaced with a cell reference or even with another Excel function, allowing us to customize our formulas to suit any situation. Take the above example - it's a perfectly fine, perfectly functional example of a lookup. We can change the name in G2, and the value in H2 will change accordingly...
But only for Quarter1. What if we want to be able to get results from a different quarter, without having to mess with the intestines of our formula?
The third parameter of our VLOOKUP() signifies which column holds our result. In this case, we manually entered 2, to return results for the second quarter. But we don't have to hardcode that value in - we could use a cell reference instead:
See what I did here? The formula changed to VLOOKUP(G2,A1:E21,H2+1,0), so instead of a static 2, we now have a cell reference - H2 - where the user can manually input which quarter they want to look at. Of course, the results for quarter 1 are in the second column, for the second quarter in the third column and so on, so what I had to do was add 1 to the value in cell H2. See? We have already replaced the static value 2 with a formula, H2+1. And it doesn't end there!
What if we wanted to make it even more user friendly, and instead of manually typing in the quarter number, we used a drop down to select the quarter?
In this case, maybe we could amend our formula to this
We have replaced our H2+1 with a much more intimidating MATCH(H2,A1:E1,0). This formula in turn looks up our drop down result against the headers row to figure out which column the user wants to scan for Andy's results - and we're starting our MATCH() range from column A so we don't need to add a +1 any more.
Ah, but what if these results are just for one year, and we have more sets for other years?
See this monstrosity? Oof, someone also sorted each year by their Q4 results - and those at the top, they're merged cells in groups of 5! But we can still deal with this - we will just build a monstrosity of our own.
Assuming we have a dropdown for the year, we can use that to figure out which range to use in our formula. In keeping with the subject of this post, we will use another nested formula - that's what these formulas within formulas are called.
First, let's use a MATCH() to find out which cell holds the year in the dropdown, which is in cell B26. That's a simple MATCH(B26,1:1), and for 2023 it will return the value 10 - which is, for now at least, as useful to us as a spreadsheet to a bat. It will also return 2 for 2021, 6 for 2022 and 14 for 2024. Let's do a bit of math on our MATCH() formula:
=ROUNDUP(MATCH(B26,1:1)/4,0)
As the merged cells span 5 columns each, we can divide our MATCH() result by 5 and round up the results to get 1, 2, 3 or 4 as a result, depending on what year was selected. And now, we can use this inside a CHOOSE() function to select the relevant range for each year:
=CHOOSE(ROUNDUP(MATCH(B26,1:1)/4,0),A2:E22,F2:J22,K2:O22,P2:T22)
If we enter this in a cell, Excel will happily try to spill the results and give us the resulting range (remember the previous post?) We don't need that right now, so we can just pop this entire thing inside our VLOOKUP(), and it will use the result ranges as its target ranges accordingly:
=VLOOKUP(A26,CHOOSE(2,A2:E22,F2:J22,K2:O22,P2:T22),MATCH(C26,A2:E2,0),0)
We could keep going. Why not use an INDEX(MATCH(MAX))) instead of A26, to return the name of top achiever in a specific quarter of a specific year, and the corresponding value? Why not use a dropdown to let the user decide if they want an exact match for their search or not? Or let the demons win and use a RAND() function to decide that for them!
Everything can be nested, ad infinitum, and we can use that to tailor solutions to meet any needs. The formulas themselves may not look pretty but no complex machinery is - and at least we don't have to worry about oil stains!
Cat On A Spreadsheet
Full Service Consulting
Reporting
Automation
Cat On A Spreadsheet