The LOOKUP function in Excel is used to search for a value in a range and return a corresponding value from another range. There are two forms of the LOOKUP function: Vector form and Array form. Below are 20 examples of using the LOOKUP function with sample data.
Example 1: Basic Lookup (Vector Form)
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Banana", A1:A3, B1:B3)
Result: 20
Explanation: Looks up "Banana" in column A and returns the corresponding value from column B.
Example 2: Lookup with Numeric Values
Data:
| A | B |
|---|---|
| 101 | Red |
| 102 | Green |
| 103 | Blue |
Formula: =LOOKUP(102, A1:A3, B1:B3)
Result: Green
Explanation: Looks up the value 102 in column A and returns the corresponding value from column B.
Example 3: Approximate Match
Data:
| A | B |
|---|---|
| 10 | Low |
| 20 | Medium |
| 30 | High |
Formula: =LOOKUP(25, A1:A3, B1:B3)
Result: Medium
Explanation: Since 25 is not an exact match, it returns the closest value less than or equal to 25.
Example 4: Lookup with Unsorted Data
Data:
| A | B |
|---|---|
| 30 | High |
| 10 | Low |
| 20 | Medium |
Formula: =LOOKUP(20, A1:A3, B1:B3)
Result: Medium
Explanation: Works even if the data is unsorted.
Example 5: Lookup with Text and Numbers
Data:
| A | B |
|---|---|
| Cat | 100 |
| Dog | 200 |
| Bird | 300 |
Formula: =LOOKUP("Dog", A1:A3, B1:B3)
Result: 200
Explanation: Looks up "Dog" in column A and returns the corresponding value from column B.
Example 6: Lookup with Partial Match
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Ban", A1:A3, B1:B3)
Result: 20
Explanation: Performs a partial match and returns the corresponding value.
Example 7: Lookup with Dates
Data:
| A | B |
|---|---|
| 1/1/2023 | Winter |
| 4/1/2023 | Spring |
| 7/1/2023 | Summer |
Formula: =LOOKUP(DATE(2023,6,1), A1:A3, B1:B3)
Result: Spring
Explanation: Looks up the date 6/1/2023 and returns the corresponding season.
Example 8: Lookup with Logical Values
Data:
| A | B |
|---|---|
| TRUE | Yes |
| FALSE | No |
Formula: =LOOKUP(TRUE, A1:A2, B1:B2)
Result: Yes
Explanation: Looks up TRUE and returns the corresponding value.
Example 9: Lookup with Wildcards
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Ap*", A1:A3, B1:B3)
Result: 10
Explanation: Uses a wildcard to match "Ap" and returns the corresponding value.
Example 10: Lookup with Array Form
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Banana", A1:B3)
Result: 20
Explanation: Uses the array form to search for "Banana" and return the corresponding value.
Example 11: Lookup with Multiple Columns
Data:
| A | B | C |
|---|---|---|
| Apple | Red | 10 |
| Banana | Yellow | 20 |
| Orange | Orange | 30 |
Formula: =LOOKUP("Banana", A1:A3, C1:C3)
Result: 20
Explanation: Looks up "Banana" in column A and returns the corresponding value from column C.
Example 12: Lookup with Error Handling
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =IFERROR(LOOKUP("Grape", A1:A3, B1:B3), "Not Found")
Result: Not Found
Explanation: Returns "Not Found" if the lookup value is not found.
Example 13: Lookup with Dynamic Range
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Banana", A:A, B:B)
Result: 20
Explanation: Uses entire columns for dynamic lookup.
Example 14: Lookup with Mixed Data Types
Data:
| A | B |
|---|---|
| 101 | Red |
| 102 | Green |
| ABC | Blue |
Formula: =LOOKUP("ABC", A1:A3, B1:B3)
Result: Blue
Explanation: Works with mixed data types (numbers and text).
Example 15: Lookup with Duplicate Values
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Apple | 30 |
Formula: =LOOKUP("Apple", A1:A3, B1:B3)
Result: 10
Explanation: Returns the first match found.
Example 16: Lookup with Blank Cells
Data:
| A | B |
|---|---|
| Apple | 10 |
| 20 | |
| Orange | 30 |
Formula: =LOOKUP("Orange", A1:A3, B1:B3)
Result: 30
Explanation: Ignores blank cells in the lookup range.
Example 17: Lookup with Non-Adjacent Columns
Data:
| A | B | C |
|---|---|---|
| Apple | Red | 10 |
| Banana | Yellow | 20 |
| Orange | Orange | 30 |
Formula: =LOOKUP("Banana", A1:A3, C1:C3)
Result: 20
Explanation: Looks up "Banana" in column A and returns the corresponding value from column C.
Example 18: Lookup with Array Formula
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Banana", A1:B3)
Result: 20
Explanation: Uses the array form to search for "Banana" and return the corresponding value.
Example 19: Lookup with Multiple Criteria
Data:
| A | B | C |
|---|---|---|
| Apple | Red | 10 |
| Banana | Yellow | 20 |
| Orange | Orange | 30 |
Formula: =LOOKUP(1, (A1:A3="Banana")*(B1:B3="Yellow"), C1:C3)
Result: 20
Explanation: Uses an array formula to match multiple criteria.
Example 20: Lookup with Dynamic Array (Excel 365)
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Orange | 30 |
Formula: =LOOKUP("Banana", A1:A3, B1:B3)
Result: 20
Explanation: Works seamlessly in Excel 365 with dynamic arrays.
These examples demonstrate the versatility of the LOOKUP function in Excel.
No comments:
Post a Comment