Excel has two popular lookup functions: VLOOKUP and XLOOKUP. While they may seem similar at first glance, there are key differences between them that can affect how you use them in your spreadsheets. In this tutorial, we’ll go over the main differences between XLOOKUP and VLOOKUP and how to use them with examples.
VLOOKUP
VLOOKUP is a lookup function in Excel that searches for a value in the left-most column of a table and returns a corresponding value from a specified column. The V in VLOOKUP stands for “vertical,” as it searches vertically down a table.
Here’s the basic syntax for VLOOKUP:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: the value you want to find in the first column of the table.table_array: the range of cells that make up the table you want to search.col_index_num: the column number (starting from 1) in the table_array that contains the value you want to return.range_lookup: an optional argument that determines whether to perform an exact or approximate match. If set to TRUE or omitted, an approximate match will be performed. If set to FALSE, an exact match will be performed.
Let’s look at an example to understand VLOOKUP better. Here’s a table that contains sales data for different products:
| Product | Sales |
|---|---|
| A | 100 |
| B | 200 |
| C | 300 |
| D | 400 |
| E | 500 |
Suppose we want to find out the sales figure for Product C. We can use VLOOKUP with the following formula:
=VLOOKUP("C", A2:B6, 2, FALSE)Here, the lookup value is “C,” the table array is A2:B6, and the column index number is 2 because we want to return the sales figure (which is in the second column). The range_lookup argument is set to FALSE because we want an exact match.
When we enter this formula in a cell, Excel returns the value 300, which is the sales figure for Product C.
XLOOKUP
XLOOKUP is a new lookup function in Excel that was introduced in Office 365. It’s a more powerful and flexible lookup function than VLOOKUP, as it allows you to search for a value in any column of a table and return a corresponding value from any other column.
Here’s the basic syntax for XLOOKUP:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: the value you want to find in the table.lookup_array: the range of cells that make up the table you want to search.return_array: the range of cells that contains the values you want to return.if_not_found: an optional argument that specifies the value to return if the lookup value is not found. If omitted, XLOOKUP will return an error message.match_mode: an optional argument that specifies how to perform the match. If set to 0 or omitted, XLOOKUP will perform an exact match. If set to 1, XLOOKUP will perform an approximate match.search_mode: an optional argument that specifies whether to search from the beginning or end of the lookup_array. If set to 1 or omitted, XLOOKUP will search from the beginning. If set to -1, XLOOKUP will search from the end.
Let’s continue with the example we used for VLOOKUP to understand XLOOKUP better. Here’s the same table:
| Product | Sales |
|---|---|
| A | 100 |
| B | 200 |
| C | 300 |
| D | 400 |
| E | 500 |
Suppose we want to find out the sales figure for Product C using XLOOKUP. We can use the following formula:
=XLOOKUP("C", A2:A6, B2:B6)Here, the lookup value is “C,” the lookup array is A2:A6 (the column containing the product names), and the return array is B2:B6 (the column containing the sales figures). Since we want an exact match, we don’t need to specify the match_mode argument.
When we enter this formula in a cell, Excel returns the value 300, which is the sales figure for Product C.
One of the advantages of XLOOKUP is that it can perform a reverse lookup. In other words, it can search for a value in a column and return a corresponding value from another column. Here’s an example:
Suppose we have the same sales data, but we want to find out which product had sales of 400. We can use XLOOKUP with the following formula:
=XLOOKUP(400, B2:B6, A2:A6)Here, the lookup value is 400, the lookup array is B2:B6 (the column containing the sales figures), and the return array is A2:A6 (the column containing the product names). Since we want an exact match, we don’t need to specify the match_mode argument.
When we enter this formula in a cell, Excel returns the value D, which is the product that had sales of 400.
Another advantage of XLOOKUP is that it can handle errors more gracefully than VLOOKUP. For example, if we try to look up a value that’s not in the table, VLOOKUP will return an error message, while XLOOKUP can return a specified value instead. Here’s an example:
Suppose we want to find out the sales figure for Product F, which is not in the table. We can use XLOOKUP with the following formula:
=XLOOKUP("F", A2:A6, B2:B6, "Product not found")Here, the lookup value is “F”, the lookup array is A2:A6 (the column containing the product names), the return array is B2:B6 (the column containing the sales figures), and the if_not_found argument is set to “Product not found.” This means that if the lookup value is not found, XLOOKUP will return the specified value instead of an error message.
When we enter this formula in a cell, Excel returns the value “Product not found,” since there is no product named F in the table.
In conclusion, while VLOOKUP and XLOOKUP can both be used to look up values in a table, XLOOKUP is a more powerful and flexible function that can perform reverse lookups and handle errors more gracefully. It’s a great function to use when you need more control over your lookup operations.