Master HLOOKUP and XLOOKUP in Excel with joboskill clear guide. Learn syntax, examples, and key differences to streamline your data lookup tasks. Perfect for all skill levels!
XLOOKUP – Flexible Lookup
Introduced in Excel 365, XLOOKUP is a powerful and versatile lookup function that can replace both VLOOKUP and HLOOKUP. It allows you to search a range or array, find the exact or closest match, and return a corresponding value. The syntax for XLOOKUP is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value to search for.
- lookup_array: The range or array to search.
- return_array: The range or array containing the values to be returned.
- if_not_found: Optional. The value to return if no match is found.
- match_mode: Optional. Specifies the type of match: 0 for an exact match, -1 for an exact or smaller match, 1 for an exact or larger match, and 2 for wildcard characters.
- search_mode: Optional. Specifies the search mode: 1 for the first-to-last search, -1 for the last-to-first search, and 2 for the binary search.
Example:
Suppose you have a list of student names in column A and their corresponding grades in column B. You want to find the grade for “John” using XLOOKUP:
=XLOOKUP(“John”, A1:A10, B1:B10, “Not Found”, 0, 1)
This formula searches for “John” in column A (A1 to A10) and returns the corresponding grade from column B. If no match is found, it returns “Not Found.”
HLOOKUP and XLOOKUP, are two additional lookup functions in Excel that complement VLOOKUP.
HLOOKUP – Horizontal Lookup
Similar to VLOOKUP, HLOOKUP (Horizontal Lookup) searches for a value in the first row of a table and returns a corresponding value in the same column from a specified row. The syntax for HLOOKUP is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value to search for in the first row of the table.
- table_array: The range of cells containing the data. The first row of this range is where Excel will search for the lookup value.
- row_index_num: The row number in the table from which to retrieve the value.
- range_lookup: Optional. If set to TRUE (or omitted), Excel will look for an approximate match. If set to FALSE, it will look for an exact match.
Example:
Suppose you have a table with product names in the first row (A1:D1) and corresponding prices in the rows below. You want to find the price of “Product B” in row 2. The formula would look like this:
=HLOOKUP(“ProductB”, A1:D10, 2, FALSE)
This formula searches for “Product B” in the first row (A1 to D1) and returns the corresponding price from the second row.
In summary, while VLOOKUP and HLOOKUP have their uses, XLOOKUP provides a more flexible and comprehensive solution for searching and retrieving data in Excel, making it a powerful tool for modern spreadsheet tasks.