VLOOKUP and XLOOKUP both find information in a table and return a matching value. The main difference is that VLOOKUP can only search the first column of a range and return a column to its right, while XLOOKUP lets you point to the search column and the answer column separately, so it can look in either direction.
XLOOKUP is easier to write and harder to break. VLOOKUP is still worth knowing because it appears in an enormous number of existing business spreadsheets, and because XLOOKUP is only available in Excel 2021, Microsoft 365 and Excel for the web.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Search direction | Right only | Left or right |
| How you specify the answer | A column number you count yourself | A range you select |
| Default match type | Approximate, unless you add FALSE | Exact |
| Survives a new column being inserted | No | Yes |
| Handles a value not being found | Needs IFERROR | Built in |
| Wildcard matching | Limited | Yes, as a setting |
| Search from the bottom up | No | Yes |
| Works horizontally as well | No, you need HLOOKUP | Yes |
| Excel versions | All | 2021, Microsoft 365 and the web |
Both functions are covered with practical exercises in our Advanced Excel Course and Excel Diploma.
What does VLOOKUP do?
VLOOKUP stands for Vertical Lookup. It searches down the first column of a range for a value and returns information from another column in the same row.
| Product Code | Product | Price |
|---|---|---|
| P101 | Desk Chair | £125 |
| P102 | Office Desk | £275 |
| P103 | Filing Cabinet | £190 |
| P104 | Desk Lamp | £45 |
If you have the product code P103 and want the price:
=VLOOKUP("P103",A2:C5,3,FALSE)Excel searches the first column for P103 and returns the value from the third column. The result is £190.
The three things to notice are that the code has to sit in the first column of the range, that you have to count across to determine that the price is in column 3, and that FALSE at the end forces an exact match.
What does XLOOKUP do?
XLOOKUP does the same job but asks you for ranges rather than a column number.
=XLOOKUP("P103",A2:A5,C2:C5)The result is again £190, and the formula reads as an instruction: find P103 in A2:A5 and return the matching value from C2:C5. There is no counting and no need to include the columns in between.
The four differences that matter in practice
1. XLOOKUP can look to the left
| Employee | Department | Employee ID |
|---|---|---|
| Sarah Jones | Finance | E104 |
| James Patel | Sales | E105 |
| Lucy Brown | Marketing | E106 |
Suppose you know the Employee ID E105 and want the name. VLOOKUP cannot do this directly because the ID is to the right of the name, and VLOOKUP only ever returns columns to the right of the search. The usual workarounds are to rearrange your data or to use INDEX and MATCH instead.
With XLOOKUP:
=XLOOKUP("E105",C2:C4,A2:A4)The result is James Patel.
2. VLOOKUP breaks when someone inserts a column
The number 3 in a VLOOKUP is fixed. If a colleague inserts a column into the middle of the table, the price is now in column 4 but the formula still returns column 3, so it quietly returns the wrong answer rather than an error. This is one of the most common causes of a spreadsheet being wrong without anybody noticing.
XLOOKUP references a range rather than a number, so the reference moves with the data.
3. VLOOKUP guesses if you forget the last argument
If you leave the fourth argument off a VLOOKUP, Excel assumes you want an approximate match and expects your data to be sorted. On unsorted data, that produces confident, wrong results.
XLOOKUP defaults to an exact match, which is what almost everybody wants almost all of the time.
4. XLOOKUP handles missing values on its own
=XLOOKUP("P109",A2:A5,C2:C5,"Product not found")If P109 is not on the list, Excel displays “Product not found” rather than an error. The equivalent in VLOOKUP is to wrap the whole formula in IFERROR, which also hides genuine errors you might want to see.
XLOOKUP has two further settings beyond that fourth argument. One lets you match wildcards, so you can search for part of a text entry. The other lets you search from the bottom of the list upwards, which is how you find the most recent entry when a value appears more than once.
Why is XLOOKUP not available in my Excel?
This is the most common reason people cannot use it. XLOOKUP was introduced in Microsoft 365 and is available in Excel 2021 and later, as well as in Excel for the web. It does not exist in Excel 2019, 2016 or any earlier version.
If you open a file containing XLOOKUP in an older version of Excel, the formula appears as _xlfn.XLOOKUP and returns an error. That matters if you share workbooks with clients, suppliers or colleagues who may not be on a current version. If in doubt, VLOOKUP is the safer choice for a file that will travel.
Why is VLOOKUP still worth learning?
Because it is everywhere. You will inherit workbooks from colleagues, start a new job or receive a client file built on VLOOKUP formulas. Being able to read one, work out what it is doing and fix it when it breaks is a practical skill regardless of which function you write yourself.
Some employers also still test on it at interview, particularly in finance and administrative recruitment.
Should I learn VLOOKUP or XLOOKUP?
Learn both, and learn why lookups exist rather than memorising the syntax. Use XLOOKUP in new spreadsheets where your version supports it. Use VLOOKUP when a file needs to work on older versions of Excel, and know it well enough to maintain what already exists.
Once the principle makes sense, the same technique covers a great deal of everyday work:
- Finding a price from a product code
- Matching customer numbers with customer details
- Pulling employee information from an employee ID
- Retrieving values from large data sets
- Matching information held across different worksheets
- Filling in reports and templates automatically
That last point is where lookups stop being a formula and start being a way of building spreadsheets that update themselves.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
For new spreadsheets in a current version of Excel, yes. It is easier to write, defaults to an exact match and does not break when columns move.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the search column. Use XLOOKUP, or INDEX and MATCH in older versions.
Which versions of Excel have XLOOKUP?
Excel 2021, Microsoft 365 and Excel for the web. It is not in Excel 2019 or earlier.
Will XLOOKUP replace VLOOKUP?
Not for a long time. Microsoft keeps older functions working, and millions of existing workbooks depend on VLOOKUP.
Is XLOOKUP faster than VLOOKUP?
For large data sets, it is generally more efficient because it does not have to hold the entire table during the calculation.
What about INDEX and MATCH?
It is the traditional way to look left in older versions of Excel. If you have XLOOKUP, you rarely need it, but you will still meet it in existing spreadsheets.
Learn lookup functions properly
Lookup functions are part of the advanced material on our Advanced Excel Course, which suits people who already have a solid working knowledge of Excel and want to go further. If you want to build up across several levels rather than jump straight in, the Excel Diploma covers the ground from the foundations upwards.
Either way, the useful part is not knowing the function’s name. It is recognising the moment at work when a lookup would save you an afternoon.
