Excel Tests in Interviews: INDIRECT, MATCH, SUMIFS, and More

In this tutorial, you’ll learn how to write a flexible Excel formula that lets you summarize quarterly or monthly data in an annual format using the INDIRECT, MATCH, and SUMIFS functions. This is a common task given in Excel tests and case studies, especially in industries such as real estate.
breakingintowallstreet.com/
"Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
Table of Contents:
2:54 Using SUMIFS to Make the Dates Flexible
4:59 Using MATCH and INDIRECT to Make the Entire Function Flexible
11:20 Testing the Formula
13:42 Recap and Summary
Resources:
youtube-breakingintowallstree...
youtube-breakingintowallstree...
youtube-breakingintowallstree...
Lesson Outline:
Some of the most frequently-tested topics in Excel tests include the proper uses of lookup functions (HLOOKUP and VLOOKUP), INDEX/MATCH, INDIRECT, and the SUM, SUMIF, and SUMIFS functions to find and summarize data.
Often, interviewers will ask you to write a single function that accomplishes a task elegantly rather than having to modify the function slightly or otherwise change it each time you use it.
In many cases, you could write simple SUM formulas to sum up cells manually, but it’s far more robust to use the SUMIFS function so that you can check the dates and include only the matching quarterly or monthly data for the year you’re in.
But to make the function truly flexible so that you can copy and paste it down and around and use it to sum up data for different rows, you must use the MATCH and INDIRECT functions.
MATCH lets you move down to the appropriate row based on the data you need - for example, if “Profits” is 25 rows down in the monthly spreadsheet, the MATCH function will retrieve 25 when you use it in that spreadsheet with “Profits” as the input.
Then, INDIRECT lets you create your own variable references to other spreadsheets.
For example, instead of using E9:T9 as the fixed range, you could let the “9” parts vary based on the row or column you’re in or the output of functions.
We used INDIRECT and MATCH to rewrite the SUMIFS function with a fixed summation range and make the summation range variable.
The function is more flexible because when we copy it down, the summation range reference will change, and the row will match the correct row number of the data we’re seeking.

[@Eduardozco]

This is the lesson that convinced me to buy the premium course. Loving it so far

[@financialmodeling]

Thanks! Glad to hear it. We are adding to it and revising content each quarter this year.

[@serhiidubrovin8205]

Thank you for awesome content!

[@parkervance3700]

Great video, very easy to follow. Thank you!

[@financialmodeling]

Thanks for watching!

[@Bertztuful]

Just AMAZING ! I wish I had seen this video 1 year when I did all this work manually

[@financialmodeling]

Thanks! Glad to hear it.

[@Batmanananana]

Thnx for these vids. Really appreciate them! Keep it up!:)

[@financialmodeling]

Thanks for watching!

[@davidcarragher5151]

This was excellent. Thank you.

[@financialmodeling]

Thanks for watching!

[@xhesitase9729]

Your content never disappoints!

[@financialmodeling]

Thanks for watching!

[@alanmt8819]

It's just awesome! Encountered those puzzling things before, but only in this video it is explained absolutely clearly! Thank you guys for such a great content! :-)

[@financialmodeling]

Thanks for watching!

[@zelinli4287]

Impressive! Thank you so much

[@financialmodeling]

Thanks for watching!

[@kendrewt]

Terrific!

[@financialmodeling]

Thanks for watching!

[@garimakhanna8625]

Hi, where can I download the excel file used in the video?

[@financialmodeling]

Click "Show More". Click the links under "Resources."

[@jonaswesley2658]

so why did you anchor the number "2" in the formula? i didn't get that. To me it seems like nothing is achieved by doing so

[@financialmodeling]

There are several references to "2" in this formula, so I don't know which you're referring to, but if it's something like this, Quarters!$E$2:$T$2,"

[@TheNBFCOAProductions]

This is a nice and clear guide, but using the sumproduct function as a “2 directional sumif” would be much easier than this

[@financialmodeling]

Yes, that's true, but the goal here was to illustrate how INDIRECT works in this context.

[@randomizer_1234]

Could you provide the solution with sumproduct ?
Thansk a lot

[@bigworm3886]

Or you could just pivot table the quarters data and filter it by year

[@financialmodeling]

You could, but they asked for a formula solution, not something using pivot tables.

[@pv0315]

very very scary