Excel has several lookup functions that allow users to search for specific values in a table and return corresponding values from the same or another table. Three popular lookup functions are XLOOKUP, VLOOKUP, and HLOOKUP. In this tutorial, we will explain the differences between these functions and provide examples of how to use them in Excel.
- VLOOKUP (Vertical Lookup)
The VLOOKUP function searches for a value in the leftmost column of a table and returns a value in the same row from a column that we specify. This function is useful when we have a table with vertical columns, and we want to find specific values in the first column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value we want to look up in the first column of the table.
- table_array: The table or range of cells that we want to search.
- col_index_num: The column number in the table from which we want to return a value. The leftmost column is column 1.
- range_lookup: Optional argument that specifies whether we want an exact or approximate match. A value of TRUE or omitted will result in an approximate match, while a value of FALSE will result in an exact match.
Example:
Suppose we have a table of sales data that looks like this:
| Product | Sales |
|---|---|
| A | 100 |
| B | 200 |
| C | 150 |
| D | 75 |
| E | 300 |
We want to find the sales value for Product B. To do this, we can use the VLOOKUP function as follows:
=VLOOKUP("B", A2:B6, 2, FALSE)
This formula will search for the value “B” in the first column of the table (A2:A6) and return the corresponding value in the second column (B2:B6), which is 200.
- HLOOKUP (Horizontal Lookup)
The HLOOKUP function works in a similar way to VLOOKUP, but it searches for a value in the top row of a table and returns a value from the same column in a row that we specify. This function is useful when we have a table with horizontal rows and want to find specific values in the top row.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value we want to look up in the top row of the table.
- table_array: The table or range of cells that we want to search.
- row_index_num: The row number in the table from which we want to return a value. The top row is row 1.
- range_lookup: Optional argument that specifies whether we want an exact or approximate match. A value of TRUE or omitted will result in an approximate match, while a value of FALSE will result in an exact match.
Example:
Suppose we have a table of student grades that looks like this:
| Math | Science | English | |
|---|---|---|---|
| Student1 | 80 | 90 | 85 |
| Student2 | 90 | 75 | 95 |
| Student3 | 70 | 85 | 80 |
| Student4 | 85 | 80 | 90 |
| Student5 | 95 | 90 | 85 |
We want to find the English grade for Student2. To do this, we can use the HLOOKUP function as follows:
=HLOOKUP("English", A1:D6, 2, FALSE)
This formula will search for the value “English” in the top row of the table (A1:D1) and return the corresponding value in the second row (A2:D2), which is 95.
- XLOOKUP
XLOOKUP is a more advanced lookup function that was introduced in Excel 365. It can perform both vertical and horizontal lookups, as well as search for values in any column of a table. It also allows users to specify a default value to return if the lookup value is not found. XLOOKUP is more versatile and flexible than VLOOKUP and HLOOKUP, and it can replace both functions in most cases.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value we want to look up in the lookup_array.
- lookup_array: The table or range of cells that we want to search for the lookup_value.
- return_array: The table or range of cells that contains the values we want to return. This can be in any column or row of the table.
- if_not_found: Optional argument that specifies the value to return if the lookup value is not found. This can be a value, formula, or reference to another cell. The default is #N/A.
- match_mode: Optional argument that specifies how the lookup_value should be matched. There are four options: 0 (exact match), -1 (exact match or next smallest), 1 (exact match or next largest), or 2 (wildcard match using ? and *).
- search_mode: Optional argument that specifies whether to search from the beginning or end of the lookup_array. There are two options: 1 (search from the beginning) or -1 (search from the end).
Example:
Suppose we have a table of employee data that looks like this:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1001 | John Doe | IT | 50000 |
| 1002 | Jane Doe | HR | 60000 |
| 1003 | Bob Smith | IT | 55000 |
| 1004 | Sue Brown | Sales | 45000 |
| 1005 | Tom Jones | Finance | 70000 |
We want to find the salary of the employee with ID 1003 in the same row. To do this, we can use the XLOOKUP function as follows:
=XLOOKUP(1003, A2:A6, D2:D6)
This formula will search for the value 1003 in the first column of the table (A2:A6) and return the corresponding value in the fourth column (D2:D6), which is 55000.
We can also use XLOOKUP to search for values in any column of the table. For example, suppose we want to find the name of the employee with the highest salary. To do this, we can use the MAX function to find the highest salary, and then use XLOOKUP to find the corresponding name:
=XLOOKUP(MAX(D2:D6), D2:D6, B2:B6)
This formula will search for the highest value in the fourth column of the table (D2:D6), which is 70000, and return the corresponding value in the second column (B2:B6), which is Tom Jones.
Conclusion:
In summary, VLOOKUP, HLOOKUP, and XLOOKUP are useful functions for searching for specific values in a table and returning corresponding values. VLOOKUP and HLOOKUP are older functions that are still widely used, but
XLOOKUP is a more advanced and flexible function that can replace both VLOOKUP and HLOOKUP in most cases. It can perform both vertical and horizontal lookups, as well as search for values in any column of a table. It also allows users to specify a default value to return if the lookup value is not found.
When choosing between VLOOKUP, HLOOKUP, and XLOOKUP, consider the following factors:
- Lookup direction: VLOOKUP searches for values in the first column of a table and returns corresponding values in a specified column to the right. HLOOKUP searches for values in the first row of a table and returns corresponding values in a specified row below. XLOOKUP can perform both vertical and horizontal lookups, and can search for values in any column or row of a table.
- Flexibility: XLOOKUP is the most flexible function, as it can perform both vertical and horizontal lookups, and can search for values in any column or row of a table. It also allows users to specify a default value to return if the lookup value is not found.
- Compatibility: VLOOKUP and HLOOKUP are older functions that are supported in most versions of Excel, including older versions. XLOOKUP was introduced in Excel 365, so it may not be available in older versions of Excel.
By understanding the differences between VLOOKUP, HLOOKUP, and XLOOKUP, you can choose the right function for your specific needs and make your Excel tasks more efficient and accurate.