VLOOKUP and XLOOKUP are two functions in Excel that are used to look up and retrieve data from a table. While VLOOKUP has been a popular function for many years, XLOOKUP is a newer function that was introduced in Excel 365. In this article, we will compare VLOOKUP vs. XLOOKUP, including the benefits and drawbacks of each function, and provide three examples that show the data used and the result.
VLOOKUP
VLOOKUP is a function that is used to look up a value in a table and return a corresponding value from the same row. The V in VLOOKUP stands for vertical, as the function looks up the value in a vertical column. The syntax for VLOOKUP is as follows:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Benefits of VLOOKUP:
- Simplicity: VLOOKUP is a simple function that is easy to use and understand.
- Widely used: VLOOKUP has been a popular function in Excel for many years and is widely used in the industry.
- Stable: VLOOKUP has been around for a long time and is a stable function that is unlikely to change or be removed from Excel.
Drawbacks of VLOOKUP:
- Limited functionality: VLOOKUP can only look up values in the leftmost column of a table and return a corresponding value from the same row. This means that it cannot be used to look up values to the right of the lookup column.
- Exact match requirement: VLOOKUP requires an exact match between the lookup value and the value in the lookup column. This can make it difficult to use when dealing with data that contains variations or typos.
- Slow performance: VLOOKUP can be slow to calculate, especially when used with large data sets.
Examples of VLOOKUP:
Example 1: Looking up a Value in a Table
Suppose you have a list of products and their prices. You want to look up the price of a particular product.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product B, you can use the following formula:
=VLOOKUP(“B”,A2:B5,2,FALSE)
This formula looks up the value “B” in the range A2:B5 and returns the corresponding value from the second column. The result will be as follows:
| Price |
|---|
| 20 |
Example 2: Looking up a Value and Returning a Default Value
Suppose you have a list of products and their prices. You want to look up the price of a particular product, but you want to return a default value if the product is not found.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product E and return a default value if the product is not found, you can use the following formula:
=IFERROR(VLOOKUP(“E”,A2:B5,2,FALSE),”Product not found”)
This formula looks up the value “E” in the range A2:B5 and returns the corresponding value from the second column. If the value is not found, the formula returns the message “Product not found.” The result will be as follows:
| Result |
|---|
| Product not found |
Example 3: Looking up Values from a Different Sheet
Suppose you have a list of products and their prices on Sheet1, and you want to look up the prices on Sheet2.
Sheet1:
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
Sheet2:
| Product | Price |
|---|---|
| B | |
| C | |
| D | |
| E |
To look up the prices on Sheet2, you can use the following formula:
=VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE)
This formula looks up the value in cell A2 on Sheet2 in the range A2:B5 on Sheet1 and returns the corresponding value from the second column. The result will be as follows:
Sheet2:
| Product | Price |
|---|---|
| B | 20 |
| C | 30 |
| D | 40 |
| E |
XLOOKUP
XLOOKUP is a newer function in Excel that was introduced in Excel 365. XLOOKUP is a more versatile function than VLOOKUP and can perform more complex lookups. The syntax for XLOOKUP is as follows:
=XLOOKUP(lookup_value,lookup_array,result_array,[if_not_found],[match_mode],[search_mode])
Benefits of XLOOKUP:
- Versatility: XLOOKUP can perform a wide range of lookups, including looking up values to the left of the lookup column, returning multiple results, and returning the nth match.
- Simplicity: XLOOKUP is a simple function that is easy to use and understand.
- Dynamic arrays: XLOOKUP can return dynamic arrays, which means that it can return multiple results that automatically adjust as the data changes.
Drawbacks of XLOOKUP:
- Limited compatibility: XLOOKUP is only available in Excel 365, which means that it is not available in earlier versions of Excel.
- Complexity: While XLOOKUP is a more versatile function than VLOOKUP, it is also more complex, which can make it more difficult to use and understand.
- Performance: XLOOKUP can be slower to calculate than VLOOKUP, especially when used with large data sets.
Examples of XLOOKUP:
Example 1: Looking up a Value in a Table
Suppose you have a list of products and their prices. You want to look up the price of a particular product.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product B, you can use the following formula:
=XLOOKUP(“B”,A2:A5,B2:B5)
This formula looks up the value “B” in the range A2:A5 and returns the corresponding value from the range B2:B5. The result will be as follows:
| Result |
|---|
| 20 |
Example 2: Looking up a Value and Returning a Default Value
Suppose you have a list of products and their prices. You want to look up the price of a particular product, but you want to return a default value if the product is not found.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product E and return a default value if the product is not found, you can use the following formula:
=XLOOKUP(“E”,A2:A5,B2:B5,”Product not found”,0)
This formula looks up the value “E” in the range A2:A5 and returns the corresponding value from the range B2:B5. If the value is not found, the formula returns the message “Product not found.” The result will be as follows:
| Result |
|---|
| Product not found |
Example 3: Looking up Values to the Left of the Lookup Column
Suppose you have a list of products and their prices, and you want to look up the product name based on the price.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the product name for a price of 30, you can use the following formula:
=XLOOKUP(30,B2:B5,A2:A5)
This formula looks up the value 30 in the range B2:B5 and returns the corresponding value from the range A2:A5. The result will be as follows:
| Result |
|---|
| C |
Example 4: Returning Multiple Results
Suppose you have a list of products and their prices, and you want to look up all the products with a price greater than or equal to 20.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To return all the products with a price greater than or equal to 20, you can use the following formula:
=XLOOKUP({1,2,3,4},{1,2,3,4},A2:A5,,1,1)*(B2:B5>=20)
This formula returns a dynamic array of values that match the criteria. The result will be as follows:
| Result |
|---|
| B |
| C |
| D |
Example 5: Returning the Nth Match
Suppose you have a list of products and their prices, and you want to look up the second occurrence of a product.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 20 |
| D | 40 |
To look up the second occurrence of product C, you can use the following formula:
=XLOOKUP(“C”,A2:A5,B2:B5,,2)
This formula looks up the value “C” in the range A2:A5 and returns the second corresponding value from the range B2:B5. The result will be as follows:
| Result |
|---|
| 20 |
Conclusion
Both VLOOKUP and XLOOKUP are powerful functions in Excel that can be used to look up and retrieve data from a table. While VLOOKUP is a more straightforward function that is widely used and stable, XLOOKUP is a more versatile function that can perform more complex lookups. By comparing the benefits and drawbacks of each function and examining examples of each, you can choose the function that best suits your needs and data requirements.
