DAY 5: Excel - VLOOKUP & XLOOKUP

📝 Setup Instructions:

1. Open Excel and create a new workbook "Day05_Practice.xlsx"
2. Sheet 1 (Orders): Your main order data with columns: OrderID, CustomerID, ProductID, Quantity, Date
3. Sheet 2 (Customers): Reference table with CustomerID, CustomerName, Region, Email
4. Sheet 3 (Products): Reference table with ProductID, ProductName, Category, Price
5. You'll use VLOOKUP/XLOOKUP to pull information from reference tables into the Orders sheet

💡 Why Lookup Functions? Instead of duplicating customer/product details in every order row, we store them once in reference tables and "look them up" when needed. This is database normalization in Excel!

Exercise 1: Your First VLOOKUP - Find Customer Names

Step-by-Step Instructions:

1. Go to your Orders sheet
2. Add a column header "Customer Name" (e.g., column E)
3. Click on cell E2 (first data row)
4. Type this formula:

   =VLOOKUP(B2, Customers!A:D, 2, FALSE)

   Let's break it down:
   • B2 = Lookup value (CustomerID in current row)
   • Customers!A:D = Table to search (Customers sheet, columns A-D)
   • 2 = Column index (return value from 2nd column = CustomerName)
   • FALSE = Exact match required
5. Press Enter
6. Copy the formula down to all order rows (double-click fill handle)

💡 Tips:

• VLOOKUP always searches the FIRST column of your range
• FALSE = exact match (use this for IDs), TRUE = approximate match (for ranges)
• Use absolute references if needed: $A$2:$D$100
• The "V" stands for "Vertical" - searches down columns

✅ Expected Result: Customer names appear next to their IDs in every order row

Exercise 2: VLOOKUP Product Prices

Step-by-Step Instructions:

1. Add a "Unit Price" column in your Orders sheet (e.g., column F)
2. In F2, type:

   =VLOOKUP(C2, Products!A:D, 4, FALSE)

   • C2 = ProductID to look up
   • Products!A:D = Product reference table
   • 4 = Return value from 4th column (Price)
3. Copy down to all rows
4. Add a "Total Price" column (G): =F2*D2 (Unit Price × Quantity)
5. Copy this formula down too

🔍 Understanding Column Index:

• If your Products table is: A=ProductID, B=ProductName, C=Category, D=Price
• Column index 1 = ProductID, 2 = ProductName, 3 = Category, 4 = Price
• To get category instead: =VLOOKUP(C2, Products!A:D, 3, FALSE)

✅ Expected Result: Each order shows unit price and calculated total price

Exercise 3: Handle Errors with IFERROR

Step-by-Step Instructions:

1. VLOOKUPs return #N/A error when lookup value isn't found
2. Wrap your VLOOKUP with IFERROR to handle this gracefully:

   =IFERROR(VLOOKUP(B2, Customers!A:D, 2, FALSE), "Customer Not Found")

3. Now if a CustomerID doesn't exist, you see "Customer Not Found" instead of #N/A
4. Try it: Temporarily change a CustomerID to "999999" and watch the formula handle it
5. Change it back after testing

💡 Error Handling Best Practices:

• Always wrap VLOOKUPs in IFERROR for user-friendly error messages
• Alternative messages: "Not Found", "N/A", "Check Data", or even "" (blank)
• Syntax: =IFERROR(your_formula, value_if_error)

✅ Expected Result: Clean data with user-friendly messages instead of #N/A errors

Exercise 4: XLOOKUP - The Modern Alternative (Excel 365)

Step-by-Step Instructions:

1. XLOOKUP is newer, more flexible than VLOOKUP
2. Add a "Region" column (e.g., column H)
3. In H2, type:

   =XLOOKUP(B2, Customers!A:A, Customers!C:C, "Not Found")

   Syntax:
   • B2 = Lookup value (CustomerID)
   • Customers!A:A = Where to search (CustomerID column)
   • Customers!C:C = What to return (Region column)
   • "Not Found" = Built-in error handling!
4. Press Enter and copy down

🎯 XLOOKUP Advantages over VLOOKUP:

• Can search any column (not just first column)
• Can return columns to the LEFT of lookup column
• Built-in error handling (no IFERROR needed)
• Can search from bottom-up
• Generally faster and more intuitive

⚠️ Note: XLOOKUP only works in Excel 365/2021+. If you don't have it, continue using VLOOKUP or INDEX-MATCH

✅ Expected Result: Region data pulled from Customers table with built-in error handling

Exercise 5: INDEX-MATCH - The Flexible Power Combo

Step-by-Step Instructions:

1. INDEX-MATCH is more flexible than VLOOKUP and works in all Excel versions
2. Add a "Product Category" column (e.g., column I)
3. Type this formula:

   =INDEX(Products!C:C, MATCH(C2, Products!A:A, 0))

   Breaking it down:
   • MATCH(C2, Products!A:A, 0) finds the row number where C2 (ProductID) appears in Products column A
   • INDEX(Products!C:C, ...) returns the value from that row in column C (Category)
   • 0 = exact match
4. Wrap in IFERROR for safety:

   =IFERROR(INDEX(Products!C:C, MATCH(C2, Products!A:A, 0)), "Not Found")

5. Copy down to all rows

💡 Why INDEX-MATCH?

• More flexible than VLOOKUP (can look left, not just right)
• Can change column references without rewriting formulas
• Works in all Excel versions (unlike XLOOKUP)
• Often faster with large datasets

✅ Expected Result: Product categories populated using INDEX-MATCH

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use VLOOKUP to retrieve data from reference tables
• Understand VLOOKUP syntax and column index numbers
• Handle lookup errors gracefully with IFERROR
• Use XLOOKUP (Excel 365) with built-in error handling
• Apply INDEX-MATCH for flexible lookups
• Choose the right lookup function for your situation

Key Insight: Lookup functions connect related data across tables - this is how databases work! You're learning relational data concepts in Excel.

Comparison Guide:

• VLOOKUP: Classic, simple, but limited (only looks right)
• XLOOKUP: Modern, powerful, easy - use if available
• INDEX-MATCH: Most flexible, works everywhere, steeper learning curve

Take a 5-minute break, then move on to Independent Practice!