How to Use LOOKUP in Excel: Syntax, Examples, and Tips
The
LOOKUP function in Excel searches for a value in a range or array and returns a corresponding value from another range. It requires a lookup value and a lookup vector or array, and it works best with sorted data for accurate results.Syntax
The LOOKUP function has two main forms: vector form and array form.
- Vector form:
LOOKUP(lookup_value, lookup_vector, result_vector) - Array form:
LOOKUP(lookup_value, array)
In vector form, lookup_value is the value to find, lookup_vector is the range to search, and result_vector is the range to return a value from. Both vectors must be the same size.
In array form, array is a two-row or two-column range where the function searches the first row or column for lookup_value and returns the corresponding value from the second row or column.
excel
LOOKUP(lookup_value, lookup_vector, result_vector) LOOKUP(lookup_value, array)
Example
This example shows how to find a price for a product code using the vector form of LOOKUP.
excel
A1:A5 contains product codes: 101, 102, 103, 104, 105 B1:B5 contains prices: 10, 15, 20, 25, 30 Formula in C1: =LOOKUP(103, A1:A5, B1:B5)
Output
20
| Product Code | Price |
|---|---|
| 101 | 10 |
| 102 | 15 |
| 103 | 20 |
| 104 | 25 |
| 105 | 30 |
Common Pitfalls
- Unsorted lookup_vector:
LOOKUPexpects the lookup vector to be sorted in ascending order; otherwise, it may return incorrect results. - Exact match not guaranteed:
LOOKUPfinds the largest value less than or equal to the lookup value, so it may not find an exact match. - Vector size mismatch: The lookup and result vectors must be the same size or the function will return an error.
excel
=LOOKUP(106, A1:A5, B1:B5) // Returns 30 because 106 is not found, returns last smaller value =LOOKUP(103, A1:A4, B1:B5) // Error due to vector size mismatch
Quick Reference
| Parameter | Description |
|---|---|
| lookup_value | Value to search for |
| lookup_vector | Range to search in (must be sorted) |
| result_vector | Range to return value from (same size as lookup_vector) |
| array | Two-row or two-column range for array form |
Key Takeaways
Use LOOKUP to find a value in one range and return a related value from another.
Ensure the lookup vector is sorted ascending for accurate results.
LOOKUP returns the closest match less than or equal to the lookup value, not always exact.
The lookup and result vectors must be the same size in vector form.
For exact matches, consider using newer functions like XLOOKUP or VLOOKUP.