Leading the way in Microsoft Office Development

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

 -

HLOOKUP worksheet function

Returns the value in a row that matches a value in the top row of a table

This function looks up a value in the first row and returns the value in the same column but in a different row.

The "range_lookup" argument is very important as this determines if an exact match or an approximate match is found.

If "range_lookup" = False then only exact matches are returned.

If "range_lookup" = False and there is no match then, #N/A! is returned.

If "range_lookup" = True then approximate values will be returned if no match is found.

If "range_lookup" = True then the first column of your table should be sorted in ascending order.

Looking up a value that exists (e.g. "John")

This example looks up the value "Mark" (in the first column) and returns the corresponding value from column "B".

Mark exists in the first row so the "range_lookup" argument is irrelevant.

When the "range_lookup" argument is False, the function returns the correct value.

In this case "Fidler" is returned.

When the "range_lookup" argument is True, the function returns the correct value.

In this case "Fidler" is returned.

Looking up a value that does not exist (e.g. "Richard")

This example tries to look up the value "Richard" (in the first column) and returns the corresponding value from column "B".

Richard does not exist in the first column so the value that is returned depends on whether "range_lookup" is True or False.

When the "range_lookup" argument is False, the function will only return exact matches.

In this case #N/A! is returned.

When the "range_lookup" argument is True, the function will find return the nearest matching value.

In this case "Ellis" is returned because Paul is the nearest value that is less than Richard.

Things to Remember

This is identical to the VLOOKUP() function, except that is works with a horizontal table and not a vertical table.

Your data table does not have to contain row labels.

When no match is found and you want to return approximate values, the first row of your table must be sorted in ascending order.

This function is not case sensitive when searching for text strings.

This function can be used in place of several IF() functions.

You cannot use this function to lookup a value based on both the row and the column headers. This can be done using a combination of the OFFSET() and MATCH() functions which is described in the Cross Reference page of this section.