[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

HLOOKUP

Syntax:

HLOOKUP ( lookup-value , table-array , row-index-num [ , [ range-lookup-flag ] ] )

Description: Performs a horizontal search for a value in the top row of a table or an array, noting the column in which the matching value is found. From that column, the value from a given row is returned.

Arguments:

Name

Type

Description

lookup-value

value of any type or a reference to a value of any type.

The value to be located in the first row of the table. If range-lookup is FALSE and lookup-value is a string, the wildcard characters, question mark (?) and asterisk (*), can be included in lookup-value. A question mark matches any single character; an asterisk matches any sequence of characters. To find a question mark or asterisk, type a tilde (~) before the character.

table-array

array, reference, name

Designates the table of information to be searched. The values in the first row of table-array can be text, numbers, or logical values. If range-lookup-flag is TRUE, the values in the first row of table-array shall be placed in "ascending order", as follows: ..., -2, -1, 0, 1, 2, ..., A–Z, FALSE, TRUE. If range-lookup-flag is FALSE, table-array's values need not be sorted. Uppercase and lowercase text is treated as equivalent.

row-index-num

number

The row number in table-array from which the matching value is to be returned. (A row-index-num of 1 returns the first row value in table-array, a row-index-num of 2 returns the second row value in table-array, and so on.)

range-lookup-flag

logical

Specifies whether HLOOKUP is to find an exact or approximate match. If TRUE or omitted, an approximate match is returned. That is, if an exact match is not found, the next largest value that is less than lookup-value is returned. If FALSE, an exact match is performed.

 

Return Type and Value: any – The value from a given row number, where the column is determined by a search of the top row looking for a match with a given value.

However, if

An exact match is performed, but no match is found, #N/A is returned.

row-index-num is less than 1, #VALUE! is returned.

row-index-num is greater than the number of rows in table-array, #REF! is returned.

lookup-value is smaller than the smallest value in the first row of table-array, #N/A is returned.

[Example: Given the following data:

 

A

B

C

1

Axles

Bearings

Bolts

2

4

6

9

3

5

7

10

4

6

8

11

 

HLOOKUP("Axles",A1:C4,2,TRUE) results in 4
HLOOKUP("Bearings",A1:C4,3,FALSE) results in 7
HLOOKUP("B",A1:C4,3,TRUE) results in 5
HLOOKUP("Bolts",A1:C4,4) results in 11
HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE) results in c

end example]