Advanced Excel interview questions and answers

Excel interviews for MIS, accounts and analyst roles follow a predictable shape. A handful of formula questions, one or two about PivotTables, something about cleaning messy data, and — the part that actually decides it — a practical test with a file.

Grouped by what is being tested, preparation gets much more efficient.

Lookups — the most-asked category

What is the difference between VLOOKUP and INDEX-MATCH?

VLOOKUP searches the first column of a range and returns a column to its right, identified by position. INDEX-MATCH finds the row separately from the column, so it can look leftward and does not break when a column is inserted.

The follow-up that matters: why does VLOOKUP break so often? Because the column index is a hard-coded number. Insert a column in the middle of the table and every VLOOKUP silently returns the wrong field — no error, just wrong data. That silence is the reason experienced people avoid it.

How is XLOOKUP different?

It searches and returns in separate arguments, defaults to exact match, can return arrays, has a built-in not-found argument, and searches in either direction. It solves essentially every VLOOKUP complaint.

Add the practical caveat: it is unavailable in older Excel versions, so files shared with clients on legacy installs still need INDEX-MATCH. Saying this shows you think about who else opens the file.

Why does my lookup return #N/A when the value is clearly there?

Almost always one of three causes: a number stored as text on one side, trailing whitespace, or an approximate-match argument left at its default.

Say how you diagnose it, not just what causes it. Compare lengths with LEN, strip with TRIM, and coerce type. This answer signals you have debugged real files rather than reading about them.

Aggregation and conditional logic

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition, SUMIFS takes multiple. Note that the argument order differs between them — SUMIFS puts the sum range first — which is a common source of errors when converting one to the other.

How do you handle errors inside a formula?

IFERROR wraps a formula and substitutes a value when it fails. The important caveat, and a good thing to volunteer: IFERROR hides every error including the ones you needed to see. Wrapping a whole workbook in it is how a broken lookup goes unnoticed for six months.

When would you use SUMPRODUCT?

For conditional aggregation across arrays, or multiplying two ranges pairwise — quantity by rate, for example — without a helper column. It also predates SUMIFS, so you will meet it in older files and need to read it.

PivotTables

What are the limits of a PivotTable?

It summarises but does not clean. If the source has inconsistent spellings, merged cells or numbers stored as text, the pivot faithfully reproduces the mess. Cleaning belongs upstream — ideally in Power Query.

What is a calculated field, and when does it give a wrong answer?

A calculated field computes within the pivot. The classic trap is a percentage or ratio: the pivot computes the formula on the aggregated totals, not the average of the row-level values, and those differ. Knowing this is a strong signal.

What does GETPIVOTDATA do?

It returns a value from a pivot by field reference rather than cell position, so your report does not break when the pivot’s layout shifts. Most people disable it because the auto-generated formula looks intimidating; the ones who use it build reports that survive.

Data cleaning and Power Query

How do you clean a messy export?

Walk through a method rather than naming features: check for duplicates, trim whitespace, standardise case and spelling, coerce data types, and handle blanks deliberately — a blank and a zero are not the same thing.

Then say that you would do it in Power Query rather than by hand, so it replays next month on refresh.

What is Power Query and why does it matter?

An extract, transform and load tool built into Excel. You define cleaning steps once and they re-run on new data when you refresh.

This is the highest-value answer in an MIS interview. Most candidates describe cleaning data manually every month. Describing a pipeline that eliminates the monthly task is a different level of answer, and interviewers notice.

How do you find duplicates without removing them?

COUNTIF on the key column, flagging anything greater than one, or conditional formatting to highlight them. The distinction matters — removing duplicates before you have looked at them is how legitimate repeated transactions get deleted.

The practical test

Most MIS and analyst interviews end with a file and a time limit. A few things that reliably help.

Check the row count before and after every join or filter. More candidates lose on a silent fan-out than on a formula they could not write.

Validate one number by hand. Pick a single row and verify your summary against it. Being able to say “I checked this total against the source” is worth more than finishing early.

Say what you would do with more time. Nobody expects a polished dashboard in forty minutes. Describing what you would automate, and why, demonstrates the judgement the job actually needs.

The question behind the questions

A number in your report looks wrong. What do you do?

This decides more interviews than any formula.

Say you check what one row represents, then the row counts around each lookup or filter, then whether blanks are being treated as zero, then whether the date range covers what you assumed. Say you validate against a known total from another source before trusting your own output.

Candidates who answer this well tend to get the offer, because it is the closest thing in the interview to the actual job.