Important:LOOKUP
Description
LOOKUP searches for a value in one range and returns the contents of the corresponding position in a second range.
Important:
For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
If sorting data in ascending order is not possible, consider using the HLOOKUP, VLOOKUP, or MATCH functions.
Syntax
LOOKUP (lookup_value, lookup_range, result_range )
Parameter |
Description |
lookup_value |
The value for which to search in the first range. |
lookup_range |
The first range to search and contains only one row or one column. The range can contain numbers, text, or logical values. To search lookup_range correctly, the expressions in the range must be placed in ascending order (for example, -2, -1, 0, 1, 2...A through Z, False, True). The search is not case-sensitive. |
result_range |
A range of one row or one column that is the same size as lookup_range. |
Remarks
If lookup_value does not have an exact match in lookup_range, the largest value that is less than or equal to lookup_value is found and the corresponding position in result_range is returned. When lookup_value is smaller than the data in lookup_range, #N/A is returned.
Examples
The following examples use this worksheet.
This function returns Detroit:
LOOKUP("North", A2:A7, B2:B7)
This function returns #N/A:
LOOKUP("Alabama", A2:A7, B2:B7)
Note:
If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.
Also, see