Advanced Excel interview questions on XLOOKUP, pivots and Power Query
In this article (4 sections)
Related programme: the Data Analytics with Generative AI course (3–4 months; ₹90,000 (inclusive of GST) · EMI from ₹4,000/month).
Advanced Excel interviews move past "do you know VLOOKUP" to whether you can build a report that survives new data: dynamic lookups, pivot calculations that mean what the manager thinks they mean, and Power Query steps that rerun on next month's file. The table and worked answers below cover twelve questions at that level, contrasting the answer that gets a nod with the one that gets you the next round.
Sheet references assume a hypothetical sales table named tblSales with columns Date, Region, Product, Rep and Amount.
Lookups: questions 1 to 4
| Question | Weak answer | Strong answer |
|---|---|---|
| 1. Look up a rep's region when the ID column is to the right of the name. | "Move the column so VLOOKUP works." | =XLOOKUP(G2, tblRep[RepID], tblRep[Region], "Missing") — XLOOKUP can return a column to the left. On older Excel, INDEX(tblRep[Region], MATCH(G2, tblRep[RepID], 0)). |
| 2. Look up using two conditions, Region and Product. | "Make a concatenated helper column." | Works, but =XLOOKUP(1, (tblPrice[Region]=H2)*(tblPrice[Product]=I2), tblPrice[Price]) does it without a helper. The multiplication makes an array of 1s and 0s, and XLOOKUP finds the first 1. |
| 3. Find the commission slab for a sales amount, where slabs start at 0, 1,00,000 and 5,00,000. | "Nested IFs." | An approximate lookup: =XLOOKUP(J2, tblSlab[From], tblSlab[Rate], , -1). Match mode -1 returns the exact match or the next smaller value. It scales when a slab is added; nested IFs do not. |
| 4. Return all rows for one region, not just the first. | "Use a filter and copy." | =FILTER(tblSales, tblSales[Region]=K1, "No rows") on Microsoft 365. It spills and updates when data changes. Wrap in SORT(..., 5, -1) to sort by Amount descending. |
What the interviewer is checking in this block: whether you know match modes rather than a single lookup habit, and whether your solutions keep working when the source table grows.
Pivot tables: questions 5 to 8
5. The manager asks for each product's share of its region's total. How?
Weak: "I'd add a formula next to the pivot dividing by the region total."
Strong: In Value Field Settings, Show Values As, % of Parent Row Total, with Region above Product in Rows. The percentage then updates with filters and slicers; a formula next to the pivot breaks as soon as the pivot changes shape.
6. A calculated field for margin shows the wrong number at the total row. Why?
Strong answer: a pivot calculated field applies the formula to the sums of the fields, not row by row. Margin % = Profit / Revenue is fine at the total because it becomes sum of profit over sum of revenue, which is what you want. But = Qty * Price becomes sum of Qty times sum of Price, which is meaningless. Compute row-level values in the source table, or in the Data Model with a DAX measure such as SUMX.
7. You added last week's rows to the data and the pivot didn't change.
Strong: the pivot's source was a fixed range. Convert the data to an Excel Table so the source grows automatically, then Refresh. Mentioning Refresh All and "refresh data when opening the file" in PivotTable Options shows you have maintained a live report, not just built one.
8. Two pivots on one dashboard should respond to the same Region slicer.
Strong: both pivots must come from the same pivot cache or the same Data Model; then Slicer, Report Connections, and tick both. If one was built from a copy of the data, it cannot be connected — which is a good reason to build from one Table.
Power Query: questions 9 to 12
9. The monthly target file has months as columns (Apr, May, Jun...). You need it as rows.
Weak: "Copy, paste special, transpose."
Strong: In Power Query, select the ID columns (Region, Rep), then Unpivot Other Columns. That produces Attribute (month) and Value (target) columns. Choosing "Unpivot Other Columns" rather than "Unpivot Columns" matters: when a new month column arrives, it is unpivoted too instead of being left behind.
The step Power Query writes looks like this in M:
= Table.UnpivotOtherColumns(Source, {"Region", "Rep"}, "Month", "Target")10. Combine twelve monthly CSV exports in a folder into one table.
Strong: Get Data, From Folder, Combine and Transform. Power Query builds a sample-file function and applies it to every file; next month you drop a new CSV in and click Refresh. Add a check step: filter out any repeated header rows, because some exports write the header on every page.
11. Merge the sales table with the rep master. What join kind?
Strong: Left Outer, to keep every sale even if a rep is missing from the master. Then, as a check, do a second merge as Left Anti to list the sales whose rep ID is not in the master. The anti-join is the answer interviewers rarely hear, and it shows you verify merges instead of trusting them.
12. Why is Power Query better than formulas for this work?
Strong: the steps are recorded, repeatable and readable in the Applied Steps pane, so another person can audit what happened to the data. Formulas over 50,000 rows also recalculate slowly; Power Query loads once on refresh. Be honest about the limit — it is a preparation tool, and fast-changing what-if analysis still belongs in formulas.
A three-line formula to show at the end
If you are asked to show something you are proud of, a short LET formula demonstrates readability:
=LET(
sales, SUMIFS(tblSales[Amount], tblSales[Region], A2),
target, XLOOKUP(A2, tblTarget[Region], tblTarget[Target], 0),
IF(target=0, "No target", sales/target))Naming the pieces makes the formula self-explaining and avoids calculating the same SUMIFS twice. It also handles the region with no target, which is where a plain division would show #DIV/0!.
The basics these questions build on are in the MIS executive Excel interview. When pivots start straining, the natural next tool is Power BI; see the Power BI and DAX question bank, and the Data Analyst programme takes the same report from Excel to SQL to Power BI.
Frequently asked questions
Is XLOOKUP better than VLOOKUP for interviews?
XLOOKUP is safer because it defaults to exact match, can look left, and does not break when columns are inserted. Mention it if the role uses Microsoft 365, but also know VLOOKUP with FALSE and INDEX-MATCH, because many offices still run older Excel versions.
When should I use Power Query instead of formulas?
Use Power Query when the same cleaning steps repeat on a new file every day or month, such as removing header rows, unpivoting months or merging two exports. The steps are recorded once and replayed on Refresh, which removes manual copy-paste errors.
Pankit Kumar has 10 years in Data Science & AI, building and shipping production systems in regulated pharma and clinical environments. He is a freelance trainer at Boston Institute of Analytics, AnalytixLabs and Scaler, and has taught this material to thousands of working professionals.
This article is part of our Data Analytics with Generative AI programme — 3–4 months. The full analyst stack — Excel, SQL, Power BI and Python pipelines — then a generative-AI layer you can prove is right.
Explore Data Analytics with Generative AI