How To Use HLOOKUP In Microsoft Excel?

“HLOOKUP (Horizontally lookup) function of Microsoft excel looks up a given value in the top row of a table and returns the corresponding value from another row. "

The Parameters used in the HLOOKUP function are:

1.lookup_value – The value which needs to be searched in the provided excel table (it should be from the first row of the supplied table or data array)
2.table_array – The data array or table, containing the data to be searched in the top row, and the return values in any other row.
3.row_index_num – The row number within the selected table range or table array from which the corresponding value will be returned as HLOOKUP output.
4.[range_lookup] – An optional argument in the form of TRUE/FALSE
1.Select TRUE in range_lookup parameter if you want search approximate value or closest match is HLOOKUP couldn’t find the exact match. Remember, if you are selecting range_lookup as true then the value in the first/top row of the table_array must be sorted in ascending order.
2.Select FALSE if you are passing the value which has the exact match in the table_array and if the function cannot find an exact match to the supplied lookup_value, it will return an error.
“If [range_lookup] is TRUE or omitted, an approximate match is returned.” Combining all together, we get complete HLOOKUP formula as below; HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Let’s see a simple example explaining the HLOOKUP

Below table shows the salary structure of employees in horizontal format.

Now we want to know the basic salary of employees like below;

Below formula will help us in retrieving the desired output: =HLOOKUP(A11,A1:E5,2,FALSE)

This formula will search the value present in cell “A11” in the table range A1:E5 and give the output from the second row if exact match found in the very first row of the table (in this case first column).


Common Errors in HLOOKUP :

1.#N/A – It occurs if the Hlookup function fails to find a match to the supplied lookup_value in selected table_array
Possible reasons could be:
    1.You have selected [range_lookup] = TRUE or [range_lookup] = nothing and the smallest value in the lookup row is greater than the supplied lookup_value.
      2.You have selected [range_lookup] = FALSE but HLOOKUP didn’t find any exact match for the supplied lookup value within provided table_array.
2.#REF! – Occurs if the supplied row_index_num argument is greater than the number of rows in the supplied table_array.
3.#VALUE! -This error occurs if:
    1.The supplied row_index_num argument is < 1 or is non-numeric
    2.The supplied [range_lookup] argument is not recognized as TRUE or FALSE.

Passing wildcard characters in HLOOKUP as lookup_value:

If you have selected [range_lookup] as FALSE and your [lookup_value] is text, you can use the below wildcard characters.

  ? – Question mark– If you want to search a single character
  * – Asterisk mark – If you want to search a sequence of characters.

Note: If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Below screen demonstrates the uses of wildcard characters as lookup_value in HLOOKUP formula