In this article
Lookups and matching
What does VLOOKUP do, and what is its biggest limit?
The VLOOKUP function finds a value in the first column of a range and returns a value from a column to its right in the same row. Microsoft's page says the value you look up must sit in the first column of the range you give it (Microsoft Support, VLOOKUP, accessed October 2026). So it cannot return from a column to the left of the lookup column.
Why it is asked: it is the most common formula test, and the limit shows whether you have used it on real tables. Read the full explanation: Microsoft Support, VLOOKUP.
Why does VLOOKUP sometimes return a wrong value or an error, and how do you stop it?
The last argument of VLOOKUP chooses between an approximate and an exact match, written as true or false (Microsoft Support, VLOOKUP, accessed October 2026). Approximate matching assumes the first column is sorted. In an illustrative five-row part list that is not sorted, a standard binary search of the kind approximate matching relies on found nothing for the key P-10, although an exact search returned its price of 4.0.1 Excel can also return a wrong nearby value in this situation, the failure Microsoft's page lists as "Wrong value returned". Always write the exact-match setting yourself.
Why it is asked: silent wrong answers are the costliest spreadsheet bug. Read the full explanation: Microsoft Support, VLOOKUP.
How do XLOOKUP, VLOOKUP and INDEX with MATCH differ?
Microsoft describes XLOOKUP as looking in one column for a search term and returning a result from the same row in another column, whichever side the return column is on (Microsoft Support, XLOOKUP, accessed October 2026). XLOOKUP looks for an exact match unless you ask for another kind, so it avoids the approximate-match default of VLOOKUP described above (Microsoft Support, XLOOKUP and VLOOKUP, accessed October 2026). INDEX with MATCH also looks in either direction but needs two nested functions, and it works in older workbooks. Check which functions your Excel version offers.
Text description of the diagram
- VLOOKUP needs the lookup value in the first column of its range.
- XLOOKUP can return a result from a column on either side of the lookup column.
- INDEX with MATCH can also return from either side.
Why it is asked: it shows whether your habits are current. Read the full explanation: Microsoft Support, XLOOKUP.
How do you look up a value when the key might not exist?
Wrap the lookup so a missing key gives a result you chose instead of an error. In the same illustrative part list, an exact search for the key P-25 finds no row, so the formula returns an error unless you handle it.1 The IFERROR function replaces any error with a fallback, but it also hides unrelated errors, so a narrower check, such as the not-found argument of XLOOKUP, is safer.
Why it is asked: it tests whether you plan for bad data. Read the full explanation: Microsoft Support, XLOOKUP.
Formulas and references
What is the difference between relative, absolute and mixed references?
A relative reference such as B2 changes when you copy the formula to another cell. An absolute reference is a cell address with dollar signs before both the column letter and the row number, so it stays fixed when copied. A mixed reference such as $B2 locks only the column. On Windows, pressing F4 while editing a reference cycles through the forms. Use absolute references for a tax rate or a lookup table that every row must share.
Why it is asked: copying a formula down a column is the daily task, and this is the usual way it breaks. Read more: data analyst interview questions.
What is the difference between SUMIF, SUMIFS and COUNTIFS?
The SUMIF function adds the cells that meet one condition, SUMIFS adds the cells that meet several conditions at once, and COUNTIFS counts rows that meet several. In an illustrative five-row sales table, SUMIFS for the North region and the Pens item gives a quantity of 14, and COUNTIFS for the North region counts 3 rows.1 These replace helper columns and manual filtering.
Why it is asked: conditional totals are the core of reporting work. Read more: A/B test analysis questions for analysts, where group totals feed a decision.
How do you use IF and handle several conditions without a tangle of nested IFs?
The IF function returns one value when a test is true and another when it is false. For several cases, IFS or a lookup against a small table of thresholds is easier to read and change than many nested IF functions. Combine tests with AND and OR. Keep each formula short enough that a colleague can check it, and move repeated rules into a visible table.
Why it is asked: it tests readability, not only syntax. Read more: data analyst interview questions.
What is a dynamic array, and what does "spill" mean?
A dynamic array formula returns several values from one formula, and the results spill into the neighbouring empty cells instead of needing one formula per cell. Functions such as FILTER, SORT and UNIQUE work this way in current Excel for Microsoft 365. A spill fails if a cell in its path is not empty. Check your version before relying on them, since older workbooks do not have them.
Why it is asked: it separates people who know modern Excel from those who stopped at VLOOKUP. Read more: data analyst interview questions.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Summarising and cleaning data
What is a PivotTable, and when would you use one?
A PivotTable is an Excel tool that summarises a table by grouping its rows and applying a calculation such as a sum or a count, without writing formulas. You drag fields into rows, columns and values. Use one for quick totals by category or period, and refresh it after the source data changes. For a repeatable report with set logic, formulas may be easier to audit.
Why it is asked: it is the standard test of data-analyst Excel skill. Read more: how to answer a metric dropped case, where breaking a number down by group is the first move.
How do you clean messy data in Excel?
Start with a copy of the raw data. Remove stray spaces with TRIM, fix text case, convert text that looks like numbers into real numbers, and use the remove-duplicates command on a clearly defined key. Then check the result: count rows before and after, and scan for blanks. Record each step so someone else can repeat it. For a very large or recurring clean, Power Query or Structured Query Language (SQL) is a better home.
Why it is asked: analysts spend much of their time on this, and a careful method matters more than a trick. Read more: SQL interview questions.
How do you check a spreadsheet for errors before you share it?
Compare totals against an independent figure, such as a subtotal you can compute another way. In the illustrative sales table, revenue is 58.0 for North and 34.0 for South, so the total must be 92.0; a different total points to a broken range.1 Also trace precedents on key cells, look for numbers typed over formulas, and test edge cases such as zero or blank rows.
Why it is asked: a wrong number that looks confident does real damage. Read more: how to investigate a surprising number in a metric dropped case.
Modelling and limits
What are the key Excel functions for financial modelling?
Common ones are NPV for net present value, IRR for internal rate of return and PMT for a loan payment. For an illustrative project costing 1,000 now and returning 400 a year for three years at a 10% discount rate, net present value is about -5.26 and the rate that makes it zero is about 9.7%.1 A loan of 10,000 at 6% a year over 36 months has a monthly payment of about 304.22.1
Why it is asked: finance-flavoured roles use these to test modelling basics. Read more: data analyst interview questions.
What mistake do people make with NPV?
The NPV function discounts every value it receives by at least one period, so a cost paid today should be added outside the function rather than passed in. In the illustrative project, passing all four values gives -4.78 instead of the correct -5.26 for a cost paid today.1 Confirm the timing rule in the function's documentation for your version, and state your timing assumption aloud.
Why it is asked: it reveals whether you understand the maths behind the function, not only the button. Read more: data analyst interview questions.
How many rows can Excel hold, and what do you do when data is bigger?
Microsoft's specifications page lists a maximum worksheet size of 1,048,576 rows by 16,384 columns (Microsoft Support, specifications and limits, accessed October 2026). Past that, or when a sheet turns slow, load the data into a database and query it, or use Power Query to import and shape it.
Why it is asked: it tests whether you know when to stop using a spreadsheet. Read the full explanation: Microsoft Support, specifications and limits.
When would you use Excel and when SQL?
Use Excel for small data, quick exploration, one-off analysis and work a business user must edit. Use SQL when data lives in a database, is large, or the query must be repeated and reviewed. Many analysts do both: query in SQL, then summarise or present in Excel. Say which you would pick and why for the task given.
Why it is asked: it checks judgment about the tool, not loyalty to one. Read the full explanation: SQL interview questions.
What is a macro, and when would you use VBA?
A macro is a recorded or written set of steps that Excel replays on demand, so a repeated task runs with one command. Visual Basic for Applications (VBA) is the programming language behind macros. Record a macro for a short, fixed routine such as formatting a weekly report; write VBA when you need loops, conditions or input checks. Open macros only from sources you trust, since they can run harmful code.
Why it is asked: it separates people who automate repeated work from those who redo it by hand. Read more: data analyst interview questions.
Follow-up questions
What data analyst interview questions are asked on Excel?
Expect lookups, conditional sums, PivotTables, data cleaning and a question about checking your own work. The entries above cover each, and the data analyst guide places Excel next to SQL and dashboards. Questions about charting tools are covered in Tableau developer questions.
What does an Excel formula engine coding interview test?
An Excel formula engine coding interview asks you to build a small spreadsheet in code: cells hold numbers or formulas such as =A1+B2, and changing one cell must update every cell that depends on it. A strong answer stores a dependency graph, parses each formula into the cells it reads, recomputes dependents in topological order and rejects a circular reference by detecting a cycle. Mention the cost of recomputing on each edit and how you would test it on edge cases.
What are Excel financial modeling interview questions?
They usually test timing and discounting: net present value, internal rate of return, loan payments and clear assumptions. The two modelling entries above cover them. For cash flows on irregular dates, XNPV and XIRR take the dates as well, unlike NPV and IRR. State every assumption before you compute.
How does Excel fit into a full interview loop?
Excel can be one round among several, alongside SQL and behavioural rounds. See how rounds fit together in the interview loop guide.
VLOOKUPneeds the lookup column first and an explicit exact-match setting;XLOOKUPreturns from either side.- Lock references that must not move when you copy a formula, and use
SUMIFSfor conditional totals. - A PivotTable summarises a table without formulas; clean on a copy and check totals afterwards.
- Add a cost paid today outside
NPV, and state your timing assumption. - Know the worksheet limit and when SQL or Power Query is the better tool.
Sources
- XLOOKUP function (Microsoft Support) — Microsoft Support, 26 Oct 2026
- VLOOKUP function (Microsoft Support) — Microsoft Support, 26 Oct 2026
- Excel specifications and limits (Microsoft Support) — Microsoft Support, 26 Oct 2026
