Excel Tutorials
Two lookup functions that solve the same basic problem in very different ways, with real examples showing exactly where they agree, where they diverge, and why XLOOKUP is worth switching to. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Both functions look up a value in one place and pull back a related value from somewhere else, the same basic job as manually scanning a list to find a match and copying what's next to it. VLOOKUP has been Excel's standard lookup function for decades: it searches the first column of a range and returns a value by counting columns over from there. XLOOKUP is the newer, more flexible replacement, letting you point directly at the lookup column and the return column as two separate ranges, with no counting involved.
They solve the same problem, but the differences in how they do it matter a lot in practice, which is what the rest of this page walks through with real examples.
Step-by-step, matching the video above.
VLOOKUP needs the value you're searching for to sit in the first column of the range you point it at.
=VLOOKUP(lookup_value, table_range, column_number, FALSE). The column_number counts from the first column of table_range, starting at 1.
Leaving it off defaults to an approximate match, which silently returns the wrong result on unsorted data. FALSE forces an exact match only.
=IFERROR(VLOOKUP(...),"Not Found") replaces the default #N/A error with whatever message you want shown instead.
| A: Product | B: SKU | C: Price |
|---|---|---|
| Widget | W100 | 12.50 |
| Gadget | G200 | 24.00 |
| Gizmo | Z300 | 8.75 |
=VLOOKUP("G200",B2:C4,2,FALSE) searches column B for "G200", then counts 2 columns over (into C) and returns 24.00. Notice the lookup value (SKU) has to be the first column of the range you're searching, VLOOKUP can't be pointed at column A to search by SKU here since SKU sits in column B.
Same basic job, but the lookup and return ranges are specified separately, no column counting required.
=XLOOKUP(lookup_value, lookup_range, return_range). Three core arguments, the lookup range and return range are each their own separate reference.
XLOOKUP defaults to an exact match automatically, there's no equivalent of VLOOKUP's easy-to-forget FALSE.
A fourth, optional argument handles a missing match right inside the formula: =XLOOKUP(lookup_value,lookup_range,return_range,"Not Found"), no IFERROR wrapper required.
| Formula | Result |
|---|---|
| =XLOOKUP("G200",B2:B4,C2:C4) | 24.00 |
| =XLOOKUP("G200",B2:B4,A2:A4) | Gadget |
| =XLOOKUP("X999",B2:B4,C2:C4,"Not Found") | Not Found |
Using the same table as above: the second formula searches column B but returns from column A, to its left, something VLOOKUP genuinely cannot do without rearranging your columns or switching to INDEX/MATCH. The third shows a missing match falling back to "Not Found" with no IFERROR needed.
The syntax looks similar enough to seem interchangeable, these are the differences that actually matter.
VLOOKUP only searches its lookup value in the first column of a range and returns values to the right. XLOOKUP can return a value from any column, left or right of the lookup column.
VLOOKUP requires manually counting which numbered column to return from. XLOOKUP just points directly at the return range, nothing to count or miscount.
VLOOKUP defaults to an approximate match unless you add FALSE. XLOOKUP defaults to an exact match automatically, a safer default with less room for a silent wrong answer.
VLOOKUP returns a bare #N/A on no match, needing IFERROR wrapped around it. XLOOKUP has a built-in fallback argument right inside the formula.
VLOOKUP's column_number argument can silently point at the wrong column if someone inserts a new column into the range later. XLOOKUP references the actual return range, so it isn't affected by columns being added or reordered.
Beyond the technical differences, here's what actually improves day to day once you make the switch.
Since XLOOKUP doesn't depend on column position, reorganizing a spreadsheet's columns won't quietly break lookups the way it can with VLOOKUP.
Built-in error handling means fewer formulas wrapped in IFERROR just to avoid an ugly #N/A showing up.
Before XLOOKUP, looking up a value to the left of your lookup column meant switching to INDEX/MATCH entirely. XLOOKUP handles both directions on its own.
Exact match by default means a typo or unsorted list is far less likely to return a confidently wrong number without any warning.
Without it, VLOOKUP defaults to an approximate match, which can return a confidently wrong value on data that isn't sorted exactly the way VLOOKUP expects.
It can't. The lookup value always has to be in the first column of the range, if what you're searching by sits to the right of what you want returned, VLOOKUP won't work without rearranging columns.
A new column shifts what used to be column 3 into column 4, but VLOOKUP's column_number argument doesn't update itself. Always recheck VLOOKUP formulas after inserting columns into their range.
XLOOKUP requires Excel 2021, Microsoft 365, or Excel for the web. A file shared with someone on an older version will show a #NAME? error instead.
A lookup formula shouldn't be the only thing holding your data together.
Free 14-day trial. No credit card required.
VLOOKUP only searches the first column of a range and returns a value by counting columns to the right. XLOOKUP lets you point at the exact lookup column and return column separately, in any direction.
Yes. XLOOKUP can return a value from any column relative to the lookup column, including columns to its left, something VLOOKUP cannot do on its own.
Not directly. VLOOKUP always searches its lookup value in the first column of the range and only returns values from columns to the right of it.
No. XLOOKUP defaults to an exact match, unlike VLOOKUP, which defaults to an approximate match unless you explicitly add FALSE as the fourth argument.
It returns a #N/A error, which needs to be wrapped in IFERROR to display something more useful instead.
You can specify a fallback directly inside the formula's fourth argument, like XLOOKUP(A2,B:B,C:C,"Not Found"), no wrapping required.
Yes, if the new column shifts what used to be the Nth column over. VLOOKUP counts columns by number, so that number can silently become wrong.
No. XLOOKUP references the actual return range, not a column count, so it keeps working correctly even if columns are inserted or reordered.
No. XLOOKUP requires Excel 2021, Microsoft 365, or Excel for the web. VLOOKUP works in every version, which matters if a file needs to open on an older install.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →