π Mastering the VLOOKUP Function in Google Sheets: With Practical Examples
π [Step 1] English Post β First Draft (Markdown)
The `VLOOKUP` function in Google Sheets allows you to **search for a specific value in the first column of a range and return a related value from another column**.
It's widely used in business scenarios like employee directories, product pricing, customer classifications, and more.
---
### π Syntax
```excel
=VLOOKUP(search_key, range, index, [is_sorted])
| Argument | Description |
|---|---|
search_key | The value to search for |
range | The table that contains the data |
index | The column number to return a value from |
is_sorted | Optional; use FALSE for exact match (recommended) |
β In most practical use cases, always use
FALSEfor exact matches.
π§Ύ Sample Table: Employee Directory (A1:C5)
| A (1) | B (2) | C (3) |
|---|---|---|
| Emp ID | Name | Dept |
| 1001 | Hana Kim | Marketing |
| 1002 | Jimin Lee | Finance |
| 1003 | Doyoon Park | Sales |
| 1004 | Yerin Song | Dev |
β Examples and Output
βΆ Example 1: Lookup Name by Employee ID
=VLOOKUP(1002, A2:C5, 2, FALSE)
β Result: Jimin Lee
βΆ Example 2: Lookup Department by Employee ID
=VLOOKUP(1003, A2:C5, 3, FALSE)
β Result: Sales
βΆ Example 3: Lookup Non-Existent ID
=VLOOKUP(1010, A2:C5, 2, FALSE)
β Result: #N/A (Not Found)
π Changing the Search Key β How Results Change
1001β Hana Kim1004β Yerin Song9999β#N/Aerror
β‘ When the search key changes, the returned result updates automatically based on the corresponding row.
β Common Errors & Fixes
| Error | Cause | Fix |
|---|---|---|
#N/A | No match found | Check spelling or value |
#REF! | Index number too large | Adjust the column number |
| Wrong result | is_sorted not set to FALSE | Always use FALSE for exact matches |
π§ VLOOKUP vs SUMIF: When to Use Which?
| Feature | VLOOKUP | SUMIF |
|---|---|---|
| Purpose | Lookup a related value | Calculate conditional sum |
| Return Type | Text, numbers, etc. | Numeric sum only |
| Example Use | Name from ID | Total sales by region |
πΌ Real-World Examples
1) Name to Salary
| A | B |
|---|---|
| Name | Salary |
| Hana | 3,500,000 |
| Jimin | 3,700,000 |
=VLOOKUP("Jimin", A2:B3, 2, FALSE)
β Result: 3,700,000
2) Product Code to Price
| A | B |
|---|---|
| Code | Price |
| P001 | 15,000 |
| P002 | 18,000 |
=VLOOKUP("P002", A2:B3, 2, FALSE)
β Result: 18,000
3) Customer ID to Grade
| A | B |
|---|---|
| Cust ID | Grade |
| C001 | VIP |
| C002 | Regular |
=VLOOKUP("C001", A2:B3, 2, FALSE)
β Result: VIP
β Self Checklist: Can You Do These?
- Use VLOOKUP to find related values based on a key
- Understand the purpose of
FALSEfor exact match - Explain what causes
#N/Aand how to fix it - Apply the function to real-world tasks like payroll or product lookup
- Know when to use VLOOKUP vs SUMIF
𧩠Final Summary
The VLOOKUP function is an essential tool for anyone managing structured data.
Whether you’re handling employees, product catalogs, or customer records,
this function enables fast, accurate lookups based on a single identifier.
π Always remember to use FALSE for exact matches unless approximate matching is intended.
