Posted On: 2026-08-11
Posted By: Lery

Excel Skills Finance Employers Actually Test For (INDEX-MATCH, Scenarios, VBA)

"Advanced Excel" is the most meaningless phrase on a finance CV. Everyone writes it. Almost nobody defines it. And it survives on applications precisely because there is no way to verify it from a document.

That is exactly why the test exists. An Excel assessment cannot be inflated the way a bullet point can. You either build the pivot table or you do not. You either write the INDEX-MATCH correctly or the formula breaks. Live Excel assessments are now a standard part of hiring across finance roles, and a TestGorilla survey found that around 68% of financial services leaders still rely on Excel as a primary tool.

The encouraging part is that these tests are highly predictable. Roughly fifteen functions cover about 90% of what appears on employment Excel assessments, and the failure patterns are consistent enough to prepare against directly.

This article covers what gets tested at each level, what the assessor is actually watching for, and where candidates lose marks they did not know were available.

How the test is administered

Four common formats:

A timed task during the interview. Typically 30 to 60 minutes with a dataset you have never seen. The most common format for analyst roles.

A screen-share live exercise. You work through a problem in real time while explaining your thinking. This assesses technical skill and communication simultaneously, and it is increasingly common for roles involving cross-functional work.

A take-home model. More time, higher expectations on structure and presentation.

A platform assessment. Kenexa Prove It is common in financial services and runs performance-based tasks in a simulated Excel environment. iMocha uses adaptive testing that adjusts difficulty based on your answers. TestGorilla and eSkill combine knowledge questions with task-based evaluation.

One detail people miss: you are scored on speed as well as accuracy, even when no time limit is stated. Some formats, such as the Kenexa Power User test with its 25 tasks, have no formal clock but still measure how long you take.

The three things being graded

Employers assess across three dimensions, and candidates typically prepare for only the first.

Accuracy. Correct formulas, clean outputs, right answer.

Efficiency. Speed, keyboard shortcuts, and appropriate use of automation rather than manual repetition.

Judgment. How you structure the data, which approach you choose, how you present results, and whether you validate your own work.

Judgment is the differentiator, because it is what separates someone who knows functions from someone who can be trusted with a model. Demonstrating it is often what moves a candidate from acceptable to hired.

Tier one: the foundations that quietly fail people

Most failures happen here rather than at the advanced level, because candidates underestimate how precisely basic skills are assessed.

Absolute and relative references. Knowing when to lock a reference with $ and when not to. Forgetting to lock a reference when copying a formula down a column is one of the most common errors on these tests, and it produces silently wrong answers rather than visible errors.

Named ranges and Paste Special. Basic scaffolding for readable formulas.

Data cleaning. Real datasets are messy and test datasets are deliberately messy. Know TRIM, Text-to-Columns, Remove Duplicates, Flash Fill, and Data Validation rules. A test that hands you inconsistent text or numbers stored as text is checking whether you notice before you calculate.

Text and date functions. LEFT, RIGHT, MID, CONCAT, TEXT, DATE, YEAR, and EOMONTH. Finance datasets are full of dates that need manipulating into periods.

If any of these are shaky, fix them first. There is no point drilling scenario analysis while losing marks on absolute references.

Tier two: lookups, the centrepiece of finance testing

This is where most finance Excel tests concentrate, and where hesitation is most visible.

The interviewer hands you a dataset and asks you to pull values from it. What they are checking is not only whether you can do it, but whether you know which tool to use and can explain why without being prompted.

VLOOKUP: the entry-level expectation

=VLOOKUP(lookup_value, table_array, col_index, FALSE)

Searches the first column of a range and returns a value from a specified column. Always use FALSE for exact match unless you have a specific reason not to.

Its limitations are the point. It only looks left to right, it breaks when columns are inserted or reordered, and it scans the entire row range, which is slow on large datasets. Knowing VLOOKUP alone marks you as entry level.

INDEX-MATCH: the professional standard

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Many employers specifically test whether you know INDEX-MATCH as an indicator of advanced proficiency. Three reasons it is the standard in finance:

It works in both directions. It can return values from columns to the left of the lookup column, which VLOOKUP cannot.

It does not break when columns are reordered. Because it references columns directly rather than counting positions, inserting a column does not silently corrupt the result. In a model that others will edit, this matters enormously.

It is more efficient on large datasets. It examines only the specific columns involved rather than the full row range.

