Introduction to Lookup Functions
Lookup functions are the bridge between different tables. They let you pull information from one place based on a key value.
The Problem Lookups Solve
Imagine you have:
- Employees sheet: Employee ID, Name, Job Code
- Job Codes sheet: Job Code, Title, Hourly Rate
How do you show each employee's hourly rate without copying data?
Answer: Use a lookup to find the Job Code in the second sheet and return the rate!
Common Lookup Functions
VLOOKUP (Vertical Lookup)
The classic lookup function. Searches the first column of a range and returns a value from another column.=VLOOKUP(lookup_value, table_array, col_index, [exact_match])
Example: =VLOOKUP("ENG-3", 'Job Codes'!A:D, 3, FALSE)
INDEX + MATCH (More Flexible)
A powerful combination that works in any direction.=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
When to Use Each
| Scenario | Best Function |
|---|---|
| Simple vertical lookup | VLOOKUP |
| Lookup column is not first | INDEX+MATCH |
| Need to look left | INDEX+MATCH |
| Performance matters | INDEX+MATCH |
Key Concepts
- Lookup Value: What you're searching for (e.g., "ENG-3")
- Lookup Range: Where to search (e.g., Job Code column)
- Return Range: What to return (e.g., Hourly Rate column)
In the labs, you'll use lookups to calculate payroll from job codes!