Related Functions:

The HLOOKUP function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.

### Syntax

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

Note: Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

#### Arguments

Argument Description
lookup_value The value to be found in the first row of the table – lookup_value can be a value, a reference, or a text string
table_array A table of information in which data is looked up – use a reference to a range or a range name

 • The values in the first row of table_array can be text, numbers, or logical values If [range_lookup] is TRUE, the values in the first row of table_array must be placed in ascending order: … -2, -1, 0, 1, 2 ,… , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If [range_lookup] is FALSE, table_array does not need to be sorted table_array is not case sensitive, e.g. “Text” is the same as “text” Sort the values in ascending order, left to right
row_index_num The row number in table_array from which the matching value will be returned
[range_lookup] Optional. A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match

[range_lookup] Meaning
TRUE
(or omitted)
if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value. Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order
FALSE if the function cannot find an exact match to the supplied lookup_value, it should return an error

#### Examples

A B C D E F G H I J K
1 Exam Results:
2   Andy Dara Steve Glen Mark Jill Tim Jack
3 Science 64 75 82 65 65 42 56 78
4 Maths 87 52 68 32 72 60 61 49
5 English 79 40 61 46 66 71 56 89
6 French 39 55 77 68 45 42 72 65
7
8 Individual Student Score:
9 Student: French:   Formula Result Notes
10 Glen 68   =HLOOKUP(A10,A2:I6,5,FALSE) 68 HLOOKUP searches through the top row of the table_array (range A2-I2), to find a match for the lookup_value (“Glen”). When the the lookup_value is found, the function returns the corresponding value from the 5th row of the table_array

Usage note: In text-related HLOOKUPs, the lookup_value can contain the following wildcard characters:

 ? – matches any single character * – matches any sequence of characters

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

#### Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

 • the supplied row_index_num argument is < 1 or is not recognized as a numeric value • the supplied [range_lookup] argument is not recognized as TRUE or FALSE
#N/A Occurs if the HLOOKUP function fails to find a match to the supplied lookup_value, which depends on the supplied [range_lookup]:

 • [range_lookup] = TRUE(or is omitted) – the supplied lookup_value is < the smallest value in the lookup row • [range_lookup] = FALSE an exact match to the lookup_value is not found in the lookup row
#REF! Occurs if the supplied row_index_num argument is greater than the number of rows in the supplied table_array