Model answers for 4 Excel questions
Typical round: verbal questions, then a 20-30 minute task on a sample file
Q: VLOOKUP vs XLOOKUP? A: VLOOKUP searches the first column of a range and returns a column to the right by index, so it breaks if columns move and can't look left. XLOOKUP takes separate lookup and return ranges, can look left, defaults to exact match and handles not-found values cleanly.
Q: How would you total sales for North region in March? A: =SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Month], "Mar"). SUMIFS takes the sum range first, then pairs of criteria ranges and criteria.
Q: How do you remove duplicates while keeping a record? A: Copy the data first, then use Data > Remove Duplicates on the key columns, or use =UNIQUE() in newer Excel to create a deduplicated list without touching the source.
Q: Why use a pivot table? A: To summarise large data quickly by dragging fields into rows, columns, values and filters, for example sales by region and month, and to drill into details without writing formulas.