VLOOKUP vs XLOOKUP: Which is the better Excel Formula?

LOOKUP functions are among the most valued formulas in Microsoft Excel. These tools help locate a single data in one column and then show linked data from a different column.

VLOOKUP emerged as a widely utilized and essential lookup formula as time passed. However, Microsoft Excel has now rolled out a fresh lookup tool, XLOOKUP, designed for the recent Office 2021 and Office 365 editions.

This guide will comprehensively go over XLOOKUP and VLOOKUP to determine the superior method for finding values in Excel.

What is VLOOKUP?

VLOOKUP is a function in Excel that helps you find a certain piece of data in a list. It can show you other information from that same line when it finds the data you want. People have been using VLOOKUP in Excel for a long time.

VLOOKUP is mostly used to search up and down in a list (that’s why it’s called “vertical” lookup). Many use it to get details from big lists or do math based on what they find.

Syntax

Lookup functions need at least three parts to work. The things in square boxes are extra options you can use. Here is the syntax for VLOOKUP function:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters

• Lookup_value – This is the thing you’re trying to find. It looks for this and shows related info from another column.
• Table_array – Here, you tell the tool where to start and stop looking.
• Col_index_num – This is like saying which column has the answer you want.
• Range_lookup – You can put either TRUE or FALSE here. TRUE means you’re okay with a near guess. FALSE means you want the exact thing only.

What is XLOOKUP?

XLOOKUP is a new search tool in Excel that helps you find a certain piece of data in a list. When it finds what you’re looking for, it shows you other details from the same line or column. It’s like an upgraded VLOOKUP.

The cool thing about XLOOKUP is it can search both up-down (vertical) and side-to-side (horizontal). So, it’s handy for lots of different tasks.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameters

• Lookup_value – This is what you’re trying to find.
• Lookup_array – Here, you tell the tool where it should look.
• Return_array – This is where you want to get details once the item is found.
• [if_not_found] – If you use this, you can pick what the tool says if it doesn’t find anything. If you skip it, you’ll see a #N/A error.
• [match_code] – You can use 0, -1, 1, or 2 here to tell the tool how closely you want things to match.

Difference between VLOOKUP and XLOOKUP

Taking a closer look, here are the fundamental differences between VLOOKUP and XLOOKUP, highlighting their individual strengths and applications.

Robustness and flexibility

When you set up your list in Excel, you want to add new things without worrying about your formulas breaking. With VLOOKUP, you often have to fix the whole command if you change columns around.

But XLOOKUP is tougher. You can add or take away columns, and it still works fine. Just remember, adding something outside the area it’s looking in might give an error.

Column index number

With VLOOKUP, you have to know exactly which column number you’re looking at. You’d often go back to your main table to double-check. Getting this column number right is super important. If it’s wrong, your sheet might show an error like “#REF” or give a wrong answer.

But with XLOOKUP, things are easier. You don’t have to count columns anymore! Just tell it what you’re looking for and where to look. It saves you from counting and mistakes, which means less hassle.

Custom error messages

XLOOKUP is cool because if it can’t find what you’re looking for, you can make it say a special message you pick with the [if_not_found] part.

VLOOKUP doesn’t have this trick. If something’s missing, you might have to use tricks like IFERROR to handle it and not show errors.

Sorting of lookup range

With VLOOKUP, if you’re doing a loose (TRUE) search, things need to be in order from smallest to biggest. If they’re not, you might get the wrong answer.

For XLOOKUP, the order matters in certain situations, like when it starts searching from the end or uses a special binary search method.

Data sorting

VLOOKUP wants data in order from smallest to biggest. But XLOOKUP is cool because it can handle data even if it’s mixed up.

Compatibility with older versions of Excel

VLOOKUP works fine with older Excel versions. But XLOOKUP is a newer tool, so it’s only in Microsoft 365, 2021 and the online version, Excel for the Web. If you’re sharing a sheet that uses XLOOKUP, be sure the other person has Microsoft 365 for now.

Search direction

VLOOKUP and XLOOKUP find things differently. VLOOKUP goes up and down in a list. It starts at the first column and moves on until it finds what it wants. This can be a bit slow with big lists or if things are mixed up.

On the other hand, XLOOKUP is smarter. It can look both up and down and side-to-side. So, it’s great for tricky lists because it can search around rows and columns to find answers faster.

Return value position

