What is the difference between VLOOKUP and XLOOKUP, and which do you prefer?
What the interviewer wants to hear
- VLOOKUP searches only the first column and returns to the right
- VLOOKUP defaults to approximate match and uses a column number
- XLOOKUP looks in any direction and defaults to exact match
- XLOOKUP has built-in not-found handling
- Mentions INDEX/MATCH for older Excel versions
Model answer
VLOOKUP searches the first column of a range and returns a value from a column to the right, picked by a column number. Two things make it risky: the last argument defaults to approximate match, which can return wrong values on unsorted data, and the hard-coded column number breaks if someone inserts a column. XLOOKUP takes a separate lookup range and return range, so it can look left or right, it does exact match by default, and it has an if-not-found argument so I can show "Missing" instead of #N/A. It can also search from the bottom to get the latest match. I use XLOOKUP whenever the file will be opened in modern Excel or Google Sheets. If colleagues use an old version, I use INDEX with MATCH, which has the same flexibility.
Can you answer it under time pressure?
Take a timed easy round and get scored on every answer.
More easy Data Analyst questions
- Explain the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN with a simple example.
- You receive a new dataset from a client. What steps do you take to clean it before analysis?
- When would you use the median instead of the mean? Give a business example.
- How do you decide which chart to use for a piece of data?
- How would you explain a data finding to a manager who is not technical?
- Walk me through one analysis you did from start to finish.
- How do you check that the numbers from your SQL query are correct before you share them?
- What is the difference between a metric and a KPI? Give an example.
- You find duplicate customer records in a dataset. How do you handle them?