What is the Microsoft Excel
HLOOKUP Function?
The HLOOKUP function is a computer
program that you run from a worksheet cell formula. It looks up
a value you designate in the first row of a worksheet area
(called a range) and returns a
value from the column it finds the lookup value in. You run the
HLOOKUP worksheet function by typing its name in a formula then
followed by the information it is suppose to use. The HLOOKUP
worksheet function is used in applications when you have to look
up prices, name information, scientific data anything that is
contained in a table. It can also be used to merge two tables
together transferring information from one to the other. The H
in HLOOKUP stands for horizontal. There is also another function
called VLOOKUP that looks along the first column of a table. Use
VLOOKUP when your comparison values are located in the first
column of the data that you want to find. Note that this
function also has the capability to search for parts of text
within other text when evaluating its lookup value.
How Do You Type the HLOOKUP
Worksheet Function in a Formula?
Whenever you type a formula in a
worksheet cell, this is called syntax or grammar. The general
HLOOKUP function syntax has a format like this when you type it
in a worksheet cell:
= HLOOKUP( lookup_value, table_array,
row_index_num, [range_lookup] )
Where lookup_value, table_array,
row_index_num, [range_lookup] are called the function argument
list. Remember, you are running a computer program at this point
so the program needs information to operate and that is why
there is an argument list. When you see an argument list and you
see square brackets [ ] around the argument name, this means the
argument is optional and you do not have to include it when
typing unless you need it. So for the syntax above, you need to
include 3 arguments for the HLOOKUP worksheet function when
typing it in a worksheet cell formula in order for it to
calculate correctly. What argument values can be used are
discussed below. Remember functions expect certain things in
their argument lists, if you do not put the correct information
in the list they will generate an error when run.
VLOOKUP Worksheet Function
Argument Definitions
 A constant, cell reference,
formula or function that generates the value to search for
in the first row of your data. Be sure there are no repeated
values in this row. If there are, HLOOKUP will lock onto the
first one encountered and ignore the rest. The values
searched for in the first row can be text, numbers or
logical values. Remember a date value must be converted to a
number (see DATEVALUE function) before using, you cannot
just type 1/1/2012. If text is present, they are not case
sensitive. If searching for a text value, you can use wild
card characters like ? and * to go after a specific
character or a group of characters. You can also use the
ampersand (&) to help concatenate the lookup value on the
fly. Be sure to surround text lookup values with quotes " ",
numeric lookup values do not take quotes.
 table_array: A range
reference, formula or function that generates a range of two
or more rows of data for example A1:B2. The INDIRECT
function can be used to generate a range.
 row_index_num: A constant
number, cell reference, formula or function that generates
the row number in the column that contains the value you
want to return. For example, say you are looking up a value
in the first row of a table, then in that column where the
value is found you want to return the third row down from
the first row, then you would generate the value 3 for this
argument. Note that if you do not indicate the proper row
say, 4 instead of 3 is generated and there are only 3 rows
of data, an error will be returned.
 range_lookup
(Optional): HLOOKUP can find an absolute match or
approximate match in the first row of data it is looking in.
Use TRUE for this argument if you want an approximate match
or FALSE if you want an absolute match.
 If you use TRUE or do
not include it, an exact or approximate match is
returned. If an exact match is not found, the next
largest value that is less than lookup_value is
returned. The values in the first row of your data must
be placed in ascending sort order; otherwise, HLOOKUP
may not give the correct value. You can put the values
in ascending order by sorting the data.
 If you use FALSE,
HLOOKUP will only find an exact match. In this case, the
values in the row column of data do not need to be
sorted. If there are two or more values in the first
column that match the lookup_value argument, the first
value found is used. If an exact match is not found, the
error value #N/A is returned.
How Do You Run the HLOOKUP
Function?
Since the HLOOKUP function is a computer
program, it runs when you press Enter to enter the formula that
contains it. If any of the arguments are wrong, the function
will return an error.
What Do I Type for a HLOOKUP
Function Argument?
When typing the HLOOKUP function in a
worksheet cell formula, you need to replace the argument list
with arguments separating each one with a comma (,). Some
typical arguments you can use are:
Cell Formula 
Explanation 
= HLOOKUP( "Widgets", A1:D5, 2, FALSE ) 
Find Widgets in the A1:D1 range then return information
from row 2 in the column that Widgets was found in 
= HLOOKUP( "*ST*", G1:J5, 4, FALSE ) 
Find the piece of text ST inside the cell value text in
the G1:J1 range then return information from row 4 in
the column that ST was found in 
= HLOOKUP( "*" & B1 & "*", G1:J5, 2, FALSE ) 
Find the piece of text from the B1 cell value inside the
text in the G1:J1 range then return information from row
2 in the column that the text was found in 
= HLOOKUP( 16, B1:D5, 5, FALSE ) 
Find number 16 in the B1:D1 range then return
information from row 5 in the column that 16 was found
in 
= HLOOKUP( B1, Prices, 3, FALSE ) 
Find the B1 cell value in range name Prices first row
then return information from row 3 in the column that
the B1 cell value was found in * 
* This function is used a great deal in
building adaptive formulas that figure out ranges, start and
stop of data.... It is also heavily used in dashboard design
tying numeric ActiveX control outputs into formulas that seek
out data in tables.
Additional VLOOKUP Function
Examples
The worksheet seen below contains some
typical worksheet formulas that run the VLOOKUP worksheet
function. Pay close attention to the argument list and the
syntax used to write the formula.

A 
B 
C 
1 
Searching by Names 

2 



3 
Item 
Cost 

4 
Widget1 
$1.25 

5 
Widget2 
$2.50 

6 
Widget3 
$3.50 

7 



8 


Look up Cost header and
return info about Widget1 cost. Formula below 
9 
Widget1 
$1.25 
= HLOOKUP( "Cost", A3:B6, 2,
FALSE ) 
10 



11 
Item 
Cost 
Allow user to search for
header Cost and return info about Widget1 cost. Formula
below 
12 
Widget1 
$1.25 
= HLOOKUP( B11, A3:B6, 2,
FALSE ) 
13 



14 
Item 
Cost 
Allow user to search for Cost
header, use range name Widgets in place of A3:B6 much
easier, can be on different sheet. Formula below 
15 
Widget1 
$1.25 
= HLOOKUP( B14, Widgets, 2,
FALSE ) 
16 



Things to Know About the HLOOKUP
Function
 When searching text values
in the first row of your data, ensure that the data in the
first row does not have leading spaces, trailing spaces,
inconsistent use of straight ( ' or " ) and curly ( ‘ or “)
quotation marks, or nonprinting characters. In these cases,
HLOOKUP may give an incorrect or unexpected value. For more
information on functions that you can use to clean text
data, see the Text functions.
 When searching for number or
date values, ensure that the data in the first row is not
stored as text values. In this case, HLOOKUP may give an
incorrect or unexpected value. For more information, see
Convert numbers stored as text to numbers in help.
 If the range_lookup argument
is FALSE and the lookup_value argument is text, then you can
use the wildcard characters, question mark (?) and asterisk
(*), in lookup_value. A question mark matches any single
character; an asterisk matches any sequence of characters.
If you want to find an actual question mark or asterisk,
type a tilde (~) preceding the character.
