What These Functions Do

Both XLOOKUP and VLOOKUP help you search for a piece of data in a table and get back some information related to it. Think of it like looking up a word in the index of a book to find where it’s mentioned.

VLOOKUP Basics

VLOOKUP has been around for a long time. You use it to find data in a table that’s organized vertically. It needs four pieces of information to work:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: What you want to find.
  • table_array: Where to look for it. The lookup_value should be in the first column of this range.
  • col_index_num: This is the number of the column that has the information you want to get.
  • [range_lookup]: Optional: Whether you need an exact match or not. Use 0 (= FALSE) to find an exact match. Use 1 (= TRUE) to find something close if an exact match isn’t there (approximate match). If you don’t choose, Excel will look for an approximate match by default.

To learn more about VLOOKUP, read this article HERE.

XLOOKUP Basics

XLOOKUP is newer and gives you more flexibility. It needs a few different pieces of information:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value: What you want to find.
  • lookup_array: Where to look: This tells Excel where to start searching. It’s like telling it, “Look in this column of names.”
  • return_array: Where to find the answer: Once Excel finds the name, it needs to know where to get the related information, like the phone number. You tell it which column has the phone numbers.
  • [if_not_found]: Optional: Sometimes, the thing you’re looking for isn’t there. Excel can either give you an error or a message you choose, like “Not found.” It’s like choosing what to hear when your search doesn’t work out.
  • [match_mode]: Optional: 0 = exact match, -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match. If you don’t choose, Excel will look for an exact match by default.
  • [search_mode]: Optional: 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

To learn more about XLOOKUP, read this article HERE.

Making the Choice between VLOOKUP vs XLOOKUP

It depends on what you’re trying to do and which version of Excel you have. XLOOKUP is more flexible and powerful, but it’s only available in Excel 2021 (and above) and Microsoft 365.

While it has its limitations, VLOOKUP is still useful, especially if you’re working with older Excel versions (Excel 2019 and below) or need something simpler.

Key Differences XLOOKUP vs VLOOKUP

XLOOKUP and VLOOKUP are both tools to search for data in a spreadsheet. They work differently, so learning about these differences can help you choose the right one for your needs.

Difference 1: Lookup to the Left

XLOOKUP can easily grab data from the left of the lookup column; VLOOKUP on the other hand can’t return data located in a column to the left of the lookup column, without a complicated workaround.

Imagine we want to find an employee’s name (in Column A) using their ID (in Column B):

The VLOOKUP doesn’t work here. It shows an error (#N/A) because it can’t find the employee name in column A. That’s because Column A is to the left of the ID column (Column B), and VLOOKUP can only look to the right.

But, XLOOKUP can fix this. It can look both left and right of the ID column.

Difference 2: Exact Match by Default

If you do not specify the “range_lookup” argument in VLOOKUP, it tries to find the next best thing. But this can lead to mistakes.

XLOOKUP always searches for the exact thing you want, making errors less likely.

In our example, we’re looking for ID999 which does not exist in the list of employees.

If VLOOKUP doesn’t find an exact match and we didn’t tell it to look for one (by not adding “1” or “False” in the range_lookup argument), it picks something close instead.

XLOOKUP by default returns exact matches. So, even if we do not define anything, it will not return a close match.

Difference 3: Adding or Removing Columns

VLOOKUP counts the columns to find the answer. If you change the order of the columns or add new ones, it gets confused and might stop working right.

XLOOKUP doesn’t have this problem. It can deal with changes and still find what you’re looking for.

XLOOKUP vs VLOOKUP cannot handle if new column is inserted in range.

XLOOKUP doesn’t break easily. It can deal with changes and still find what you’re looking for.

Difference 4: Error-Handling When There Is No Match

XLOOKUP has a special optional argument called “if_not_found.” It lets you pick what to say when it can’t find something. VLOOKUP, on the other hand, just gives you an error (#N/A) when it doesn’t find something.

For example, imagine we’re looking for the name of “ID14” in column B. But, “ID14” isn’t on the list.

XLOOKUP vs VLOOKUP returns error if no match is found

VLOOKUP shows an error (#N/A) if it can’t find a match. To fix this you have to wrap the VLOOKUP inside another function (IFERROR) to avoid the error.

XLOOKUP is better. It lets you decide what to return when it doesn’t find a match, making it clearer and more helpful.

Difference 5: Search Direction

VLOOKUP looks from the top to the bottom of a list and finds the first match. XLOOKUP can also find the last match in a list. This means XLOOKUP can easily find the most recent price in a list that’s in order by date.

For example, let’s say we want to know which department Kim West, who has the ID “ID13”, works in now. First, Kim West worked in the Utilities division but later moved to the Games division.

VLOOKUP only looks from top to bottom and it will find Kim’s first job which is in the Utility division.

But if we use XLOOKUP with the search mode setting “-1” (Search last-to-first), it will look from the bottom of the list up. This way, it finds Kim’s current job first, which is in the Game division.

Difference 6: Approximate Match Without Sorting

Both XLOOKUP and VLOOKUP can find close matches. This is useful, for example, when turning number scores into letter grades.

In this example, we’re looking at test scores in column C and want to find the matching grade from the table in columns F to H.

You can find the right grade with VLOOKUP, but only if your grade table is in order. If it’s not, VLOOKUP might get the grade wrong.

XLOOKUP can find the grade you need without worrying about the sort order of the table.

Conclusion

VLOOKUP has been around for a long time and is still widely used in Excel, despite its limits. XLOOKUP is newer and fixes these issues, making it generally better. But, many Excel files still use VLOOKUP effectively for basic tasks.

You don’t have to rush to switch to XLOOKUP unless VLOOKUP is causing problems. Keep in mind, XLOOKUP works only in Excel 2021, newer versions, and Microsoft 365. If someone with an older Excel opens your file, they’ll run into errors.

So, try using XLOOKUP for your needs. It’s takes a bit more time to learn properly but offers a lot more options. Even though you might use XLOOKUP a lot, you’ll still see VLOOKUP around. If you use Excel often, knowing both is helpful.

Download the Practice Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real time. Master the Excel XLOOKUP and VLOOKUP functions with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.