HLOOKUP

Description

This function performs a horizontal lookup by searching for a search key value in the first row of the input range and returning the value of a specified cell from the column where it finds the key. An error will be shown if the key doesn’t exist.

Sample Usage

HLOOKUP(10003, A2:Z6, 2, FALSE)

Syntax

HLOOKUP(search_key, range, index, [is_sorted])

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first row in the range is searched for the key specified insearch_key.
index - The row index of the value to be returned, where the first row in range is numbered 1.
- If index is not between 1 and the number of rows in range, #VALUE! is returned.
is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the row to be searched (the first row of the specified range) is sorted.
- If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search row are greater than the search key, #N/A is returned.
- If is_sorted is set to TRUE or omitted, and the first row of the range is not in sorted order, an incorrect value might be returned.
- If is_sorted is FALSE, only an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.

How to use HLOOKUP

To use the HLOOKUP Formula, start with the Excellentable you would like to edit.

Then type HLOOKUP( into the cell you have chosen to display the outcome:

Enter values for the four required parameters: search_key, range, index, [is_sorted]

When you hit Enter Excellentable will retrieve the lookup value:

A

B

C

D

E

1

State

Alabama

Alaska

Arizona

Arkansas

2

Population

4833722

735132

6626624

2959373

38332521

3

Total area in mi2

52,420 (135,767)

665,384 (1,723,337)

113,990 (295,233)

53,179 (137,733)

4

Land area in mi2

50,645 (131,170)

570,641 (1,477,950)

113,594 (294,207)

52,035 (134,770)

5

Water area in mi2

1,775 (4,597)

94,743 (245,383)

396 (1,026)

1,143 (2,960)

6

House seat(s)

7

1

9

4

7

	F	G	H	I	J
1	California	Colorado	Connecticut	Delaware	Florida
2

5268367

3596080

925749

19552860

9992167

3

163,695 (423,968)

104,094 (269,602)

5,543 (14,356)

2,489 (6,446)

65,758 (170,312)

4

155,779 (403,466)

103,642 (268,432)

4,842 (12,541)

1,949 (5,048)

53,625 (138,888)

5

7,916 (20,502)

452 (1,171)

701 (1,816)

540 (1,399)

12,133 (31,424)

6

53

7

5

1

27

7

9

	K	L	M
1	Georgia	Hawaii	Idaho
2

1404054

1612136

3

59,425 (153,910)

10,932 (28,314)

83,569 (216,443)

4

57,513 (148,958)

6,423 (16,635)

82,643 (214,044)

5

1,912 (4,950)

4,509 (11,678)

926 (2,398)

6

14

2

7

Notes

When searching for numeric or date values, make sure that the first row in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
Search keys based on regular expressions or wildcard patterns are NOT supported. Use QUERY instead.
HLOOKUP has much better performance with sorted ranges and is_sorted set to TRUE. Consider sorting the row being searched.