Function : LOOKUP()
Definition:
The LOOKUP() function in Excel searches for a value either from a row or a column and returns a corresponding value from another row or column.
It is a legacy lookup function, but still useful when dealing with simple, sorted data.
🧩 Syntax:
| Argument | Description |
|---|---|
| lookup_value | The value to search for. |
| lookup_vector | The range where Excel searches for the lookup_value. |
| result_vector | The range from which the result is returned. |
⚠️ Note: Data in the lookup_vector must be sorted in ascending order; otherwise, the function may return incorrect results.
🎯 Example:
Example 1 – Basic LOOKUP from a Single Column
| A (Marks) | B (Grade) |
|---|---|
| 0 | F |
| 40 | C |
| 60 | B |
| 80 | A |
Formula:
✅ Result: B
Explanation: 75 is between 60 and 80, so LOOKUP returns the corresponding grade B.
🧠 5 Practical Projects Using LOOKUP()
🧮 Project 1: Student Performance Evaluation
Scenario:
A teacher wants to assign performance levels based on students’ total marks.
| Student | Total Marks | Grade |
|---|---|---|
| Rahul | 82 | =LOOKUP(B2, {0,40,60,80}, {“Fail”,”Pass”,”Good”,”Excellent”}) |
| Priya | 59 | =LOOKUP(B3, {0,40,60,80}, {“Fail”,”Pass”,”Good”,”Excellent”}) |
| Sumit | 38 | … |
✅ Output Example:
Rahul → Excellent
Priya → Good
Sumit → Fail
Concept Used: Inline array lookup to replace nested IF statements.
💹 Project 2: Sales Target Achievement Level
Scenario:
You want to determine achievement categories based on percentage of target achieved.
| Salesperson | % of Target | Level |
|---|---|---|
| Arjun | 96% | =LOOKUP(B2, {0,50,75,90}, {“Low”,”Average”,”Good”,”Excellent”}) |
| Nisha | 67% | … |
✅ Result:
Arjun → Excellent
Nisha → Good
🏦 Project 3 (Intermediate): Bank Loan Interest Finder
Scenario:
Interest rate changes based on loan amount.
| Loan Amount | Interest % |
|---|---|
| 0 | 8.5 |
| 100000 | 9.0 |
| 500000 | 9.5 |
| 1000000 | 10.0 |
Formula Example:
If D2 = ₹6,00,000 → Interest = 9.5%
✅ Result: 9.5%
Concept: Dynamic lookup for financial calculation using sorted slabs.
🏬 Project 4 (Harder): Product Price Finder with Dynamic Discount
Scenario:
Discount depends on purchase quantity, using LOOKUP for quick slab mapping.
| Quantity | Discount % |
|---|---|
| 0 | 0 |
| 10 | 5 |
| 25 | 10 |
| 50 | 15 |
| 100 | 20 |
| Product | Qty | Unit Price | Discount | Final Price |
|---|---|---|---|---|
| Soap | 36 | ₹50 | =LOOKUP(B2, F2:F6, G2:G6) | =C2*(1-D2/100)*B2 |
✅ Result Example:
Qty 36 → Discount 10% → Final Price = ₹50 × 36 × 0.9 = ₹1620
Concept Used: Volume-based pricing using LOOKUP() logic.
🏢 Project 5 (Hardest): Employee Salary Band Classification
Scenario:
An HR manager classifies employees into bands based on salary ranges.
| Salary (₹) | Band |
|---|---|
| 0 | Trainee |
| 15000 | Junior |
| 30000 | Executive |
| 60000 | Senior |
| 100000 | Manager |
| Employee | Salary | Band |
|---|---|---|
| Anjali | 72,000 | =LOOKUP(B2, F2:F6, G2:G6) |
| Rohan | 1,20,000 | … |
✅ Output Example:
Anjali → Senior
Rohan → Manager
Concept Used:
Hierarchical data classification, similar to HR salary banding systems.
🧾 Summary Table
| Function | Purpose | Key Feature |
|---|---|---|
| LOOKUP() | Searches in one range and returns value from another | Works with sorted data, both row and column vectors supported |