Excel Interview Questions for Data Analysts

Excel interview questions for analysts are seldom "what does VLOOKUP do?". They are "why did this formula give the wrong answer?", because that is the problem analysts face every week. Each answer below comes from formulas calculated by Microsoft Excel itself, driven from Python so the results can be shown exactly, on a real table: New York's 265 taxi zones and the 20 busiest pickup zones of January 2024 (NYC TLC: trip record data). The zone table is deliberately unsorted, as data usually is when it arrives.

import pandas as pd
import win32com.client

zones = pd.read_csv("taxi_zone_lookup.csv").sample(frac=1, random_state=0)       # 265 NYC taxi zones, unsorted
trips = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["PULocationID"])
top = trips.PULocationID.value_counts().head(20)                                   # the 20 busiest pickup zones

excel = win32com.client.DispatchEx("Excel.Application")        # a separate, invisible Excel instance
excel.Visible, excel.DisplayAlerts = False, False
book = excel.Workbooks.Add()
z, t = book.Worksheets(1), book.Worksheets.Add()
z.Name, t.Name = "zones", "trips"
z.Range(f"A1:C{len(zones) + 1}").Value = [("LocationID", "Borough", "Zone")] + [tuple(r) for r in zones[["LocationID", "Borough", "Zone"]].itertuples(index=False)]
t.Range(f"A1:B{len(top) + 1}").Value = [("LocationID", "pickups")] + [(int(k), int(v)) for k, v in top.items()]
n = len(top) + 1


def column(col, formula):                                       # write a formula down a column, let Excel calculate it
    t.Range(f"{col}2:{col}{n}").Formula = formula
    return [row[0] for row in t.Range(f"{col}2:{col}{n}").Value]


exact = column("C", "=VLOOKUP(A2,zones!$A$2:$C$266,3,FALSE)")
approx = column("D", "=VLOOKUP(A2,zones!$A$2:$C$266,3,TRUE)")
print(f"E1 VLOOKUP on an unsorted table: approximate match disagrees with exact match for {sum(a != b for a, b in zip(exact, approx))} of 20 zones")
print(f"   e.g. zone {int(t.Range('A2').Value)}: exact '{exact[0]}' | approximate '{approx[0]}'")

t.Range("F1").Formula = '=XLOOKUP("JFK Airport",zones!$C$2:$C$266,zones!$A$2:$A$266)'
t.Range("F2").Formula = '=XLOOKUP("Atlantis",zones!$C$2:$C$266,zones!$A$2:$A$266,"not found")'
print(f"\nE2 XLOOKUP to the left of the key column: JFK Airport -> {int(t.Range('F1').Value)} | missing zone -> {t.Range('F2').Value}")

t.Range("G1").Value = "'132"                                    # an ID typed or imported as text
t.Range("G2").Formula = "=VLOOKUP(G1,zones!$A$2:$C$266,3,FALSE)"
t.Range("G3").Formula = "=VLOOKUP(VALUE(G1),zones!$A$2:$C$266,3,FALSE)"
print(f"\nE3 an ID stored as text: lookup returns {'#N/A' if t.Range('G2').Text == '#N/A' else t.Range('G2').Value}"
      f" | after VALUE(): {t.Range('G3').Value}")

borough = column("E", "=XLOOKUP(A2,zones!$A$2:$A$266,zones!$B$2:$B$266)")
t.Range("H1").Formula = f'=SUMIFS(B2:B{n},E2:E{n},"Manhattan")'
check = sum(p for p, b in zip(top.values, borough) if b == "Manhattan")
print(f"\nE4 SUMIFS: Manhattan pickups among the 20 busiest zones {int(t.Range('H1').Value):,} (pandas check {check:,})")

t.Range("H2").Formula = '=SUMPRODUCT(--(COUNTIF(zones!$C$2:$C$266,zones!$C$2:$C$266)>1))'
print(f"E5 rows whose zone name appears more than once: {int(t.Range('H2').Value)}")

t.Range("A30").Value, t.Range("B30").Value = 999, 1000           # a zone ID missing from the lookup
t.Range("C30").Formula = "=IFERROR(VLOOKUP(A30,zones!$A$2:$C$266,3,FALSE),\"\")"
t.Range("D30").Formula = "=ISNA(VLOOKUP(A30,zones!$A$2:$C$266,3,FALSE))"
print(f"\nE6 IFERROR hides a missing key: shows '{t.Range('C30').Value}' | ISNA says missing: {t.Range('D30').Value}")

t.Range("H3").Formula = '=DATEVALUE("1/12/2010")'
print(f"\nE7 DATEVALUE(\"1/12/2010\") on this machine's regional settings: {pd.Timestamp('1899-12-30') + pd.Timedelta(days=t.Range('H3').Value):%d %B %Y}")
book.Close(False)
excel.Quit()

