The LOOKUP function 20 examples

 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.