How to Use VLOOKUP and XLOOKUP in Excel: Complete Guide and Practical Examples

Have you ever found yourself lost among the thousands of rows and columns of a spreadsheet when searching for specific data? If you regularly work with Excel, you've probably heard of VLOOKUP and XLOOKUP, two functions that make a difference when it comes to automating searches and streamlining tasks. Mastering these functions represents a leap in quality in the daily work of any user, from those who manage simple contact lists to those who analyze large projects. databases In companies.

This article is your ultimate guide to understanding and using VLOOKUP and XLOOKUP in Excel like a pro. Here we break down all the secrets, advantages, limitations and Tricks practical, starting from the basics and delving into examples, syntax, and ways to get the most out of them, so that a search in Excel never stops you again.

What are VLOOKUP and XLOOKUP in Excel?

VLOOKUP and XLOOKUP belong to the family of lookup and reference functions in ExcelThese are tools that make it easy to locate specific data in large lists or tables, returning related information quickly and error-free. Their uses are as diverse as they are necessary: ​​invoicing, inventories, reports, human resources, CRM, accounting, and more. If you need to find something in a sea of ​​data, these features are your best friend.

VLOOKUP is the classic formula for searching data vertically., that is, within a column. It's been a mainstay in Excel for years. However, with newer versions comes XLOOKUP, an even more powerful and flexible function that overcomes many of VLOOKUP's limitations.

Why is it so important to learn both? Because many companies still work with older versions of Excel, where XLOOKUP doesn't exist, and because VLOOKUP is still useful and highly compatible. However, if you have the right version, XLOOKUP opens up a world of new possibilities.

What is each one used for? Basic uses and real-life situations

The VLOOKUP function is used to find data by referring to a value in the leftmost column of a table., returning something related that's in another column in the same row. For example: you have a list of products with codes, descriptions, and prices, and you want it to automatically display the description and price when you enter the code. VLOOKUP makes it easy for you.

SEARCHX takes this concept to the next level: Allows you to search both vertically and horizontally, in any column or row, and returns one or more data items without VLOOKUP restrictions. You can also customize search behavior, search results in case of errors, search with wildcards, and other advanced features.

Let's look at this with a very common practical example: invoice management. Imagine you're an SME that sells medical supplies. You don't have an advanced invoicing system, and you need to extract data from a list of more than 3.000 items every time an order comes in. Filling in the data manually would take forever and be prone to errors. With VLOOKUP or XLOOKUP you can automate the process so that, with just the product code, the description, presentation and price are automatically filled in.

Key Differences: What Limits VLOOKUP and Why Is XLOOKUP the Evolution?

VLOOKUP is a great function, but it has some limitations. which are uncomfortable in complex sheets:

• Only search from left to rightThe reference data must always be in the first column of the specified range. If the data you're looking for isn't there, you'll need to reorganize the table or perform additional joins.
• Returns a single valueIf you need to extract multiple related data items (e.g., first name, last name, and department), you'll need to use multiple formulas.
• You must indicate the number of the column to be returned. If you then rearrange columns in the table, the formula may break or return incorrect results.
• The default result for data not found is the #N/A error.. It must be combined with YES.ERROR to display a personalized message.
• Does not support wildcards or reverse lookup (bottom-up), nor advanced binary search.

XLOOKUP removes virtually all of these restrictions and adds decisive improvements:

• Allows you to search in any column or row, regardless of positionYou can search in the middle column and return something from the first one, for example.
• Return multiple values ​​when using multi-column ranges, facilitating searches for more complex databases.
• You don't have to count columns. You directly select the result range.
• You can customize the not found message without additional formulas.
• Exact, approximate and wildcard search available to find part or exactly what you are looking for.
• Possibility of reverse (bottom-up) and binary search (ideal for ordered data).
• Searching multiple tables simultaneously and using nested XLOOKUP, which expands the possibilities of automation.

Which versions of Excel allow each function?