E1 VLOOKUP on an unsorted table: approximate match disagrees with exact match for 18 of 20 zones
   e.g. zone 132: exact 'JFK Airport' | approximate 'Far Rockaway'

E2 XLOOKUP to the left of the key column: JFK Airport -> 132 | missing zone -> not found

E3 an ID stored as text: lookup returns #N/A | after VALUE(): JFK Airport

E4 SUMIFS: Manhattan pickups among the 20 busiest zones 1,668,917 (pandas check 1,668,917)
E5 rows whose zone name appears more than once: 5

E6 IFERROR hides a missing key: shows '' | ISNA says missing: True

E7 DATEVALUE("1/12/2010") on this machine's regional settings: 01 December 2010

E1. What is the difference between VLOOKUP with TRUE and FALSE?

FALSE finds an exact match. TRUE, which is also the default if you leave the argument out, assumes the first column is sorted and returns the closest match (Microsoft: VLOOKUP). On the unsorted zone table, the approximate version disagreed with the exact one for 18 of the 20 zones, and returned "Far Rockaway" for zone 132, which is JFK Airport. Excel gave no error, just wrong names. Always pass FALSE for lookups by ID, or use XLOOKUP, which defaults to an exact match.

E2. Why do interviewers prefer XLOOKUP?

It looks in any direction, matches exactly by default, and has a built-in "not found" value (Microsoft: XLOOKUP). Finding the ID of "JFK Airport", where the ID column sits to the left of the name, returned 132; VLOOKUP cannot look to the left. Looking up a zone that does not exist returned "not found" instead of #N/A. Mention that XLOOKUP is not available in older Excel versions, so shared workbooks may still need INDEX and MATCH.

E3. Why does a lookup return #N/A when the value is clearly there?

Often because one side is text and the other is a number. The ID 132 typed or imported as text returned #N/A; wrapping it in VALUE() found JFK Airport. Imports from CSV files and systems that pad IDs are the usual cause. Check with ISTEXT, and convert one side consistently, rather than retyping values.

E4. How do you sum with conditions?

SUMIFS, with the sum range first and then pairs of criteria ranges and criteria. Looking up each zone's borough with XLOOKUP and summing with SUMIFS(..., "Manhattan") gave 1,668,917 pickups among the 20 busiest zones, matching an independent pandas calculation exactly. Cross-checking a spreadsheet total against a second method is a habit worth describing in the interview.

E5. How do you find duplicates?

COUNTIF of each value against the whole column: anything above 1 is a duplicate. Five rows in the zone table share a name with another row: Corona appears twice and the island zone three times, under different IDs. That matters because a lookup by name returns only the first match, silently.

E6. Is IFERROR a good habit?

Only when you have decided what an error means. IFERROR replaces every error, #N/A but also #REF! and #DIV/0!, with your value (Microsoft: IFERROR). A zone ID missing from the lookup showed as a blank, indistinguishable from a real blank, while ISNA confirmed it was missing. Wrapped around sums, the same habit turns missing data into zeros and quietly lowers totals. Prefer IFNA for lookups, and count the misses.

E7. Why does the same date formula give different answers on different computers?

Because text dates are interpreted using the computer's regional settings (Microsoft: DATEVALUE). On this machine, set to day-first dates, DATEVALUE("1/12/2010") returned 1 December 2010; with US settings, the same text means 12 January 2010. For shared workbooks, store real dates, or build them with DATE(year, month, day) from separate parts.

E8. When would you use a pivot table instead of formulas?

When the question is exploratory or the grouping may change: a pivot table summarises thousands of rows by any combination of fields in seconds, and can be rearranged during the conversation. Formulas such as SUMIFS suit a fixed report layout that must update cell by cell. Interviewers often ask you to build one live, so practise grouping dates by month, showing values as a percentage of the total, and refreshing after the source data changes.

E9. What is INDEX MATCH, and is it still needed?

INDEX returns the value at a position; MATCH finds the position of a value. Together, INDEX(return_column, MATCH(key, key_column, 0)) does what XLOOKUP does: an exact match in any direction. It is still worth knowing because many workbooks predate XLOOKUP and must open in older versions of Excel. The 0 in MATCH means exact match, and forgetting it causes the same silent errors as VLOOKUP with TRUE.

How to answer Excel questions

• Say which match type you use, and why.
• Check types before blaming the data.
• Cross-check totals with a second method.
• Handle errors deliberately, not wholesale.

The formulas themselves are taught in Excel for data analysis: the formulas that matter and summaries in pivot tables in Excel; cleaning imports is covered in Excel Power Query. For the full analyst round, see data analyst interview questions and answers.

Excel for analysis is part of the Data Analytics course.

Sources

• VLOOKUP function (Microsoft Support) — Microsoft
• XLOOKUP function (Microsoft Support) — Microsoft
• IFERROR function (Microsoft Support) — Microsoft
• DATEVALUE function (Microsoft Support) — Microsoft
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission