Excel is a popular spreadsheet software that has been widely used for decades. It has many features that allow users to manipulate and analyze data easily. Two of the most commonly used functions in Excel are XLOOKUP and HLOOKUP. These two functions are used to search for a value in a table and return the corresponding value in the same row or column. In this tutorial, we will compare XLOOKUP vs HLOOKUP and explain their differences using examples.
What is HLOOKUP in Excel?
HLOOKUP stands for Horizontal Lookup. It is a function used to search for a value in the top row of a table and return the corresponding value in the same column. The HLOOKUP function has four arguments:
- Lookup_value: This is the value that you want to find in the top row of the table.
- Table_array: This is the range of cells that contain the table.
- Row_index_num: This is the row number in the table from which you want to return a value.
- Range_lookup: This is an optional argument that specifies whether you want an exact match or an approximate match.
Example:
Suppose you have a table that shows the prices of different products in different regions. The table looks like this:
| Region | Product 1 | Product 2 | Product 3 |
|---|---|---|---|
| East | $10 | $12 | $15 |
| West | $11 | $13 | $16 |
| North | $12 | $14 | $17 |
| South | $13 | $15 | $18 |
If you want to find the price of Product 2 in the West region, you can use the HLOOKUP function as follows:
=HLOOKUP("West", A1:D5, 2, FALSE)Here, “West” is the lookup value, A1:D5 is the table array, 2 is the row index number (which corresponds to the row of Product 2), and FALSE indicates that an exact match is required. The function returns $13, which is the price of Product 2 in the West region.
What is XLOOKUP in Excel?
XLOOKUP is a new function introduced in Excel 2019. It is a versatile function that can be used to search for a value in any column or row of a table and return the corresponding value in any other column or row of the same table. The XLOOKUP function has five arguments:
- Lookup_value: This is the value that you want to find in the table.
- Lookup_array: This is the range of cells that contains the values that you want to search for.
- Return_array: This is the range of cells that contains the values that you want to return.
- If_not_found: This is an optional argument that specifies what to return if the lookup value is not found in the table.
- Match_mode: This is an optional argument that specifies the match mode.
Example:
Suppose you have the same table as in the HLOOKUP example above. If you want to find the price of Product 2 in the West region using XLOOKUP, you can use the following formula:
=XLOOKUP("West", A2:A5, B2:D5)Here, “West” is the lookup value, A2:A5 is the lookup array (which contains the region names), and B2:D5 is the return array (which contains the prices). The function returns $13, which is the price of Product 2 in the West region.
Difference between XLOOKUP and HLOOKUP:
- Lookup direction: The main difference between XLOOKUP and HLOOKUP is the direction of the lookup. HLOOKUP searches for the lookup value in the first row of the table and returns the corresponding value in the same column. XLOOKUP, on the other hand, can search for the lookup value in any column or row of the table and return the corresponding value in any other column or row of the same table.
- Arguments: XLOOKUP has more arguments than HLOOKUP. XLOOKUP requires a lookup array, a return array, and two optional arguments for if_not_found and match_mode. HLOOKUP, on the other hand, only requires a table array, a row index number, and an optional argument for range_lookup.
- Flexibility: XLOOKUP is more flexible than HLOOKUP. With XLOOKUP, you can search for a value in any column or row of a table and return the corresponding value in any other column or row of the same table. This makes it easier to use XLOOKUP for more complex lookup scenarios. HLOOKUP, on the other hand, can only search for a value in the top row of a table and return the corresponding value in the same column.
- Performance: XLOOKUP is generally faster than HLOOKUP, especially for large tables. This is because XLOOKUP uses a more efficient algorithm to search for values in a table.
Conclusion:
In summary, XLOOKUP and HLOOKUP are both useful functions in Excel for searching for a value in a table and returning the corresponding value. However, XLOOKUP is a more versatile and flexible function that allows you to search for a value in any column or row of a table and return the corresponding value in any other column or row of the same table. It also has better performance for large tables. HLOOKUP is still useful for simple lookup scenarios where you only need to search for a value in the top row of a table and return the corresponding value in the same column.