The VLOOKUP function is available in all modern versions of Excel., from the oldest to the most recent. You will find it both in Microsoft 365 as in the perpetual versions of Excel for desktop.

XLOOKUP, on the other hand, is only available from Excel version 1910 onwards.If you have Excel 2016 or one of the earlier versions released in 2019, you probably don't have it. To find out if you can use XLOOKUP, just type =XLOOKUP in the formula bar and see if it appears in the list of suggestions.

If your version doesn't support it yet, don't despair. Master VLOOKUP while you can upgrade, and when the time comes, upgrade to XLOOKUP to take advantage of its improvements.

VLOOKUP Syntax: Breaking Down Each Argument

Let's break down the VLOOKUP formula to find out exactly how it works.:

=VLOOKUP(lookup_value; table_array; column_indicator; )

• lookup_value: The data you want to find (can be written directly or in a reference cell).
• table_array: The full range to search, including the column where the reference data is located and the columns of information to retrieve.
• column_indicator: The number (starting with 1) of the column from which you want to extract the result within the selected range. If you want the data from the second column in the range, you'll enter 2 here.
• : Optional. If TRUE or omitted, returns an approximate match; if FALSE, returns only exact matches.

Typical example: You have a customer table in the range A1:D16. You want to find the phone number (which is in column 4, or column D) for customer ID 8. The formula would be:

=VLOOKUP(8;A1:D16;4;FALSE)

If the data doesn't exist and you request an exact match, you'll see a #N/A error. If you allow an approximate match, the closest result below will be returned.

Advanced VLOOKUP Tricks and Variants

VLOOKUP is not limited to searching by the first column: you can redefine the range to search by any columnFor example, if you have names in column B and need to search by name instead of ID, simply define the range from column B onward and set the column indicator:

=VLOOKUP("Miguel Perea Ramos";B1:D16;3;FALSE)

This will search by name and return, for example, the corresponding phone number.

Another useful combination is VLOOKUP with drop-down lists: You can let the user select a value from a list, and another cell will automatically display relevant information using VLOOKUP. This is ideal for worksheets, automated reports, catalogs, and more.

And if you want to avoid ugly mistakes, combine VLOOKUP with YES.ERROR to display custom messages if the data does not exist:

=IF(VLOOKUP(8;A1:D16;4;FALSE);»Customer not found»)

XLOOKUP Syntax: Arguments and Their Power

The XLOOKUP formula is structured like this:

=XLOOKUP(lookup_value; lookup_array; returned_array; ; ; )

• lookup_value: The data you want to locate (cell or direct value).
• lookup_array: Range where Excel should search for that data.
• returned_array: Range from which to extract the result. This can be one column or several if you want multiple pieces of data at once, unlike VLOOKUP.
• : Optional. If the value isn't found, you can specify the message or data you want to display (for example, "Not Found").
• : Optional. Defines how the value is searched: 0 for exact, -1 for exact or next lower, 1 for exact or next higher, 2 for wildcard matching.
• : Optional. Allows you to choose how to traverse the data: 1 top to bottom, -1 bottom to top, 2 binary search ascending, -2 binary search descending.

Typical example of use with XLOOKUP: You want to search for a product in a list (codes in column A and prices in column E), display the price, and customize the message if the product doesn't exist. The formula would be:

=XLOOKUP(B2;Prices!$A$1:$A$7000;Prices!$E$1:$E$7000; "Not found"; 0)

Here, if the element does not exist, it will directly show you “Not Found” without the need to YES.ERROR.

Breakdown of all optional XLOOKUP arguments

One of the great advantages of XLOOKUP is the flexibility of its optional arguments. Let's look at them one by one:

• : You can return text, a number, or even an empty cell by entering “”. Forget the #N/A visual error and customize the experience.
• :
  • 0: Exact match (default).
  • -1: Exact match or next lowest value.
  • 1: Exact match or next highest value.
  • 2: Wildcard matching (* for multiple characters, ? for a single character, ~ to ignore the special character).