VLOOKUP has a bit of a drawback. When you ask it for data, it only looks at columns on its right. If you need something from the left? Tough luck. This restriction can complicate the retrieval of data from columns to their left.

XLOOKUP is more flexible. It can grab data from any spot around your starting point: left, right, above, or below. With XLOOKUP, you’re not limited to just one direction, making it a more comprehensive tool for finding the necessary information.

Pros & Cons

Every excel formula has its limits, even if it does some incredible things otherwise. Let’s quickly breakdown the strengths and weaknesses of each of VLOOKUP and XLOOKUP.

Read More: How to Hide Columns in Excel

VLOOKUP Pros

• Intuitive operation: VLOOKUP starts by checking the first column of the table. Once it spots a matching item, it goes over to the given column number to fetch the related value. With just a few inputs, using VLOOKUP is a breeze.
• Widely used: There are countless spreadsheets out there relying on VLOOKUP. You’ll see it used all over, and knowing how to use VLOOKUP is a big plus, even if XLOOKUP is your go-to.
• Simple configuration: Got a data table with the first column ready for lookups? You’re all set for VLOOKUP. It just asks for a lookup item, where the table is, and the column’s number.

XLOOKUP Pros

• Smart defaults: XLOOKUP starts with an exact match by default, making it safer. VLOOKUP’s default of approximate matching can sometimes give unexpected results.
• Search flexibility: XLOOKUP doesn’t just search up and down; it goes side to side, too. No need for extra functions if your data is laid out horizontally.
• Two-way search: With XLOOKUP, you can search forward direction (first to last) or in reverse (last to first). So, finding the most recent data, like the latest prices, becomes a piece of cake.
• Normal column reference: With XLOOKUP, you reference cells as usual, making it less prone to errors than VLOOKUP if you change your table (like adding or taking away columns).
• Approximate match: XLOOKUP can find an exact match, the nearest lesser value, or an exact match or the closest bigger value. Plus, your data doesn’t have to be in order.
• Built-in error handling: XLOOKUP comes with its own ‘if_not_found’ argument. So, you can tell it exactly what to do or say if it can’t find a match. No need for extras like IFERROR.

VLOOKUP Cons

• Error Messages: Lacks the ability to display custom error messages.
• Search Direction: Exclusively searches top-down and cannot search horizontally.
• Single Value Return: Only returns a single value.
• Column Dependency: Relies heavily on the first column of a table for lookups.
• Approximate Search: Won’t round up, which can lead to incorrect results if not set up carefully.
• Setup Sensitivity: Minor misconfigurations can lead to errors like “#REF”.

XLOOKUP Cons

• Compatibility: Not available in older Excel versions, limiting its use for those on older platforms.
• Data Sorting: Without proper data sorting, it might return incorrect values.
• Binary Search: While it can utilize a binary search, this method has its own set of limitations and might not be suitable for all datasets.

Here’s a nifty comparison table highlighting the differential pros and cons between the two:

Why Is XLOOKUP better than VLOOKUP?

In XLOOKUP, defining a return range or array directly from the data table simplifies the process. In contrast, VLOOKUP demands manual entry of column index numbers when pulling multiple values, which becomes challenging with expansive datasets.

• XLOOKUP has a distinct advantage when displaying a custom error message for missing lookup values, an ability VLOOKUP lacks.
• VLOOKUP is restricted to the first column of a table; XLOOKUP can search any column within the provided data set.
• Instead of defining the entire table array like in VLOOKUP, XLOOKUP distinctly designates the lookup and return arrays.
• XLOOKUP can search for a value from the bottom upwards, allowing for data extraction based on the last occurrence without additional functions. VLOOKUP, however, lacks this capability.
• While XLOOKUP can utilize a binary search, VLOOKUP does not offer this search type.

Final thoughts

In wrapping up, the evolution from VLOOKUP to XLOOKUP in Microsoft Excel marks significant advancements in data search and retrieval.

XLOOKUP addresses many shortcomings of VLOOKUP, offering versatility with two-way searches, custom error messages, and flexibility in column or row selection. It streamlines data extraction from large datasets and provides enhanced error handling.

However, it’s pivotal to note its limited compatibility with older Excel versions. While XLOOKUP remains foundational in numerous spreadsheets, XLOOKUP emerges as the more dynamic, user-centric tool for those on Microsoft 365 or Excel for the Web.

Both have strengths, but XLOOKUP sets a new benchmark in Excel’s data analysis capabilities.