HLOOKUP, short for Horizontal Lookup, is a function used in Excel and Google Sheets to search for data across the first row of a table and return a corresponding value from a different column in the same row.
Here’s a breakdown of HLOOKUP and its components:
Function Syntax:
=HLOOKUP(lookup_value, table_array, row_index_number, [is_sorted])
Arguments:
lookup_value: The value you want to find in the first row of the table. This can be a text string, number, or a reference to another cell.table_array: The range of cells containing the data you want to search. This includes the first row with header information and the data itself.row_index_number: The row number within the table_array from which you want to return the value. The first row (containing the lookup values) is considered row 1.[is_sorted]: (Optional) A logical value indicating whether the first row of the table_array is sorted in ascending order. TRUE for sorted, FALSE for not sorted (default).
Using HLOOKUP:
- Identify your data:
- Ensure your data is organized in a table format with the lookup values in the first row.
- Insert the formula:
- Click on the cell where you want the result to appear.
- Type
=HLOOKUP(and enter the arguments as described above.
- Complete the formula:
- Close the parenthesis after each argument and press Enter.
- Excel/Google Sheets will search for the lookup_value in the first row and return the value from the specified row_index_number.
Important Notes:
- If the lookup_value is not found, HLOOKUP returns an #N/A error.
- Using the
is_sortedargument with TRUE can improve performance if your data is indeed sorted in ascending order. - Be cautious with relative vs. absolute cell references when copying the formula across cells.
HLOOKUP vs. VLOOKUP:
HLOOKUP searches horizontally across rows, while VLOOKUP searches vertically down a column. Choose the appropriate function based on how your data is organized.