The LOOKUP function in Excel searches for a value in a row or column and returns a related value from another row or column. It is useful for simple lookup tasks such as matching employee IDs, product names, grades, or pricing information. While newer functions like XLOOKUP and VLOOKUP are often preferred today, the LOOKUP function is still useful for older spreadsheets and basic approximate matching.
In This Guide, You’ll Learn
- What the LOOKUP function does
- The syntax and arguments of LOOKUP
- How to use LOOKUP with practical examples
- The difference between vector and array forms of LOOKUP
- Common mistakes to avoid when using LOOKUP
- Real-world business uses for LOOKUP
What is the LOOKUP Function?
The LOOKUP function searches for a value in one row or column and returns a matching value from another row or column in the same position. It works best when your data is sorted in ascending order.
LOOKUP Function Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])Argument Definitions
- lookup_value – The value Excel searches for. This could be a number, text value, or cell reference.
- lookup_vector – The row or column Excel searches within. This range must be sorted in ascending order.
- [result_vector] – Optional. The row or column containing the result you want returned. If omitted, Excel returns a value from the lookup_vector.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> LOOKUP Function Data
Looking Up Employee Departments
Imagine you have employee IDs and want to return the employee’s department.
Formula Used
=LOOKUP(1002,A2:A6,B2:B6)
How It Works
The formula searches for Employee ID 1002 in column A and returns the corresponding department from column B.
Finding Product Prices
You can use LOOKUP to return product pricing.
Formula Used
=LOOKUP(D2,A2:A6,B2:B6)
How It Works
The formula searches for Notebook in column A and returns the matching price from column B.
Using LOOKUP for Approximate Match Grading
One of the best uses of LOOKUP is approximate matching.
Formula Used
=LOOKUP(D2,A2:A6,B2:B6)
How It Works
The formula searches for 84. Since it falls between 80 and 90, Excel returns B.
Important: The lookup values must be sorted in ascending order for approximate matching to work correctly.
Using LOOKUP to Find Commission Rates
Businesses often use LOOKUP to determine commission percentages based on sales amounts.
Formula Used
=LOOKUP(D2,A2:A6,B2:B6)
How It Works
The formula looks for 7200 and returns the closest matching commission rate without going over, which is 5%.
Common LOOKUP Function Mistakes
Using Unsorted Data
The LOOKUP function expects the lookup vector to be sorted in ascending order. Unsorted data can produce incorrect results.
Expecting Exact Matches Only
LOOKUP performs approximate matching by default. If an exact match is not found, Excel returns the nearest smaller value.
Using LOOKUP Instead of XLOOKUP
For modern spreadsheets, XLOOKUP is often easier and more flexible because it supports exact matching by default and can search in any direction.
Forgetting Matching Range Sizes
The lookup vector and result vector should contain the same number of cells.
LOOKUP vs VLOOKUP vs XLOOKUP
| Function | Best For | Limitation |
|---|---|---|
| LOOKUP | Simple approximate matching | Requires sorted data |
| VLOOKUP | Vertical lookups | Cannot look left |
| XLOOKUP | Flexible modern lookups | Only available in newer Excel versions |
If you are using a modern version of Excel, XLOOKUP is generally the better option. However, understanding LOOKUP is still helpful when working with older spreadsheets.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> LOOKUP Function Data
