Excel Lookup

Let’s explore VLOOKUP and XLOOKUP, two essential Excel functions for searching and retrieving data from tables. I’ll break down both with examples, highlighting their differences and best use cases.

---

VLOOKUP vs. XLOOKUP Tutorial

Goal: Learn to find specific data in a table (e.g., product prices, employee IDs, customer details).

---

Part 1: VLOOKUP (Vertical Lookup)

What It Does

Searches for a value in the first column of a table and returns a value from a specified column to the right.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to search for (e.g., “Laptop”).
• table_array: The table range (e.g., A2:D10).
• col_index_num: Column number in the table containing the result (e.g., 2 for the second column).
• [range_lookup]: Use FALSE for exact match, TRUE for approximate match.

---

Example 1: Find a Product’s Price

Data:

ProductID	Product	Price
101	Laptop	999
102	Phone	699
103	Monitor	250

Task: Find the price of “Phone” using its ProductID (102).

1. In cell F2, type 102 (the lookup value).
2. In cell G2, use:

   =VLOOKUP(F2, A2:C4, 3, FALSE)

• F2: Value to search for (102).
• A2:C4: Table range.
• 3: Return the 3rd column (Price).
• FALSE: Exact match.

Result: 699.

---

Limitations of VLOOKUP:

1. Can only search left-to-right (lookup column must be the first column).
2. Column index number is static (breaks if columns are added/deleted).
3. No built-in error handling (requires IFERROR).

---

Part 2: XLOOKUP (Modern Replacement)

What It Does

Searches for a value in any column/row and returns a result from any column/row. More flexible and powerful than VLOOKUP.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: Value to search for.
• lookup_array: Column/row to search.
• return_array: Column/row to return.
• [if_not_found]: Custom message if no match (optional).
• [match_mode]: 0 (exact), -1 (exact or smaller), 1 (exact or larger), 2 (wildcard).
• [search_mode]: 1 (top to bottom), -1 (bottom to top).

---

Example 1: Find Price with XLOOKUP

Using the same data as above:

=XLOOKUP(F2, A2:A4, C2:C4, "Not Found", 0)

• F2: Lookup value (102).
• A2:A4: Search in the ProductID column.
• C2:C4: Return the Price column.
• "Not Found": Custom message if no match.
• 0: Exact match.

Result: 699.

---

Example 2: Search in Any Direction

Task: Find the ProductID using the Product name (“Monitor”).

=XLOOKUP("Monitor", B2:B4, A2:A4)

• Searches the Product column (B2:B4) and returns the ProductID (A2:A4).

Result: 103.

---

Part 3: Key Differences

Feature	VLOOKUP	XLOOKUP
Search Direction	Left-to-right only	Any direction
Column Reference	Static column index	Dynamic column/range
Error Handling	Requires IFERROR	Built-in [if_not_found]
Approximate Match	Requires sorted data	Works with unsorted data
Flexibility	Limited	Supports vertical/horizontal

---

Part 4: Practice Exercise

Dataset:

EmployeeID	Name	Department	Salary
E001	Alice	Sales	5000
E002	Bob	IT	6000
E003	Carol	HR	5500

Tasks:

1. Use VLOOKUP to find Bob’s salary.

• Lookup value: E002, return the 4th column.

1. Use XLOOKUP to find Carol’s department.

• Search by name (“Carol”), return the Department column.

1. Add error handling to both formulas (e.g., display “Not Found”).

---

Pro Tips

• Wildcard Search:
• Use * or ? in XLOOKUP (e.g., XLOOKUP("Pho*", B2:B4, C2:C4,,2)).
• Dynamic Ranges: Use Excel Tables (Ctrl+T) to auto-expand ranges.
• Two-Way Lookup: Combine XLOOKUP with XLOOKUP for matrix searches.

---

Common Errors

• #N/A in VLOOKUP:
• Check if [range_lookup] is FALSE for exact matches.
• Ensure the lookup column is the first column of the table.
• #VALUE! in XLOOKUP:
• Verify lookup_array and return_array have the same size.

---

When to Use Which

• Use VLOOKUP: For simple left-to-right lookups in older Excel versions.
• Use XLOOKUP: For flexibility, bidirectional searches, and error handling (Excel 365/2021+).

---

Let me know if you’d like to practice with a custom dataset or dive into HLOOKUP/INDEX-MATCH! 😊