Beginner’s Guide to XLOOKUP vs VLOOKUP in Excel

If you’ve worked with Excel for anything more than basic tasks, you’ve probably seen VLOOKUP in action. Maybe you’ve used it yourself, or copied someone else’s formula and hoped it worked.

Then at some point, someone mentions XLOOKUP and says it’s better, but never really explains why….

So let’s clear that up! In this guide, I’ll show you how these two functions compare, where they differ, and why XLOOKUP is often the better choice.

Sidenote: If you want to take your Excel skills to the next level, check out my Complete Excel course.

You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!

All online, and updated every year.

With that out of the way, let’s get into this 5-minute tutorial

What are lookups and why do they matter?

Before we dive into which one you should use, let’s start with what these functions actually do.

Both VLOOKUP and XLOOKUP are made for a single task - finding information in a table.

For example

Imagine you have a list of product codes in one column of a table, and product prices in another column. If you want an Excel formula to return the associated price for a specific product code, that’s a lookup. You’re telling Excel,

“Find this thing, then give me the matching value.”

These functions do the searching for you. So instead of scanning through a list by hand, Excel finds the match instantly based on what you tell it to look for. It could be a product ID, a customer name, or anything else. You give Excel one value, and it returns the one that goes with it.

So why should you care about lookups?

Because they help make your spreadsheets dynamic. If something changes — like a product price or a name — your formulas update automatically. That means fewer mistakes, less manual editing, and a spreadsheet that stays accurate even as your data evolves.

TL;DR

Both VLOOKUP and XLOOKUP do the same job. The difference is how they do it, and how much control they give you. That’s where things start to get interesting.

How VLOOKUP works and where it falls short

You’ll still find VLOOKUP in spreadsheets all over the world. It’s been part of Excel for decades, and if you’ve ever used a shared file at work, there’s a good chance you’ve seen it in action.

The name stands for “vertical lookup,” and that’s exactly what it does: it searches down the first column of a table to find a match, then returns a value from another column in the same row.

For example

Let’s say you have a table with product IDs in column A and prices in column B. If you want to look up the price for product ID 123 in that table, you’d write:

=VLOOKUP(123, A:B, 2, FALSE)

This tells Excel to look for 123 in column A, go across to the second column of the range, and return the value it finds.

It works, but it’s not perfect.

The first issue is that the column number is hard-coded. If you add or remove columns later, the formula might break or pull the wrong value
Not only that but VLOOKUP only searches from left to right. If your return value is in a column to the left of what you’re searching, it won’t work
And finally, if you forget to include FALSE at the end, Excel will try to return an approximate match instead of an exact one. That can lead to subtle, frustrating errors

Because of these quirks, VLOOKUP needs more care than you might expect, but it’s still widely used. Especially in older files or in companies using older versions of Excel that don’t support XLOOKUP.

So while it’s no longer the best tool in most cases, it’s still worth knowing how it works. You’ll come across it often, and in some situations, it’s the only option that will work with the version of Excel you're using.

Why XLOOKUP is a smarter way to find data

If you’ve ever felt like VLOOKUP is a little clunky, you’re not alone. That’s exactly why Microsoft created XLOOKUP. It does the same job, but in a cleaner, more flexible way.

At its core, XLOOKUP is still about matching a value and returning related data. But XLOOKUP formulas are easier to read, easier to write, and a lot less fragile.

For example

If we use the same example as before, where we want to look up the price for product ID 123 (with product IDs in column A, and prices in column B).

With VLOOKUP, you had to use:

=VLOOKUP(123, A:B, 2, FALSE)

But with XLOOKUP, that becomes:

=XLOOKUP(123, A:A, B:B)

Instead of counting columns, you tell Excel exactly where to look and exactly where to return the value from. A:A is your lookup range. B:B is your return range.

That’s it. There’s no need to worry about moving columns around, OR fetching values from the wrong column because you miscounted by one or two (which, believe me, is surprisingly easy to do!).

You can also search from right to left, something VLOOKUP can’t do. And by default, XLOOKUP looks for an exact match, so you don’t need that extra TRUE/FALSE argument at the end of the formula.

Oh, and it also has built-in error handling!

For example:

=XLOOKUP(123, A:A, B:B, "Not found")

If Excel doesn’t find a match, it returns the message “Not found” instead of showing an error like #N/A.

So compared to VLOOKUP, XLOOKUP is more flexible, easier to manage, and far more reliable. Once you start using it, you’ll probably wonder why it wasn’t always like this.

Common mistakes that trip people up (and how to avoid them)

Even though XLOOKUP is easier to use than VLOOKUP, there are still a few things you’ll want to watch out for. Most of these are small mistakes, but they can cause big headaches if you don’t catch them early.

Forgetting to set exact match in VLOOKUP

By default, VLOOKUP looks for the closest match, not an exact one. That means if it can’t find the exact value, it might just return whatever’s nearby. This can be a big problem when you’re working with things like product codes or IDs, where close enough isn’t good enough.

To avoid this, always add FALSE as the fourth argument when using VLOOKUP:

=VLOOKUP("123", A:B, 2, FALSE)

This tells Excel to only return a result if it finds an exact match.

Using XLOOKUP in an older version of Excel

If you’re using Excel 2016 or earlier, XLOOKUP won’t work. It was introduced in Excel 365 and Excel 2019, so older versions won’t recognize the function at all.

If that’s the case, you’ll either need to upgrade or use a workaround like INDEX and MATCH.

Mixing up your ranges

With VLOOKUP, the column you’re searching in has to be the first column in the range. For example, if you use A:C as your lookup range - when the values you’re trying to match against the lookup value are in column B - your formula won’t return anything.

XLOOKUP gives you more flexibility, since you define the lookup and return ranges separately. But those ranges still need to line up row by row. If they’re mismatched or uneven, your formula won’t work as expected.

Hardcoding column numbers in VLOOKUP

One of the most fragile parts of a VLOOKUP formula is the column number. If you insert or delete columns in your table, that number doesn’t adjust automatically - meaning you’ll either get the wrong result, or break the formula entirely.

XLOOKUP avoids this by letting you point directly to the column you want. That makes your formulas easier to update and much less likely to break when your spreadsheet changes.

Not checking for basic errors

It sounds simple, but it’s worth saying - always double-check your references!

Are your ranges pointing to the right columns?
Did you spell your lookup value correctly?
Are there any extra spaces or hidden formatting issues?

Taking a moment to check these things can save you a lot of time and frustration later.

So which one should you use?

VLOOKUP shows up all the time in older spreadsheets, tutorials, and workplaces where Excel hasn’t been updated in a while. If someone shares an Excel file with you, there’s a decent chance you’ll find a VLOOKUP somewhere in it, so being able to read and tweak those formulas is definitely worth it.

But for anything new you’re building, XLOOKUP should be your default.

It’s easier to understand, easier to write, and way more flexible. You don’t have to worry about column numbers, match types, or which direction your data is arranged. It just plain works the way you expect it to.

The best way to understand this of course is to give each of them a try. Create a small table — maybe with product names and prices — and try both formulas. First, set up a lookup using VLOOKUP. Then try it again with XLOOKUP. Make a few changes to your table and watch how each formula behaves.

That kind of hands-on practice is the best way to build intuition around how these functions work, and how to use them.

P.S.

Remember, if you want to take your Excel skills to the next level, and even open up new career options, then check out my Complete Excel course.

You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!

Plus, once you join, you'll have the opportunity to ask questions in our private Discord community from me, other students, and working Data professionals.