Lookup Functions: Connecting Data

Real data lives in multiple tables — a price list here, an order list there. Lookup functions join them, and they are the single most-tested Excel skill in interviews.

Key Concepts

• VLOOKUP(value, table, column_number, FALSE) fetches matching data from another table
• The FALSE argument forces exact match — omit it and silent wrong answers appear
• VLOOKUP's limits: looks only rightward, breaks when columns are inserted
• XLOOKUP(value, lookup_range, return_range) fixes those limits and is the modern standard
• INDEX-MATCH remains the compatible power combo for older Excel versions

Try It Yourself

Create two tables: products with prices, and orders with product names. Use VLOOKUP (then XLOOKUP) to pull prices into the orders table. Break it by inserting a column — observe which function survives.