LOOKUP
Formulas / LOOKUPSearches a one-column range for a specific value
LOOKUP(lookup_value, lookup_vector, [result_vector])
- lookup_value - the value to find
- lookup_vector - a range to search (one row or one column)
- result_vector - [OPTIONAL] a range of results (one row or one column); if not specified, LOOKUP returns the value in lookup_vector
Examples
=LOOKUP(F4,B5:B9)The LOOKUP function can be used to find a match in a range of data. This example will return the value of the match found in column B.
=LOOKUP(F4,B5:B9,C5:C9)The LOOKUP function can also be used to return the corresponding tier from a column. For example, this formula will return the corresponding tier from column C.
=LOOKUP(2,1/(B:B<>""),B:B)The LOOKUP function can also be used to return the last non-empty cell in a column by using a full column reference. For example, the formula above will return the last non-empty cell in a column.
=LOOKUP(2,1/(item=F5),price)The LOOKUP function can also be used to return the latest price in data that is ordered by ascending date. This example, will return the latest price in data that is ordered by ascending date.
Summary
The LOOKUP function is a lookup function that searches for a value in a range by using the same position in another range.
- The LOOKUP function has both array and vector forms and may use a full column as a reference.
- Microsoft suggests using VLOOKUP or HLOOKUP for the array form over using the LOOKUP function.
- LOOKUP assumes ascending order sorting for the lookup_vector and finds the next smallest value if the lookup_value is not found.
- LOOKUP returns #N/A when lookup_value is greater than every lookup_vector value.
Frequently Asked Questions
What is the LOOKUP function?
What does the LOOKUP function return if it can't find the requested value?
Can the LOOKUP function look in multiple rows and columns?
Is the LOOKUP function case-sensitive?
What types of values can the LOOKUP function take as arguments?
What does the LOOKUP function return?
What are some other lookup functions?
