Excel HLOOKUP Function

This Excel Tutorial demonstrates how to use the Excel HLOOKUP Function in Excel to look up a value, with formula examples.

HLOOKUP Function Description:

The HLOOKUP Function Hlookup stands for horizontal lookup. It searches for a value in the top row of a table. Then returns a value a specified number of rows down from the found value. It is the same as a vlookup, except it looks up values horizontally instead of vertically.

Formula Examples:

hlookup function examples

Syntax and Arguments:

The Syntax for the HLOOKUP Formula is:

Function Arguments ( Inputs ):

lookup_value – The value you want to search for.

table_array

row_index_num

range_lookup

Additional Notes

Use the HLOOKUP Function to perform a horiztonal lookup. If you’re already familiar with the VLOOKUP Function, an HLOOKUP works in exactly the same way except the lookup is performed horizontally instead of vertically. The HLOOKUP searches for an exact match (range_lookup = FALSE) or the closest match that is equal to or less than the lookup_value (range_lookup = TRUE, numeric values only) in the first row of the table_array. It then returns a corresponding value, n number of rows below the match.

When using an HLOOKUP to find an exact match, first you define an identifying value that you want to search for as the lookup_value. This identifying value might be a SSN, employee ID, name, or some other unique identifier.

Next you define the range (called the table_array) that contains the identifiers in the top row and whatever values that you ultimately wish to search for in the rows below it. IMPORTANT: The unique identifiers must be in the top row. If they are not, you must either move the row to the top, or use MATCH / INDEX instead of the HLOOKUP.

Third, define row number (row_index) of the table_array that you wish to return. Keep in mind that the first row, containing the unique identifiers is row 1. The second row is row 2, etc.

Last, you must indicate whether to search for an exact match (FALSE) or nearest match (TRUE) in the range_lookup. If the exact match option is selected, and an exact match is not found, an error is returned (#N/A). To have the formula return blank or “not found”, or any other value instead of the error value (#N/A) use the IFERROR Function with the HLOOKUP.

To use the HLOOKUP Function to return an approximate match set: range_lookup = TRUE. This option is only available for numeric values. The values must be sorted in ascending order.

HLOOKUP Examples in VBA

You can also use the HLOOKUP function in VBA. Type:
application.worksheetfunction.hlookup(lookup_value,table_array,row_index_num,range_lookup)
For the function arguments (lookup_value, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

How to use the HLOOKUP Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
=AND(
After entering it in the cell, notice how the AND formula inputs appear below the cell:
hlookup formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=HLOOKUP(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the hlookup function in excel

For more information about the HLOOKUP Formula visit the
Microsoft Website.

ADS GO HERE

Posted in