HLOOKUP Function in Excel

While working with Excel, quiet often you will feel the need the search a table in excel and get some value from it.  There are few in-built functions (LOOKUP, HLOOKUP and VLOOKUP) provided by Excel to serve those needs. Each one of these function have their own usability. I will be covering HLOOKUP in this tutorial

HLOOKUP should be used when the table you are going to look-up is organised horizontally like the one below

Lets assume you wish to search this table using "Employee ID" and get the name of that employee. You can make use of HLOOKUP function to do so. Following is the syntax of the function and definition of the parameters

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

lookup_value: is the value to search for in the first row of the table_array.

table_array: is two or more rows of data which will be looked upon. If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

row_index_number: is the row number in table_array from which the matching value must be returned. The first row is 1.

[range_lookup]: It is an optional parameter with a default value of "true". If passed as "false", the function will look for an exact match of lookup_value in table_array.  If "true" or omitted, the function will look for an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.

Let back to where we started and let me explain these with the help of a few examples. Lets assume you want to look up employee id in cell A6 and get the name in B6. Type the following formula in B6 

=HLOOKUP(A6,B1:E3,2,FALSE)

and hit "enter", you will get the employee name in cell B6.

Other examples of how HLOOKUP works

HLOOKUP(A6,B1:E3,2,TRUE) will return "Employee 2" when value of A6 is 11128

HLOOKUP(A6,B1:E3,2,FALSE) will return "#N/A" when value of A6 is 11128. Other possible errors returned by this function are explained below with their reason.

HLOOKUP(A6,B1:E3,3,FALSE) will return "Technical"

Possible Error returns from HLOOKUP

• If row_index_number is less than 1, the HLOOKUP function will return #VALUE!.
• If row_index_number  is greater than the number of rows in table_array, the HLOOKUP function will return #REF!.
• If you enter FALSE for the not_exact_match parameter and no exact match is found, then the HLOOKUP function will return #N/A.