• :
  • 1: From the first to the last row (default).
  • -1: From last to first (reverse search).
  • 2: Ascending binary search (requires data sorted from lowest to highest).
  • -2: Descending binary search (requires data sorted from highest to lowest).

Practical examples of XLOOKUP in action

We show you how LOOKUPX gains an advantage in various real-life scenarios:

• Search for a single data item: Searches for a country name and returns its country code.
• Search for multiple data: Unlike VLOOKUP, you can return an entire row of information (name, department, etc.) with a single formula.
• Error-free customization: If you want to avoid the typical #N/A when the data does not exist, you just have to use the argument .
• Combined search (vertical and horizontal): You can nest XLOOKUP to cross values ​​in a table in both dimensions, similar to combining INDEX and MATCH.
• Dynamic ranges: If you have two ranges of data (multiple tables), you can concatenate both in the formula and XLOOKUP will search both simultaneously.
• Partial wildcard search: Find data by partial match using *, ?, or ~ in your search value.

Quick argument comparison: VLOOKUP vs. XLOOKUP

VLOOKUP always requires the column number where is the data to be returned and only allows searching in the first column of the range. On the other hand, XLOOKUP does not depend on the order of the columns or their position, simply specify the precise ranges to search and return.

Additionally, XLOOKUP supports dynamic arrays.: When the returned_array groups multiple columns, you can return a series of contiguous data in multiple cells at once, without having to repeat the formula.

Example arguments and solved cases

Imagine a table of countries with data on population, life expectancy and codesIf you only want the population, you define the returned array as the population column. If you want multiple data points, you define the returned array as multiple columns. By placing the formula in cell G6, the data in columns H6 and I6 will also be dynamically populated.

What happens if you search for data that doesn't exist? With VLOOKUP, if you search for Uruguay (and it is not in the list) and you do not use YES.ERROR, you'll see the #N/A error. With XLOOKUP, you decide which message appears, the number, or the result you want.

Beyond exact search: match mode and its uses

• To find the next lowest value, use -1. For example: Searching for 46.520 in a discount table will return the discount for the next lowest amount if it doesn't exist.
• For the next higher value, use 1. Useful if you're looking for progressive ranges.
• To use wildcards, use 2. For example, search for “*south*” and you will get “South”, “Southeast”, “North-south”…

How do wildcards work in XLOOKUP?

The asterisk (*) replaces any number of charactersIf you search for "*east", you'll see "East", "Southeast", "Northeast"...

The question mark (?) replaces only one character. Unsure about "Maria" or "Mario"? Try "María" and both will be found.

The tilde (~) overrides the wildcard valueIf you search for "How~?", you won't find "Howr", just "How?".

What is the binary search that XLOOKUP offers?

When you have large ordered lists, binary search allows you to locate data quickly.Imagine a 100-page book: with binary, you split it in half, choose the block where it can be, and reduce the number of checks. Of course, you need the range to be well sorted (ascending or descending depending on the mode chosen), otherwise, the results will be incomplete. reliability.

Can I search multiple tables or by more than one criterion at a time?

XLOOKUP allows you to search multiple concatenated ranges at once using the appropriate syntaxFor example, if you have two country tables, the lookup_array can be both areas separated by a colon, just like the return_array. All in the same formula, with no extra effort.

What if I want to cross-reference multiple criteria? This is where nested XLOOKUP comes in: you can use one initial lookup to determine the column range and another for the row, returning the exact data at the intersection, like an advanced version of INDEX + MATCH.

How much can I customize XLOOKUP results?

A little-known advantage of XLOOKUP is that you can return several types of results in case of error.You can display custom text, a number, or leave the cell blank depending on your needs. Additionally, when returning multiple columns, the result occupies adjacent cells, which cannot be edited individually from Excel, but can be modified by deleting the main formula.