Lookup and Matching Functions

Lookup functions allow you to search for values in a column or row, then return a corresponding value from another column or row. Matching functions allow you to search for values in a column or row, then return the column or row index.

Lookup Functions

HLOOKUP (search_item, search_range, column_index): A horizontal lookup function. Searches the first row of the  search_range provided for the search_item, then returns a corresponding value. Values in the first row of the search_range  should be sorted in ascending order. The row_index is the number of the row in the search_range from which this value will be returned.

LOOKUP (lookup_value, lookup_range, result_range): Searches for the lookup_value within the supplied lookup_range, then returns the corresponding value from the result_range. Values in the lookup_range should be sorted in ascending order. The lookup_range and result_range should be of the same dimensions.

VLOOKUP (search_item, search_range, column_index): A vertical lookup function. Searches the first column of the  search_range provided for the search_item, then returns a corresponding value. Values in the first column of the search_range should be sorted in ascending order. The column_index is the number of the column in the search_range from which this value will be returned.  

Examples:

 

A

B

C

1

Bob

30

50

2

George

70

25

3

Maria

50

32

 

Formula

Result

=LOOKUP("George", A1:A3, B1:B3)

70

=LOOKUP("Maria", A1:A3, C1:C3)

32

=VLOOKUP("George", A1:C3, 2)

70

=VLOOKUP("George", A1:C3, 3)

25

=VLOOKUP("Bob", A1:C3, 2)

30

 

Matching Functions

MATCH (lookup_value, lookup_range, comparison): Searches the lookup_range of cells given for the look_up value, then returns the corresponding column or row index. If comparison is set to 1, the largest value less than or equal to the lookup value will be found,k and the range must be sorted in ascending order. If comparison is set to -1, the smallest value greater than or equal to the lookup value will be found, and the range must be sorted in descending order. If comparison is set to 0, the first value equal to the lookup value will be found. If comparison is omitted, 1 will be used.

Examples:

 

A

1

Wage

2

$10.00

3

$15.00

4

$20.00

5

$25.00

 

Formula

Result

=MATCH(15, A2:A5)

2

=MATCH(15, A2:A5, 0)

2

=MATCH(17, A2:A5, 1)

2

=MATCH(17, A2:A5, 0)

#N/A (no exact match for 17)

=MATCH(17, A2:A5, -1)

#N/A (data must be in descending order)

=MATCH(21, A2:A5, 1)

3