Advanced Excel Interview Questions and How to Answer

Advanced Excel interview questions go past "what does VLOOKUP do?" to "what would you use instead, and why?" Interviewers for analyst, finance and operations roles want to see that you can pick the right tool for a messy problem and explain the trade-offs. This guide covers twelve questions that come up often, with answers worked on small tables you can type in.

The sample data#

Most answers below use this sales table in A1:D6:

If the job only asks for intermediate skills, start with the general list of Excel interview questions instead.

Lookup questions (1–4)#

Lookups are the heart of most advanced interviews. Be ready to write them and to explain why.

1. Why would you use INDEX MATCH instead of VLOOKUP? INDEX MATCH can return a column to the left of the lookup column, it doesn't break when someone inserts a column, and it extends to two-way lookups. VLOOKUP only looks right and relies on a fixed column number. Example: =INDEX(A2:A6,MATCH(4700,D2:D6,0)) returns Dee, a name to the left of the Sales column. See the INDEX function guide.

2. How do you do a two-way lookup? Use INDEX with two MATCH functions: one finds the row, the other finds the column. On a table with reps in A2:A5 and quarters in B1:D1:

=INDEX(B2:D5,MATCH("Cruz",A2:A5,0),MATCH("Q2",B1:D1,0))

returns the value where Cruz's row and the Q2 column meet. In the exercise on this page, that's 3400.

3. How do you look up with two conditions? In Microsoft 365, multiply the conditions inside XLOOKUP:

=XLOOKUP(1,(B2:B6="West")*(C2:C6="Laptop"),A2:A6)

Each test gives TRUE or FALSE per row; multiplying turns them into 1 only where both are true. The result is Dee. In older versions, a helper column that joins Region and Product works with any lookup. The guide to lookups with multiple criteria shows every version.

4. What can XLOOKUP do that VLOOKUP can't? It looks left, defaults to an exact match, has a built-in "if not found" argument and can search from the bottom up. =XLOOKUP("East",B2:B6,A2:A6,"None",0,-1) returns Eko, the last East rep. XLOOKUP needs Excel 2021 or Microsoft 365. See the XLOOKUP function guide.

Dynamic array questions (5–7)#

Dynamic arrays return many results from one formula. They need Excel 2021 or Microsoft 365.

5. How do you return every row that matches two conditions? Use FILTER with multiplied conditions for AND:

=FILTER(A2:D6,(B2:B6="East")*(D2:D6>3000),"None")

spills the rows for Ana and Eko. Cruz is East but below 3000. For OR, add the conditions instead: (C2:C6="Phone")+(D2:D6>5000) returns Ana, Ben and Cruz. See the FILTER function guide.

6. What is a #SPILL! error and how do you fix it? A dynamic array needs empty cells to spill into. If anything is in the way, even a space, Excel shows #SPILL!. Clear the blocking cells. #SPILL! can also appear inside an Excel table, because tables don't allow spilled ranges.

7. How do you get a sorted list of unique values? Nest UNIQUE inside SORT: =SORT(UNIQUE(B2:B6)) returns East and West. To count them, wrap it in ROWS or COUNTA: =ROWS(UNIQUE(B2:B6)) returns 2.

Pivot table questions (8–9)#

Advanced roles expect more than a basic pivot. The full set of pivot table interview questions goes deeper.

8. How do you show each region's share of total sales in a pivot? Right-click a value, choose Show Values As > % of Grand Total. East shows 60.4% and West 39.6% here (11,900 and 7,800 out of 19,700).

9. When would you use SUMIFS instead of a pivot table? When the report must update instantly without a refresh, follow a fixed layout, or feed other formulas. Pivots are faster to build and explore; SUMIFS is easier to control. For example, =SUMIFS(D2:D6,B2:B6,"East",C2:C6,"Laptop") returns 9100 (5200 + 3900) and recalculates the moment a number changes. A good answer mentions that you'd often use both: a pivot to explore, then SUMIFS for the final report.

Problem-solving questions (10–12)#

These test judgment as much as formulas.

10. How would you find who has the highest sales? =INDEX(A2:A6,MATCH(MAX(D2:D6),D2:D6,0)) returns Ana. In Microsoft 365, =XLOOKUP(MAX(D2:D6),D2:D6,A2:A6) does the same.

11. When should you use IFERROR, and when not? Use it where a missing value is expected, such as an ID that may not exist. Don't wrap every formula in it: it hides real mistakes, such as a typo in a range. IFNA is safer for lookups because it catches only #N/A.

12. How do you check a workbook someone else built? Trace precedents and dependents (Formulas tab), show formulas with Ctrl+ (Mac: Control+), look for hard-coded numbers inside formulas, and add check totals that should equal zero. Mentioning these shows you think about reliability.

How to answer advanced questions well#

Give the method, the reason and a quick example, in that order. "I'd use INDEX MATCH because the ID is to the right of the name; MATCH finds the row and INDEX returns the name" is a complete answer. Mention version limits when relevant, and if you'd use Power Query or a macro for a big repeated task, say so. For what employers mean by advanced in the first place, see advanced Excel skills for jobs, and for analysis roles, Excel skills for data analysts.

Practise it#

Advanced answers only come easily after you've typed them many times. Practise INDEX MATCH in a live spreadsheet, practise XLOOKUP and pull matching rows with FILTER: you type the formula, and it checks it instantly. Then go further with Pivot tables 2, and test yourself with the timed advanced Excel mock test.