Here are 20 examples of how the VLOOKUP

 Here are 20 examples of how the VLOOKUP function can be used in Excel, along with sample datasets. These examples cover a variety of use cases, from simple lookups to more advanced scenarios.

---

Example 1: Basic VLOOKUP

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP(2, A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up the name for ID 2.

---

Example 2: VLOOKUP with Exact Match

Dataset:

Product	Price
Apple	1.2
Banana	0.8
Orange	1.0

Formula:

excel

Copy

=VLOOKUP("Banana", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Finds the price of a Banana.

---

Example 3: VLOOKUP with Approximate Match

Dataset:

Score	Grade
0	F
60	D
70	C
80	B
90	A

Formula:

excel

Copy

=VLOOKUP(85, A2:B6, 2, TRUE)

Result: B
Explanation: Finds the grade for a score of 85 using an approximate match.

---

Example 4: VLOOKUP with Multiple Columns

Dataset:

ID	Name	Age	City
1	Alice	25	New York
2	Bob	30	London
3	Charlie	35	Paris

Formula:

excel

Copy

=VLOOKUP(3, A2:D4, 4, FALSE)

Result: Paris
Explanation: Retrieves the city for ID 3.

---

Example 5: VLOOKUP with Dynamic Column Index

Dataset:

ID	Name	Age	City
1	Alice	25	New York
2	Bob	30	London
3	Charlie	35	Paris

Formula:

excel

Copy

=VLOOKUP(2, A2:D4, MATCH("Age", A1:D1, 0), FALSE)

Result: 30
Explanation: Uses MATCH to dynamically find the column index for "Age".

---

Example 6: VLOOKUP with Wildcard

Dataset:

Product	Price
Apple	1.2
Banana	0.8
Orange	1.0

Formula:

excel

Copy

=VLOOKUP("B*", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Uses a wildcard to find the price of a product starting with "B".

---

Example 7: VLOOKUP with IFERROR

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=IFERROR(VLOOKUP(4, A2:B4, 2, FALSE), "Not Found")

Result: Not Found
Explanation: Returns "Not Found" if the lookup value doesn't exist.

---

Example 8: VLOOKUP with Multiple Criteria

Dataset:

ID	Name	Department
1	Alice	HR
2	Bob	IT
3	Charlie	HR

Formula:

excel

Copy

=VLOOKUP(1 & "HR", A2:A4 & C2:C4, 2, FALSE)

Result: Alice
Explanation: Combines ID and Department to create a unique lookup value.

---

Example 9: VLOOKUP with Table Array from Another Sheet

Dataset (Sheet2):

ID	Name
1	Alice
2	Bob
3	Charlie

Formula (Sheet1):

excel

Copy

=VLOOKUP(2, Sheet2!A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up data from another sheet.

---

Example 10: VLOOKUP with Named Range

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP(3, Employees, 2, FALSE)

Result: Charlie
Explanation: Uses a named range "Employees" for the table array.

---

Example 11: VLOOKUP with Partial Match

Dataset:

Product	Price
Apple	1.2
Banana	0.8
Orange	1.0

Formula:

excel

Copy

=VLOOKUP("App", A2:B4, 2, FALSE)

Result: 1.2
Explanation: Finds the price for a product containing "App".

---

Example 12: VLOOKUP with Dynamic Lookup Value

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP(A2, A2:B4, 2, FALSE)

Result: Alice
Explanation: Uses a cell reference for the lookup value.

---

Example 13: VLOOKUP with Multiple Results

Dataset:

ID	Name	Department
1	Alice	HR
2	Bob	IT
3	Charlie	HR

Formula:

excel

Copy

=VLOOKUP("HR", C2:C4, 1, FALSE)

Result: 1
Explanation: Finds the first ID in the HR department.

---

Example 14: VLOOKUP with Date Lookup

Dataset:

Date	Sales
2023-01-01	100
2023-02-01	200
2023-03-01	300

Formula:

excel

Copy

=VLOOKUP("2023-02-01", A2:B4, 2, FALSE)

Result: 200
Explanation: Looks up sales for a specific date.

---

Example 15: VLOOKUP with Text and Numbers

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP("2", A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up a numeric value stored as text.

---

Example 16: VLOOKUP with Duplicate Values

Dataset:

ID	Name
1	Alice
2	Bob
2	Charlie

Formula:

excel

Copy

=VLOOKUP(2, A2:B4, 2, FALSE)

Result: Bob
Explanation: Returns the first match for duplicate IDs.

---

Example 17: VLOOKUP with Case-Sensitive Lookup

Dataset:

Product	Price
Apple	1.2
apple	0.8
Orange	1.0

Formula:

excel

Copy

=VLOOKUP("apple", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Matches the exact case of the lookup value.

---

Example 18: VLOOKUP with Array Formula

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP({1,2}, A2:B4, 2, FALSE)

Result: Alice, Bob
Explanation: Returns multiple results using an array formula.

---

Example 19: VLOOKUP with Dynamic Table Array

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=VLOOKUP(2, INDIRECT("A2:B4"), 2, FALSE)

Result: Bob
Explanation: Uses INDIRECT to dynamically reference the table array.

---

Example 20: VLOOKUP with Error Handling

Dataset:

ID	Name
1	Alice
2	Bob
3	Charlie

Formula:

excel

Copy

=IF(ISNA(VLOOKUP(4, A2:B4, 2, FALSE)), "Not Found", VLOOKUP(4, A2:B4, 2, FALSE))

Result: Not Found
Explanation: Handles errors using ISNA.

---

These examples demonstrate the versatility of the VLOOKUP function in Excel.