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