Excel HLOOKUP function with formula examples

Microsoft Excel's HLOOKUP function: A comprehensive guide

Excel offers three lookup functions – LOOKUP, VLOOKUP, and HLOOKUP – but HLOOKUP often causes confusion. This tutorial clarifies HLOOKUP's specifics and provides examples for efficient use.

What is HLOOKUP?

HLOOKUP performs horizontal lookups. It searches for a value in a table's first row and returns a corresponding value from a specified row in the same column. Available in all Excel versions (2007 and later).

HLOOKUP Syntax and Usage

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value to search for (cell reference, number, or text).
• table_array: The data range (range, named range, or table) containing the lookup value in its first row.
• row_index_num: The row number within table_array from which to return the value (e.g., 2 for the second row).
• range_lookup: (Optional) Logical value specifying exact or approximate match. TRUE (or omitted) for approximate match; FALSE for exact match. Approximate match requires the first row of table_array to be sorted ascendingly.

Illustrative Example

Imagine a table with planetary data (see image below). To find a planet's diameter given its name in cell B5:

The formula would be:

=HLOOKUP(B5, B2:I3, 2, FALSE)

• lookup_value: B5 (planet name)
• table_array: B2:I3 (data table)
• row_index_num: 2 (diameter row)
• range_lookup: FALSE (exact match)

Key Considerations for HLOOKUP

1. HLOOKUP searches only the first row of table_array.
2. HLOOKUP is case-insensitive.
3. For approximate matches (TRUE or omitted), the first row must be sorted ascendingly.

HLOOKUP vs. VLOOKUP

Both search for values, but VLOOKUP searches vertically (leftmost column), while HLOOKUP searches horizontally (topmost row).

Advanced HLOOKUP Examples

• Approximate vs. Exact Match: The previous example showed an exact match. An approximate match (using TRUE or omitting the last argument) finds the closest value if an exact match isn't found (requires sorted data).

• Lookup from Another Worksheet/Workbook: Use external references. For example, from a sheet named "Diameters":

  =HLOOKUP(B$1, Diameters!$B$1:$I$2, 2, FALSE)

  For workbooks, include the path: =HLOOKUP(B$1, '[Book1.xlsx]Diameters'!$B$1:$I$2, 2, FALSE)

• Partial Match (Wildcards): Use ? (any single character) and * (any sequence of characters). For example:

  =HLOOKUP("ace*", B1:I2, 2, FALSE) (finds values starting with "ace")

Cell References in HLOOKUP

Use absolute references ($) for table_array to prevent changes when copying the formula. lookup_value references are usually relative or mixed depending on the context.

INDEX/MATCH: A Superior Alternative

INDEX/MATCH offers greater flexibility than HLOOKUP, allowing lookups from any row or column. A general formula is:

INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Case-Sensitive HLOOKUP

HLOOKUP is not case-sensitive. For case sensitivity, use an array formula combining INDEX, MATCH, and EXACT:

{=INDEX(B2:I2, MATCH(TRUE, EXACT(B1:I1,B4),0))} (Ctrl Shift Enter to enter as an array formula)

Troubleshooting HLOOKUP Errors

Common issues include:

1. Looking above the first row.
2. Incorrect range_lookup usage.
3. table_array reference changes upon copying.
4. Row insertions/deletions affecting row index numbers.
5. Duplicate values in the lookup range.
6. Extra spaces in data.
7. Numbers formatted as text.
8. Lookup values exceeding 255 characters.
9. Missing full path to external workbooks.
10. Incorrect arguments.

This comprehensive guide should help you effectively utilize the HLOOKUP function in Excel. Remember that INDEX/MATCH offers a more robust and flexible solution for many lookup scenarios.

The above is the detailed content of Excel HLOOKUP function with formula examples. For more information, please follow other related articles on the PHP Chinese website!