[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
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 |
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]