VLOOKUP vs INDEX+MATCH vs XLOOKUP

A lookup is a join. That one sentence fixes most of the confusion in this topic, because it tells you what question to ask first — not "which function do I use" but "what happens to rows that do not match?"

In SQL you answer that explicitly, and the choice is the whole point:

select o.order_id, c.region
from orders o
left join cities c on c.city = o.city;

An INNER JOIN drops non-matching rows. A LEFT JOIN keeps them with nulls. Excel only has one behaviour — it keeps your row and puts #N/A in the cell — so every Excel lookup is a LEFT JOIN, and #N/A is the null. What you do with those #N/As is where interviews are won and lost.

The three functions

=VLOOKUP(A2, cities!A:C, 3, FALSE)
=INDEX(cities!C:C, MATCH(A2, cities!A:A, 0))
=XLOOKUP(A2, cities!A:A, cities!C:C)

All three do the same thing here: find A2 in the city column, return the region. They differ in how they break.

VLOOKUP's fourth argument is the most expensive default in Excel

VLOOKUP(A2, range, 3) with the fourth argument omitted does an approximate match. It assumes the lookup column is sorted ascending and returns the largest value less than or equal to what you asked for. On unsorted data that is not "a bit fuzzy" — it is effectively arbitrary, and it returns a confident wrong answer for every row rather than an error.

You must write FALSE (or 0) as the fourth argument. Every time.

=VLOOKUP(A2, cities!A:C, 3, FALSE)

There is no scenario in interview data where you want the default. The approximate mode exists for genuine banded lookups — tax brackets, shipping-weight tiers — and in that case you write it deliberately with a sorted band table.

The second problem is 3. It is a positional column index, counted from the start of the range, and nothing binds it to the column you meant. Someone inserts a column in the source sheet and every formula still evaluates, still returns values, and now returns the wrong field. This is why experienced analysts avoid VLOOKUP for anything that will be maintained, and it is a good thing to say in an interview.

INDEX+MATCH reads backwards but breaks less

=INDEX(cities!C:C, MATCH(A2, cities!A:A, 0))

Inside out: MATCH finds which row the key is in, INDEX returns the value at that row of the column you actually named. The column is a real reference, so inserting a column moves it correctly. It also looks in either direction, which VLOOKUP cannot — if the key column sits to the right of the value you want, VLOOKUP simply cannot do the job.

The 0 in MATCH is the same trap as VLOOKUP's FALSE: it means exact match, and omitting it gives you approximate. Same rule — always write it.

XLOOKUP is the one to use if you have it

=XLOOKUP(A2, cities!A:A, cities!C:C, "unmatched")

Exact match by default, real column references, looks in any direction, and the fourth argument is what to return when nothing matches — replacing the IFERROR wrapper entirely. It is only available in Excel 365 and 2021 onward, which is a real constraint: plenty of banks and government-adjacent employers in the region are still on older builds, and interview machines are often locked to whatever the corporate image ships.

Say which you would use and why, then note the constraint. "XLOOKUP if the environment has it, INDEX+MATCH otherwise" is a better interview answer than either one alone.

What to do with #N/A

This is the part that actually gets assessed, and it is a data-reasoning question rather than a formula question. An #N/A means the key was not found, and there are three genuinely different reasons, needing three different responses:

• The key is dirty. Trailing space, different case, "KL" versus "Kuala Lumpur", a number stored as text on one side and as a number on the other. The fix is upstream: TRIM, or reconcile the two vocabularies. Text-versus-number is the sneakiest of these, because the two cells look identical on screen.
• The row genuinely has no match, and zero is the right answer. A customer with no transactions. Here IFERROR(..., 0) is correct and expresses real meaning — the same judgement as coalesce on a LEFT JOIN.
• The row genuinely has no match, and that is a finding. Orders referencing a city missing from the reference table means the reference table is incomplete, and that is the answer to report, not something to paper over.

IFERROR applied before you know which of the three you are looking at is the most expensive habit in spreadsheet work. It converts a visible data problem into an invisible wrong number. Interviewers plant exactly this — a handful of unmatched keys in a 40,000-row file — and the candidate who wraps everything in IFERROR(...,0) and reports a clean total scores worse than the one who says "sixty rows do not match, and they are all one city, so I think the reference table is missing an entry."

A quick way to check before deciding:

=COUNTIF(cities!A:A, A2)

Zero means genuinely absent. If it returns 1 but the lookup still fails, you have a type mismatch rather than a missing key.

One more join Excel is bad at

If the key matches multiple rows in the reference table, all three functions return the first match and silently ignore the rest. In SQL that same situation is a one-to-many join that multiplies your row count — loudly, and usually visibly in the row total.

So Excel will not tell you your reference table has duplicates. Check it yourself before trusting any lookup on unfamiliar data — run a COUNTIF down the reference table against itself and look for anything above 1:

=COUNTIF(cities!A:A, A2)

Filled down the reference table, any cell above 1 is a duplicated key. Duplicate keys in a lookup table are one of the most common planted flaws in take-home exercises, and the same formula doubles as the probe for diagnosing an #N/A above.

Next

Once the tabs are joined, the other half of the question is nearly always aggregation — SUMIFS and COUNTIFS: the GROUP BY of Excel covers the criteria rules and the double-counting trap.

For the SQL side, join questions in the bank are graded in the browser, and the joins chapter covers the LEFT-versus-INNER decision that this whole topic mirrors.