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

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:

And here’s the annual summary page:

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:

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:

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)

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:

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

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:

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.

About Brian DeChesare

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.