Be ready to write it from memory and explain the nesting. "MATCH finds the position, INDEX returns the value at that position" is the explanation assessors want to hear.

XLOOKUP: increasingly expected

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Available in Excel 365 and current versions, and now expected for analyst roles rather than treated as a bonus. It handles bidirectional lookups like INDEX-MATCH but with simpler syntax, and it adds useful features: a built-in default when nothing is found, and options to return the first or last match.

The nuance worth knowing: XLOOKUP is easier to read, write, and debug, while INDEX-MATCH tends to calculate faster in large models. If asked which you prefer, that trade-off is the correct answer, along with the practical point that XLOOKUP is unavailable in older versions, which still matters in organisations running legacy files.

The likely question: "When would you use INDEX-MATCH over VLOOKUP?" Answer it without hesitating. Hesitation here is visible and it is noted.

Tier two continued: conditional aggregation

Nearly as common as lookups and often combined with them.

SUMIFS and COUNTIFS for multi-criteria aggregation. Know the argument order, since it differs from SUMIF.
IF and IFS for logical branching, plus IFERROR to handle errors gracefully rather than displaying #N/A across a model.
SUMPRODUCT for weighted calculations and multi-condition logic, which appears in more advanced tests.

A typical composite task: given a raw transaction dataset, calculate total spend for a specific category within a specific date range and return the associated cost centre. That single task tests lookups, conditional aggregation, and date handling together.

Tier three: pivot tables

A standard task looks like this: here is a raw transaction dataset, build a summary by category and extract one specific metric.

The assessor is checking whether you can build a pivot from scratch, group correctly, and read the output accurately. Three specific things to get right:

Check the aggregation type. Placing a text field in the Values area defaults to Count, which is frequently not what the task asked for. This is a common silent failure.

Group dates properly into months, quarters, or years as required.

Know how to refresh and how to change the source range, since take-home tasks often add data afterwards.

Pivot tables are also the fastest route to answering many test questions. Candidates who write twelve SUMIFS formulas where a pivot would have taken ninety seconds lose marks on efficiency even when the answers are correct.

Tier four: scenarios and sensitivity analysis

This is where finance tests diverge from general Excel tests, because forecasting under uncertainty is the actual job.

Data tables. One-way and two-way data tables for sensitivity analysis. If you build a valuation or a forecast, expect to be asked how the output changes across a range of two key assumptions. This is the standard tool and many candidates have never used it.

A scenario switch. The professional construction uses CHOOSE or INDEX driven by a single input cell:

=CHOOSE($C$4, base_case_value, upside_value, downside_value)

Change one cell and the entire model moves between cases. Building this cleanly demonstrates modelling maturity more than any single formula.

Goal Seek. Simple, underused, and directly relevant to questions such as what revenue is required to break even.

Scenario Manager. Less commonly used in practice than data tables and switches, but worth recognising.

The judgment element matters here. Presenting a single output figure where a sensitivity range was appropriate is a substantive error in finance, not a stylistic one.

Tier five: VBA and macros

The honest position on VBA is more nuanced than most guides admit.

What is genuinely expected at analyst level: recording a macro, reading and lightly editing recorded code, running a macro, and understanding what a macro can and cannot safely automate. Being able to say "I automated the monthly reformatting of a report with a recorded macro and edited the code to handle variable row counts" is a strong, credible claim.

What is rarely expected: writing complex VBA from scratch, custom userforms, or class modules. Some quantitative or systems-adjacent roles want this. Most analyst roles do not.

The honest caveat: VBA matters less than it did a decade ago. Power Query has replaced a large share of what people used to automate with macros, and Python is displacing it in data-heavy environments. Many organisations now restrict macro-enabled files for security reasons.

What to do about it. Learn to record and edit macros, because it is a weekend of work and appears on job descriptions. Then invest further effort in Power Query, which handles repeatable data transformation better and is more likely to be valued. If a role specifically lists VBA, take it seriously. Otherwise treat it as useful rather than essential.

Power Query specifically deserves attention: importing, cleaning, and reshaping data through a repeatable query is exactly the workflow that consumes analyst time, and it is increasingly named in finance job postings.

Model structure: graded whether or not it is mentioned

On any test where you build rather than answer, structure is assessed even when the instructions say nothing about it.

Separate inputs from calculations. Assumptions live in one clearly marked area, never buried inside formulas. Hardcoding a growth rate inside a formula is the single most common structural criticism.

Use the colour convention. Blue for hardcoded inputs, black for formulas. This is the standard in finance and its absence is noticed immediately.

