In this guide, we will compare VLOOKUP vs. XLOOKUP in Excel. We will explain each function, including “What is VLOOKUP?” and “What is XLOOKUP?”, review their syntax, and discuss the functional advantages of each Excel function.
How to Use
As you know, the VLOOKUP function is one of the most commonly used functions for data analysis and reporting. However, due to limitations in the VLOOKUP formula structure, there are cases where it cannot be used depending on the layout of the source data.
In these cases, XLOOKUP can provide a solution. If you focus on these examples, the approximately three minutes you spend on this post will be worthwhile.
What Is the VLOOKUP Function?
What is the most representative lookup function in Excel? Most people would say VLOOKUP. Other options include XLOOKUP, INDEX MATCH, and various formulas that use the dynamic range capabilities of OFFSET.
Definition
The Excel VLOOKUP function is a lookup function used to find a desired value in a specified cell range by using a lookup value.
Function Name
The name VLOOKUP combines Vertical (abbreviated Vert.) with Look-up. It finds and returns a desired value based on a lookup value in a vertical direction.
Syntax
= VLOOKUP (lookup value, specified range, column position of the value to find, exact match)
Syntax Arguments
- Lookup value: Use a value in the data you want to search, typically one that is not duplicated, as the lookup value.
- Specified range: Typically, set a range with at least two columns and include all rows through the last row of data. To reuse a VLOOKUP formula in other cells, use two methods. First, use an absolute reference to lock the range. Second, use the data Table feature to lock the range and assign names to the fields.
- Column position of the value to find: Count columns from left to right within the specified range, and enter the column position as a number. Generally, count starting with 1. Include hidden cells within the range when counting. Usually, you can identify the column number and use it in VLOOKUP, or use the MATCH function to use the field name as a lookup value and automatically return the column position within the specified range.
- Exact match: Use 0 for an exact match and 1 for a partial match. Generally, use 0.
What Is the XLOOKUP Function?
The XLOOKUP function is available in Excel 365 and is an Excel lookup function that overcomes the limitations of VLOOKUP and HLOOKUP. It can also replace the functionality of the IFERROR function, which converts error values into a desired format when a value is outside the applicable range of a formula.
Definition
The XLOOKUP function searches a range or array and automatically returns the item corresponding to the first match it finds. If no match is found, it can also return an approximate match depending on the formula syntax.
Function Name
XLOOKUP is a lookup function without the horizontal and vertical limitations of traditional lookup functions. Personally, I view the X in XLOOKUP as representing the combined capabilities of the Horizontal in HLOOKUP and the Vertical in VLOOKUP.
Syntax
=XLOOKUP(lookup value, lookup_array, return_array,[if_not_found], [Match mode], [search mode])
Syntax Arguments
- lookup value: Enter the value that will be used as the basis for finding data.
- lookup_array: Enter the column containing the lookup values. (If it is column A, selecting A:A, the entire column, is recommended. The best practice is to use an absolute reference.)
- return_array: Enter the column containing the data you want to return. (If it is column D, selecting D:D, the entire column, is also recommended.)
- [if_not_found]: This optional argument specifies what to return when no data matches the lookup value. The functionality of the IFERROR function can be considered included in XLOOKUP.
- [Match mode]: This optional argument lets you choose how the lookup value is matched.
- [search mode]: This optional argument determines the search direction when the lookup value is not unique.
You can return a value with just the first three arguments above, but because XLOOKUP is optimized for returning individual data values, it is best to understand all its features before using it. - The [if_not_found] argument is optional, as mentioned above. It specifies what to return when no data matches the lookup value. See the example below.
=XLOOKUP(lookup value, lookup_array, return_array,0)
This formula returns 0 when there is no matching lookup value. To return text, enter it in quotation marks, in the form of “text”. - The [Match mode] argument is optional and lets you choose how the lookup value is matched. You can use 0, -1, 1, or 2. Because 0 means an exact match, enter 0 if you want to specify this argument.
- 0: Exact match
- -1: Prioritizes an exact match; if none is found, it searches for the next smaller value. (Available when the lookup value is a number.)
- 1: Prioritizes an exact match; if none is found, it searches for the next larger value. (Available when the lookup value is a number.)
- 2: You can use wildcard characters to find the desired data. Wildcards refer to [ , ?, ~ ]. They can be used when the lookup value is text. For example, if you search using “1” as the lookup value, it returns a value matching text that begins with 1.
- The [search mode] argument is optional and determines the search direction when the lookup value is not unique. Use 1, -1, 2, or -2.
- 1: Returns the first value and is generally the most commonly used option.
- -1: Returns the last value.
- If the lookup value is duplicated twice, you can build a formula using 1 and -1 in search mode to return the value you want after the lookup. Options 2 and -2 use a “binary search” to find values. Use 2 for ascending order and -2 for descending order. Because they allow faster searches, use them for lookups that require sorted data.
VLOOKUP Function Examples
- VLOOKUP Multiple-Criteria Lookup Example
- VLOOKUP Duplicate Value Example
- VLOOKUP Function + VLOOKUP Function Example
- VLOOKUP Function Error Value Solution Example
XLOOKUP Function Examples
Conclusion
The VLOOKUP function has been used in Excel for a long time. It finds a specific value in a single column and returns the corresponding value from another column.
In contrast, the XLOOKUP function offers simpler and more flexible lookup capabilities than VLOOKUP. Compared with VLOOKUP, XLOOKUP offers the following advantages:
- You can freely select the lookup column and return column.
- The lookup column does not need to be to the left of the return column. (This is VLOOKUP’s greatest limitation.)
- It works even when the lookup column contains duplicate values.
- It can find and return a nearby value even when the lookup value is not an exact match.
Therefore, XLOOKUP provides more flexible and powerful lookup capabilities than VLOOKUP.