LOOKUP() Function in
Excel: A Beginner's Guide
Overview of the
LOOKUP() Function
The LOOKUP() function
in Excel is a versatile and powerful function used to retrieve data from a
specified range based on a lookup value. It allows you to find information in a
one-dimensional range (vector) or in a two-dimensional range (array). Although
it has been somewhat overshadowed by more specific functions like VLOOKUP(),
HLOOKUP(), and INDEX/MATCH, LOOKUP() can still be very useful for certain
tasks.
Syntax
There are two forms
of the LOOKUP() function: the Vector Form and the Array
Form.
1. Vector Form
LOOKUP(lookup_value, lookup_vector, [result_vector])
- lookup_value:
The value you want to find in the lookup vector.
- lookup_vector: A
range that contains the values to search.
- result_vector (optional):
A range that contains the values to return. It must be the same size as
lookup_vector.
2. Array Form
LOOKUP(lookup_value, array)
- lookup_value:
The value you want to find in the array.
- array: A
range of cells containing the data. The first row or column is searched, and
if a match is found, the corresponding value from the last row or column
is returned.
Example Scenario
Let’s consider a
practical example to illustrate how to use the LOOKUP() function. Suppose you
have a list of students and their corresponding scores.
Sample Data
|
Student |
Score |
|
Alice |
85 |
|
Bob |
78 |
|
Charlie |
92 |
|
David |
88 |
|
Eva |
95 |
Step-by-Step Example
1. Set Up Your Data
Create a table in
Excel with the data shown above, using the range A1
.
2. Use the LOOKUP
Function
Suppose you want to
find the score for David.
- Step
1: Select the cell where you want the result to
appear (e.g., cell D1).
- Step
2: Enter the formula:
=LOOKUP("David", A2:A6, B2:B6)
Explanation of the
Formula:
- "David":
This is the lookup_value, the name you want to find.
- A2
: This is the
lookup_vector, the range where you are searching for David’s name.
- B2
: This is the
result_vector, the range containing the scores that you want to return.
3. Result
After pressing Enter,
the cell will display 88, which indicates David's score.
Using LOOKUP in Array
Form
Suppose you want to
retrieve the score of a student based on their position in the list without
specifying the result vector.
- Formula:
=LOOKUP(2,1/(A2:A6="Charlie"),B2:B6)
Explanation:
- 2:
The lookup value.
- 1/(A2
="Charlie"): Creates an array where TRUE is converted to 1 and FALSE to #DIV/0!.
When Charlie is found, it returns the corresponding score from the B2:B6 range.
Important Notes
1.
Sorted Data: The LOOKUP() function assumes that the data in the lookup vector is sorted
in ascending order. If it is not sorted, you may get incorrect results.
2.
Approximate Match: The LOOKUP() function performs an approximate match. If the
lookup_value is not found, it returns the next largest value.
3.
Error Handling: If the lookup_value is not found and no smaller value exists, the
function will return the last value in the result vector, which may lead to
unexpected results. You can use IFERROR() to handle errors gracefully.
4.
Two-Dimensional Array: In the array form, LOOKUP() can also be used for two-dimensional
arrays, where the first row or column is searched for the lookup_value, and the
last row or column is returned.
Practical
Applications
1.
Data Retrieval: Use LOOKUP() to quickly find related data based on a single key.
2.
Creating Reports: It can simplify report creation by automatically pulling relevant data
based on lookup criteria.
3.
Dynamic Formulas: Combine LOOKUP() with other functions to create complex and dynamic
formulas for data analysis.
Conclusion
The LOOKUP() function is a handy tool in Excel that can simplify data retrieval tasks. By understanding its syntax and functionality, MBA students can efficiently access and analyze data, enhancing their analytical skills.
Assignment 1: Student
Score Lookup
Task: Use the student scores table to find the scores of specific students.
Sample Data
|
Student |
Score |
|
Alice |
85 |
|
Bob |
78 |
|
Charlie |
92 |
|
David |
88 |
|
Eva |
95 |
Questions:
1.
What is the score of Bob?
2.
What is the score of Eva?
Solutions
1.
For Bob's score:
o
Formula:
=LOOKUP("Bob", A2:A6, B2:B6)
o
Result: 78
2.
For Eva's score:
o
Formula:
=LOOKUP("Eva", A2:A6, B2:B6)
o
Result: 95
Assignment 2: Product
Price Lookup
Task: Use the product prices table to find the prices of specific products.
Sample Data
|
Product |
Price |
|
Laptop |
$1200 |
|
Smartphone |
$800 |
|
Tablet |
$400 |
|
Headphones |
$150 |
|
Smartwatch |
$250 |
Questions:
1.
What is the price of the Tablet?
2.
What is the price of the Smartphone?
Solutions
1.
For Tablet's price:
o
Formula:
=LOOKUP("Tablet", A2:A6, B2:B6)
o
Result: $400
2.
For Smartphone's price:
o
Formula:
=LOOKUP("Smartphone", A2:A6, B2:B6)
o
Result: $800
Assignment 3:
Employee Salary Lookup
Task: Use the employee salary table to find the salaries of specific
employees.
Sample Data
|
Employee Name |
Salary |
|
Alice |
$70,000 |
|
Bob |
$60,000 |
|
Charlie |
$80,000 |
|
David |
$75,000 |
|
Eva |
$90,000 |
Questions:
1.
What is the salary of David?
2.
What is the salary of Alice?
Solutions
1.
For David's salary:
o
Formula:
=LOOKUP("David", A2:A6, B2:B6)
o
Result: $75,000
2.
For Alice's salary:
o
Formula:
=LOOKUP("Alice", A2:A6, B2:B6)
o
Result: $70,000
Assignment 4: Country
Population Lookup
Task: Use the country population table to find the populations of specific
countries.
Sample Data
|
Country |
Population |
|
USA |
331,002,651 |
|
India |
1,380,004,385 |
|
China |
1,439,323,776 |
|
Brazil |
212,559,417 |
|
UK |
68,207,116 |
Questions:
1.
What is the population of India?
2.
What is the population of Brazil?
Solutions
1.
For India's population:
o
Formula:
=LOOKUP("India", A2:A6, B2:B6)
o
Result: 1,380,004,385
2.
For Brazil's population:
o
Formula:
=LOOKUP("Brazil", A2:A6, B2:B6)
o
Result: 212,559,417
Assignment 5: Course
Enrollment Lookup
Task: Use the course enrollment table to find the enrollment of specific
courses.
Sample Data
|
Course |
Enrollment |
|
MBA |
120 |
|
BBA |
150 |
|
MSc |
80 |
|
PhD |
30 |
Questions:
1.
What is the enrollment for the MSc program?
2.
What is the enrollment for the BBA program?
Solutions
1.
For MSc program's
enrollment:
o
Formula:
=LOOKUP("MSc", A2:A5, B2:B5)
o
Result: 80
2.
For BBA program's
enrollment:
o
Formula:
=LOOKUP("BBA", A2:A5, B2:B5)
o
Result: 150
Assignment 6: Fruit
Price Lookup
Task: Use the fruit price table to find the prices of specific fruits.
Sample Data
|
Fruit |
Price |
|
Apple |
$2.00 |
|
Banana |
$1.00 |
|
Cherry |
$3.00 |
|
Date |
$2.50 |
Questions:
1.
What is the price of Banana?
2.
What is the price of Cherry?
Solutions
1.
For Banana's price:
o
Formula:
=LOOKUP("Banana", A2:A5, B2:B5)
o
Result: $1.00
2.
For Cherry's price:
o
Formula:
=LOOKUP("Cherry", A2:A5, B2:B5)
o
Result: $3.00
Conclusion
These assignments provide a variety of scenarios where students can practice using the LOOKUP() function in Excel.
