In Excel, what does XLOOKUP do that VLOOKUP cannot?

## The two signatures `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` takes one rectangular range, searches its **leftmost column** for the lookup value, and returns whatever sits in the *n*-th column of that same range, counting rightward from that leftmost column. `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` takes the searched range and the returned range as two independent arguments. Nothing ties them together except that they should be the same length. That one structural difference produces most of the practical ones. ## Direction, and the fragile column index Because VLOOKUP counts columns rightward from the key, it cannot return anything to the left of the key. The traditional workarounds are rearranging the source columns (often impossible — it is someone else's export) or `INDEX`/`MATCH`. Worse, `col_index_num` is a **position, not a reference**. `=VLOOKUP(A2, Products!B:F, 3, FALSE)` means "the third column of B:F", i.e. column D. Insert a new column between B and D and the formula still says 3 — it now returns the inserted column. Excel does not flag this: the formula is valid, the result is a plausible-looking value, and the error surfaces weeks later as a wrong number. XLOOKUP has no index; its return range is an ordinary reference that Excel adjusts when columns move. ## Match defaults Omitting VLOOKUP's fourth argument gives **approximate match**, which assumes the first column is sorted ascending and returns the largest value less than or equal to the lookup value. On unsorted text this returns essentially arbitrary matches rather than an error, which is why `FALSE` (or `0`) is nearly always the right fourth argument and why omitting it is a classic silent bug. XLOOKUP's `match_mode` defaults to `0`, exact match. The other modes are explicit: `-1` exact or next smaller, `1` exact or next larger, `2` wildcard. `search_mode` defaults to `1` (first to last); `-1` searches last to first, which is the clean way to grab the most recent record in an append-only sheet; `2` and `-2` request a binary search on data you promise is sorted. ## Not-found handling A VLOOKUP miss returns `#N/A`, and the usual response is `IFERROR(VLOOKUP(...), "")`. That works, but `IFERROR` swallows **every** error: a `#REF!` from a deleted column and a `#VALUE!` from a bad argument disappear along with the legitimate misses, so a broken formula looks like a clean blank. XLOOKUP's fourth argument, `if_not_found`, fires only for the not-found case and leaves real errors visible. ## Returning more than one column `return_array` may be several columns wide. `=XLOOKUP(A2, Products[SKU], Products[[Name]:[Price]])` returns the whole slice and spills it across adjacent cells, replacing three separately maintained VLOOKUPs that each had their own column index to get wrong. ## Availability and fallbacks XLOOKUP requires Microsoft 365 or Excel 2021 and later (and Excel for the web). If a workbook must open in Excel 2016 or 2019, it will show `#NAME?`. The portable equivalent is `INDEX`/`MATCH`: ``` =INDEX(Products!$A:$A, MATCH(F2, Products!$C:$C, 0)) ``` `MATCH` returns a row position, `INDEX` fetches from any column using it, so this also looks leftward. Note that `MATCH`'s third argument has the same trap as VLOOKUP's fourth — omit it and you get approximate matching — so always pass `0`. ## What interviewers are checking The question is rarely about memorising an argument list. It is whether you know **which lookup failures are silent**: approximate match on unsorted data, a column index invalidated by an insert, and errors hidden by a blanket `IFERROR`. A candidate who names those three has demonstrated they have debugged a wrong spreadsheet number, which is the actual skill on offer.