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 |