Excel interview questions, and how to answer them

Question 1

What is the difference between VLOOKUP and XLOOKUP, and which would you use?

What it is really testing: Whether you have used lookups recently, or only read about them.

XLOOKUP takes the lookup array and the return array as separate arguments, so the return column can sit anywhere — including to the left of the lookup column. VLOOKUP takes a column index counted from the left edge of one block, which breaks the moment somebody inserts a column. XLOOKUP also defaults to an exact match and has a built-in if_not_found, where VLOOKUP defaults to approximate and needs wrapping in IFERROR. I would use XLOOKUP wherever it is available — but the useful half of the answer is knowing why VLOOKUP breaks, because you will still meet it in other people's files.

=XLOOKUP(A2, Customers[ID], Customers[Region], "Not found")

Practise this: Look up a customer's region with XLOOKUP →

Question 2

The value you need is in a column to the left of the one you are matching on. How do you look it up?

What it is really testing: Whether you have a second lookup approach, or reach for rearranging the data.

INDEX and MATCH. MATCH finds the row number of the value in whichever column holds it, and INDEX returns whatever sits at that row in whichever column you want — the two are independent, so direction stops mattering. XLOOKUP solves the same problem in a single call. Copying the column to the right of the data works too, but it edits the source to suit the formula, and that is the answer that gets a follow-up question.

=INDEX(Prices, MATCH(A2, Codes, 0))

Practise this: Find a value with INDEX and MATCH →

Question 3

What do the dollar signs in $B$2 do, and when do you need them?

What it is really testing: Whether you have ever filled a formula down a column and watched it break.

They freeze part of the reference when the formula is copied. B2 shifts both column and row, $B$2 shifts neither, and B$2 and $B2 each freeze one. It matters most for a lookup range: fill =VLOOKUP(A2, B2:D50, 3, FALSE) down a column and the range slides to B3:D51, then B4:D52, and rows fall off the bottom of the table one by one. Lock it as $B$2:$D$50 and every row looks at the same table. The failure is quiet — the formula still returns numbers, just wrong ones — which is exactly why interviewers like the question.

Practise this: Lock ranges so a lookup survives fill-down →

Question 4

How would you total sales for one region in one month?

What it is really testing: Whether you can filter with a formula rather than with the mouse.

SUMIFS, with one criteria pair per condition: the range to add, then the region range and the region, then the month range and the month. The reason to prefer it over filtering and reading the total off the status bar is that it recalculates when the data changes, so the number is still right next month and after somebody appends rows. Watch the argument order — SUMIFS puts the sum range first and SUMIF puts it last, and swapping them is the most common way this goes wrong.

=SUMIFS(Amount, Region, "North", Month, "March")

Practise this: Total sales for a region and month with SUMIFS →

Question 5

Your lookup is returning #N/A. Walk me through how you work out why.

What it is really testing: Whether you debug in a sequence, or start retyping the formula and hoping.

#N/A means the value was not found, so I check in order: is the lookup value actually present in the lookup column; are both sides the same type, since a number stored as text will never match a real number; are there trailing spaces, which TRIM will expose; and is the match mode exact rather than approximate. Only then do I question the range. Once I know the cause I decide whether a blank is expected — if it is, I handle it with XLOOKUP's if_not_found rather than wrapping the whole thing in IFERROR, which would swallow the real problems along with the expected ones.

Practise this: Handle missing lookups gracefully →

Question 6

Here is a formula that returns the wrong number. Find the problem.

What it is really testing: The most job-like question there is — most spreadsheet work is inheriting somebody else's.

I work backwards from the output. Evaluate Formula, or pulling the formula apart into spare cells, shows which piece returns something unexpected. The usual culprits are a range that was never locked and has slid, a condition pointed at the wrong column, a nested IF whose bands overlap or leave a gap, and a hard-coded number sitting where a reference should be. Saying that you would check the result against a row you can work out by hand is worth as much as naming the bug — it shows you verify rather than assume.

Practise this: Fix a broken commission formula →

Question 7

How would you find which customers are in one list but not in the other?

What it is really testing: A reconciliation job that comes up constantly in finance and operations.

COUNTIF each value against the other list — a count of zero means it is missing. Running it the other way round finds the ones that exist only in the second list, and both directions matter, because “the lists do not match” usually means there are differences in each. XLOOKUP with an if_not_found value does the same job and can bring back the missing detail as well. The trap is assuming a single pass answers the question.

=COUNTIF(ListB, A2) = 0

Practise this: Find which customers are missing from a list →

Question 8

This export has trailing spaces and inconsistent capitalisation. How do you clean it?

What it is really testing: Whether you have handled real data, which never arrives tidy.

TRIM removes leading, trailing and repeated internal spaces; UPPER, LOWER or PROPER settle the casing; and they nest, so one pass does both. It matters because lookups match on exact strings — “ACME ” and “ACME” are different values, and the #N/A that follows looks like a broken formula rather than dirty data. For the non-breaking spaces that come out of web exports TRIM alone will not do it, and you need CLEAN or a SUBSTITUTE of CHAR(160) — a good detail to mention.

=UPPER(TRIM(A2))

Practise this: Standardise messy codes with TRIM and UPPER →