HLOOKUP

The HLOOKUP function is used to search for a value in the top row of a table or an array and return a value in the same column from a specified row. It is handy for looking up data in horizontal headers.

Syntax

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

lookup_value The value to search for in the top row of the table.
table_array The range of cells that contains the data to be searched.
row_index_num The row number in the table from which to retrieve the matching value.
range_lookup (Optional) A logical value that specifies whether to find an exact match. TRUE for approximate match and FALSE for exact match. Defaults to TRUE if omitted.

About HLOOKUP

In the expansive landscape of Excel functions, HLOOKUP emerges as a stalwart tool for searching and fetching data from horizontal tables or arrays. This function proves to be indispensable for tasks demanding the retrieval of specific details aligned with a particular criteria across rows within a dataset. Whether navigating financial reports, inventory listings, or project timelines, HLOOKUP streamlines the process by pinpointing and extracting relevant information effectively and efficiently.

Examples

Suppose you have a table with student grades arranged horizontally and you want to find the grade of a student named 'Alice'. The HLOOKUP formula would be: =HLOOKUP('Alice', A1:D6, 3, FALSE)

Imagine you have a price list with items listed horizontally, and you wish to retrieve the price of an item named 'Chair'. The HLOOKUP formula would be: =HLOOKUP('Chair', A1:D4, 2, TRUE)

Questions

How does the HLOOKUP function differ from VLOOKUP?

The key distinction between HLOOKUP and VLOOKUP lies in the orientation of the lookup table. HLOOKUP searches for a value in the top row of a table and retrieves a value from the specified row, while VLOOKUP looks for a value in the leftmost column of a table and fetches a value from the specified column.

Can I use wildcard characters with the HLOOKUP function?

No, the HLOOKUP function does not support wildcard characters for matching patterns. It performs exact or approximate matches based on the range_lookup parameter specified.

Is it possible to nest HLOOKUP functions within other functions or formulas?

Yes, you can nest HLOOKUP functions inside other functions or formulas in Excel to create more complex lookup scenarios. This allows for dynamic data retrieval based on specific criteria or conditions.

What happens if the lookup_value is not found in the top row of the table_array?

If the lookup_value is not found in the top row of the table_array and range_lookup is set to TRUE for an approximate match, HLOOKUP will return the next largest value less than the lookup_value. If range_lookup is set to FALSE for an exact match, HLOOKUP will return an error (#N/A).

Related functions

VLOOKUP