How to use XLOOKUP and VLOOKUP in Excel

Updated on: March 22, 2023

When you work with massive data strings, you can get lost along your own worksheet! And it's awful when you don't find anything you need after reviewing your information.

But have you ever heard about VLOOKUP and XLOOKUP? Because these functions will make your life easier!

Keep reading because this article will guide you on how to use XLOOKUP and VLOOKUP in Microsoft Excel!

Read on to learn how to use them and be more agile when working in Microsoft Excel!


VLOOKUP and XLOOKUP are both functions in Microsoft Excel that help users find and retrieve data from a table or range. Both of these functions work similarly, but they have some key differences that may make one more appropriate than the other, depending on the situation.

On the one hand, VLOOKUP, which stands for "Vertical Lookup," searches for a specific value in the table's leftmost column, then returns a value in the same row from a specified column to the right. VLOOKUP is a popular and widely used function in Excel, but it has some limitations.

One is that it only searches in one direction (from left to right) and can only return a value from a column to the right of the lookup column.

XLOOKUP, on the other hand, is more versatile. It can search for a value in any table column and return a value from any other column. Its name stands for "eXtended Lookup" indeed, and it also has some additional features that make it more flexible and powerful than VLOOKUP.

For example, XLOOKUP can search for the nearest match if an exact match is not found. It can also handle multiple lookup values and return multiple results.

Note: One of the most significant advantages of XLOOKUP over VLOOKUP is that it can search in any column of a table, not just the leftmost column.

How to apply them?

To use VLOOKUP, you first need to set up a table with the lookup value in the leftmost column and the value you want to return in a column to the right. After that, specify the lookup value, the table range, the column number of the value you wish to return, and whether you want an approximate or exact match following this syntaxis:

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

  • lookup_value: This argument is the value to search for in the first column of the table.
  • table_array: The table range that contains the data. The lookup value should be in the first column of this range.
  • col_index_num: The column number in the table array from which to return the corresponding value.
  • range_lookup: Because this is between brackets, you can infer it’s an optional argument corresponding to a logical value specifying whether you want an exact match or an approximate match. If TRUE or omitted, it will return you an approximate match. If FALSE, an exact match.

For example, suppose you have a sales data table with the product's name in column A and the sales figure in column B. In this case, you could use VLOOKUP to find the sales figure for a particular product, like this:


In this example, "Oranges" is the lookup value, A:B is the table range, 2 is the column number of the sales figure, and FALSE indicates that you want an exact match.

To use XLOOKUP, you first need to specify the lookup value, the table range, and the column number of the value you want to return. You can also specify whether you want an exact match, the closest match, or multiple matches using this syntaxis:

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

  • lookup_value: The value to search for in the lookup_array.
  • lookup_array: The range or array of cells to search for the lookup value.
  • return_array: The range or array of cells from which to return a value.
  • if_not_found (optional): The value to return if Exel doesn’t find a match. The program will automatically show you “#N/A” in the cell if you don't put anything.
  • match_mode (optional): A numeric value specifying the match mode. 0 for an exact result (default), -1 for an exact match or next smallest, 1 for an exact match or next largest, and 2 for a wildcard match.
  • search_mode (optional): A numeric value that specifies the search mode. 1 for search from the start (default), 2 for search anywhere, and 3 for search from the end. 

So, for example, if you have a table of customer data with the customer ID in column A, the customer name in column B, and the customer email address in column C. In that case, you could use XLOOKUP to find the email address for a particular customer like this:

=XLOOKUP("12345", A:A, C:C, "", "exact")

In this example, "12345" is the lookup value, A:A is the table range for the customer ID column, C:C is the table range for the email address column, "" indicates that you want to return an exact match, and "exact" specifies that you want an exact match. This formula would return the customer's email address with the ID "12345".

In summary, both VLOOKUP and XLOOKUP are powerful functions in Excel that can greatly help you. By understanding their differences, you can choose the best suits your needs and use it to save.

Do you want to know more?

Finally, to increase your productivity, try our Excel course here in Skillademia and find these handy tips for mastering Excel o learn more about how to create Pivot Table Slicers or understand what Macros are.

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 4,242+ video lessons. Instantly.
Start Learning Now →