LOOKUP

Description

Looks through a sorted row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column.

This very similar to the VLOOKUP and HLOOKUP formulas, but with two notable differences; LOOKUP expects a sorted column or row to search, and while the VLOOKUP or HLOOKUP formulas can only search on columns and rows respectively, the LOOKUP formula can handle either of them.

Syntax

LOOKUP(search_key, search_range|search_result_array, [result_range])

search_key - The value to search for in the row or column. For example, 42, "Cats", or I24.
search_range|search_result_array - One method of using LOOKUP is to provide a single row or column search_range to look through for the search with a second argument result_range. The other way is to combine these two arguments into one search_result_array where the first row or column is searched and a value is returned from the last row or column in the array.
result_range - [ OPTIONAL ] - The range from which to return a result. The value returned corresponds to the location where search_key is found insearch_range. This range must be only a single row or column and should not be used if using the search_result_array method.

Sample Usage

LOOKUP(10003, A1:A100, B1:B100)

LOOKUP(10003, A1:B100)

LOOKUP("foo", A1:Z10)

How to use LOOKUP

In order to use the LOOKUP formula, start with your edited Excellentable:

Then type in the LOOKUP Formula in the area you would like to display the outcome:

By adding the values you would like to calculate the LOOKUP formula for, Excellentable will generate the outcome:

A

B

C

D

1

State

Population

Total area in mi2

Land area in mi2

Water area in mi2

House seat(s)

2

Alabama

4833722

52,420 (135,767)

50,645 (131,170)

3

Alaska

735132

665,384 (1,723,337)

570,641 (1,477,950)

4

Arizona

6626624

113,990 (295,233)

113,594 (294,207)

5

Arkansas

2959373

53,179 (137,733)

52,035 (134,770)

6

California

38332521

163,695 (423,968)

155,779 (403,466)

7

Colorado

5268367

104,094 (269,602)

103,642 (268,432)

8

Connecticut

3596080

5,543 (14,356)

4,842 (12,541)

9

Delaware

925749

2,489 (6,446)

1,949 (5,048)

10

Florida

19552860

65,758 (170,312)

53,625 (138,888)

11

Georgia

9992167

59,425 (153,910)

57,513 (148,958)

12

Hawaii

1404054

10,932 (28,314)

6,423 (16,635)

13

Idaho

1612136

83,569 (216,443)

82,643 (214,044)

14

15

	E	F
1

2

1,775 (4,597)

7

3

94,743 (245,383)

1

4

396 (1,026)

9

5

1,143 (2,960)

4

6

7,916 (20,502)

53

7

452 (1,171)

7

8

701 (1,816)

5

9

540 (1,399)

1

10

12,133 (31,424)

27

11

1,912 (4,950)

14

12

4,509 (11,678)

2

13

926 (2,398)

2

14

15

27

Notes

The LOOKUP function will only work properly if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other related functions if data is not sorted.

If search_key is not found, the item used in the lookup will be the value that's immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key is 2, then 1 will be used for the lookup.

In the search_result_array method, the last row or column in the provided range is returned. If a different row or column is desired, use VLOOKUP or HLOOKUP instead.

When using the search_result_array method, if the range provided contains more columns than rows, then the search will be from left to right over the first row in the range. If the range contains an equal number of rows and columns or more rows than columns, then the search will be from top to bottom over the first column in the range.