One formula per row. A row where the logic changes halfway across is the most common source of real-world modelling errors, and assessors check for it deliberately.

Build error checks. A balance check that returns zero. A total that reconciles to a source. Including one unprompted signals professional habits.

Label clearly and keep units consistent. State whether figures are thousands or millions.

The fifteen functions worth drilling

These cover the overwhelming majority of what appears:

VLOOKUP, XLOOKUP, INDEX, MATCH, IF, IFS, IFERROR, SUMIFS, COUNTIFS, SUMPRODUCT, TRIM, TEXT, EOMONTH, ROUND, and NPV or IRR for finance-specific tests.

Drill them until you write them without looking up the argument order. Speed here is what buys you time for the harder parts of the task.

Where candidates actually lose marks

Not on advanced technique. On these:

Using the wrong lookup function. Reaching for VLOOKUP when the return column sits to the left of the lookup column. Switch to INDEX-MATCH or XLOOKUP.
Forgetting absolute references. Copying a formula down a column without locking the reference cells produces wrong results in every subsequent row, usually without an error message.
Leaving a formula in edit mode. The formula is not saved until you press Enter. Candidates lose completed answers this way under time pressure.
Incorrect pivot table field placement. Text in the Values area defaults to Count.
Not reading the instructions completely. Requirements are frequently buried in the task description: round to two decimal places, sort descending, exclude a category. Assessors include these deliberately.

That last one is worth repeating. A substantial proportion of lost marks on finance Excel tests come from answering a slightly different question than the one asked, which is also, not coincidentally, the most common failure in real analyst work.

Preparing in two weeks

Days 1 to 3. Foundations. Absolute references, data cleaning functions, text and date handling. Take a messy dataset and clean it end to end.

Days 4 to 7. Lookups and conditional aggregation. Write each of VLOOKUP, INDEX-MATCH, and XLOOKUP twenty times against real data until argument order is automatic. Practise explaining the differences aloud.

Days 8 to 10. Pivot tables and a full summary task. Take a public transaction dataset, build summaries by multiple dimensions, and extract specific metrics under a timer.

Days 11 to 12. Scenarios. Build a small forecast with a CHOOSE-driven scenario switch and a two-way data table.

Days 13 to 14. Timed practice on unfamiliar data, plus recording and editing one macro. Practise with the mouse as far away as you can tolerate.

Tactics for the live test

Ask clarifying questions before starting. What period, what treatment for blanks, which rounding. This reads as judgment, not uncertainty.

Narrate your thinking on screen-share tests. The assessor is evaluating your reasoning as much as your output. Silence forces them to guess.

Structure before you calculate. Two minutes laying out inputs and outputs saves ten minutes of untangling.

Check your work visibly. Sense-check a total, verify a lookup returned what you expected. Doing this out loud demonstrates the habit employers most want to see.

Do not panic on an error. Trace precedents, explain what you are checking. Diagnosing calmly scores better than a flawless first attempt delivered silently.

What to write on your CV instead of "advanced Excel"

Replace the unverifiable claim with specifics an assessor can recognise:

"Advanced Excel including INDEX-MATCH and XLOOKUP, SUMIFS, pivot tables, two-way data tables for sensitivity analysis, scenario switches, and recorded macro automation. Built and maintained a rolling 13-week cash forecast with scenario toggles, reducing monthly preparation from four days to one."

That names the tools, demonstrates application, and quantifies the result. It also gives the interviewer something specific to ask about, which is an advantage when you have prepared for the question.

Since the CV has to clear automated screening before a human evaluates any of this, our guide on how to write a finance CV that passes ATS screening covers where technical skills belong and includes a full worked example. And if the underlying financial logic behind the models needs reinforcing, the money knowledge every professional should have covers the statements and ratios these spreadsheets are ultimately calculating.

The bottom line

The Excel test exists because it is one of the few parts of a hiring process that cannot be talked around. It is designed to catch the gap between what a candidate claims and what they can produce under time pressure with unfamiliar data.

That makes it the most preparable stage of the entire process. The functions are known, the task formats repeat, and the failure modes are documented. Two weeks of deliberate practice moves most candidates from anxious to comfortable.

Drill the lookups until they are automatic, build one scenario model from scratch, learn to record a macro, and read the instructions twice. That is genuinely most of it.

Excel Skills Finance Employers Actually Test For (INDEX-MATCH, Scenarios, VBA)