hours

minutes

seconds

Major BIWS Course Changes Coming on Nov 2, 2020

Learn More

Excel Practice Test for Interviews: Full Tutorial Using INDIRECT, MATCH, and SUMIFS [Tutorial Video] (14:55)

In this tutorial, you’ll learn how to complete an Excel practice test by writing a flexible 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.

For Aspiring Investment Bankers Only:

Excel Practice Test for Interviews: Full Tutorial Using INDIRECT, MATCH, and SUMIFS [Tutorial Video] (14:55)

Claim Your FREE Discounted Cash Flow (DCF) Video Tutorial Series

Master Financial Modeling As It Is Performed In Real Life, With Our Simple 3-Step Method

FREE INSTANT ACCESS

When it comes to Excel practice tests in interviews, the key is to have a solid knowledge of key Excel functions, such as SUMIFS, INDEX/MATCH, INDIRECT, and HLOOKUP/VLOOKUP, and then combine that knowledge with a lot of practice.

If you want to get an Excel practice test with simpler multiple choice questions, please see our BIWS Certificates page and look at the PDFs under “Excel & VBA” there.

But if you want a much more challenging Excel practice test that requires you to write real formulas in Excel, keep reading.

A Challenging Excel Practice Test

Often, interviewers will ask you to write a single formula that accomplishes a task elegantly rather than having to modify a formula slightly each time you use it.

For example, In 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.

Here’s a real-life example submitted by one of our students:

Excel Practice Test Prompt: “Review the quarterly real estate data for this apartment complex with 4 units, and write a SINGLE formula to retrieve everything on an annual basis.”

Here’s a partial screenshot of the data from the blank Excel file above:

Excel Practice Test - Monthly Setup

And here’s the annual summary page:

Excel - Annual Summary

The quarterly sheet is called “Quarters” and the annual sheet is called “Summary”.

So, how would you write a single formula to accomplish this?

Excel Practice Test: Recommended Approach

Instead of trying to write one formula all at once, break it into smaller pieces and build up to something more flexible by the end.

At each stage of the process, you want to have a functional formula in Excel – that way, even if you don’t finish it perfectly, you’ll still get credit for the work you did.

In this case, let’s start with a SUMIFS formula so that we can capture the quarterly data by date:

=SUMIFS(Quarters!$E$9:$T$9,Quarters!$E$2:$T$2,”<=”&Summary!E$2,Quarters!$E$2:$T$2,”>”&Summary!D$2)

Here’s what it looks like in Excel for the first row:

Excel SUMIFS Formula

This is a good start, but the problem is that since Quarters!$E$9:$T$9 refers to the summation range, we’ll have to update that and make row 9 into row 16, row 23, and so on, as we move down:

Manual Formula Changes for Rows

To make this part flexible, we need to match the text on the left to the same text in the quarterly data and then move down that number of rows.

For example, in this first row, we could write this formula to do it:

=MATCH($B4,Quarters!$B$1:$B$51,0)

Excel MATCH Function

But it’s not as simple as just inserting this MATCH function into the SUMIFS formula above.

The problem is that Excel only allows us to reference fixed ranges of rows and columns in spreadsheets… such as B10:E19 or E9:T9.

So, we can’t just “substitute in” one or several MATCH functions for the Quarters!$E$9:$T$9 range and be done with this Excel practice test.

But there is one exception: we can use the INDIRECT function to create a variable reference to another spreadsheet.

The syntax here gets a bit tricky because we’ll need to join together several parts to create a new version of the formula above, but here’s the basic approach:

First note, that double quotes (” “) indicate text within functions such as MATCH inside the INDIRECT function, and the ampersand symbol (“&”) joins text with functions.

So, in the original formula above:

=SUMIFS(Quarters!$E$9:$T$9,Quarters!$E$2:$T$2,”<=”&Summary!E$2,Quarters!$E$2:$T$2,”>”&Summary!D$2)

We’ll replace the Quarters!$E$9:$T$9 part with the following:

INDIRECT(“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0)&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

What does this do in the context of this Excel practice test?

The first part starts building the text for Quarters!$E$9:$T$9:

“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0) produces Quarters!$E$9.

This is because the first text here, “Quarters!$E$”, simply prints exactly as-is within the Excel function.

And then the MATCH($B4,Quarters!$B$1:$B$51,0) function finds the row position of the text we’re searching for in the “Quarters” spreadsheet, which is 9 in this case.

So, Excel sticks together these two parts: “Quarters!$E$” and 9 to form: Quarters!$E$9.

The next part here:

&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

Simply finishes the Quarters!$E$9:$T$9 text. We use the & character to join the first part to this part, and then we print the “:$T$” text in Excel.

Then, to find the appropriate row number, we use the same match function and join it with the & operator:

MATCH($B4,Quarters!$B$1:$B$51,0)

As a result, this entire term:

&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0)) turns into :$T$9 when Excel evaluates it.

Putting it altogether, here’s what happens with this entire formula:

INDIRECT(“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0)&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

becomes:

Quarters!$E$9:$T$9

And then as we copy this down, there’s no need to modify the formula manually because the MATCH functions keep finding the row number of the matching text on the Quarters spreadsheet:

Excel Practice Test - Solutions

Here’s the entire annual summary for this Excel practice test:

Annual Summary of Monthly Rental Data

How to Check Your Work

It is tricky to get the syntax with the double quotes and ampersands exactly correct in these types of INDIRECT functions, so you should always check your work, time permitting.

The best way to do this is to sum up manually each quarter’s figures for each line item and see if they match the annual totals – if not, you have a problem.

Here’s an example for Gross Potential Rent:

Excel Practice Test - Checking Your Work

Summary of Tests in Excel

In the Excel practice test – an actual exercise in Excel, not just a multiple choice test – the following topics are extremely common:

-Lookup functions and INDEX/MATCH

-Summation and summary functions such as SUMIFS

-INDIRECT, which lets you create custom references and variable cell ranges that you can pass into other functions and formulas

You don’t necessarily need to get the formula above 100% correct to “pass” the test.

The most important point is that you should make sure you have a working formula in each step of the process.

That way, even if you mix up some of the syntax in the INDIRECT function, or your formula doesn’t work perfectly in all cases, you can show them some of your work and get credit for it.

Master Excel & Financial Modeling

Learn Excel, valuation, and transaction modeling from the ground up - including stock pitches, case studies, deal and investment recommendations, and more.

LEARN MORE