Guide: Lookup Functions in Excel
Lookup Functions in Excel
The Lookup functions in Excel are used to search for specific data in a row or column and return a
value from the same position in another row or column.
---
Types of Lookup Functions
1. VLOOKUP (Vertical Lookup)
Searches for a value in the first column of a range and returns a value in the same row from
another column.
Example:
| Product | Price |
|---------|-------|
| Apple | $1.00 |
| Banana | $0.50 |
| Orange | $0.75 |
Formula:
=VLOOKUP("Banana", A2:B4, 2, FALSE)
- Result: $0.50
Explanation:
- Searches for "Banana" in the first column (A2:A4).
- Returns the corresponding value from the second column (B2:B4).
---
2. HLOOKUP (Horizontal Lookup)
Searches for a value in the first row of a range and returns a value in the same column from
another row.
Example:
| Product | Apple | Banana | Orange |
|---------|-------|--------|--------|
| Price | $1.00 | $0.50 | $0.75 |
Formula:
=HLOOKUP("Banana", A1:D2, 2, FALSE)
- Result: $0.50
Explanation:
- Searches for "Banana" in the first row (A1:D1).
- Returns the corresponding value from the second row (A2:D2).
---
3. LOOKUP
Searches for a value in a range and returns a value from the same position in another range.
Example:
| Product | Price |
|---------|-------|
| Apple | $1.00 |
| Banana | $0.50 |
| Orange | $0.75 |
Formula:
=LOOKUP("Orange", A2:A4, B2:B4)
- Result: $0.75
Explanation:
- Searches for "Orange" in the range A2:A4.
- Returns the corresponding value from B2:B4.
---
Important Points
- Always ensure the lookup value exists in the lookup range.
- The FALSE in the formula indicates an exact match is required. Use TRUE for an approximate
match.
- Ensure the data is sorted when using approximate matches.
---
Practice Problems
1. Use VLOOKUP to find the price of "Orange" in the following table:
| Product | Price |
|---------|-------|
| Apple | $1.00 |
| Banana | $0.50 |
| Orange | $0.75 |
2. Use HLOOKUP to find the price of "Apple" from the following table:
| Product | Apple | Banana | Orange |
|---------|-------|--------|--------|
| Price | $1.00 | $0.50 | $0.75 |
3. Use LOOKUP to find the value associated with "Banana" in the range below:
| Product | Price |
|---------|-------|
| Apple | $1.00 |
| Banana | $0.50 |
| Orange | $0.75 |
---
