Excelโs VLOOKUP function can be used for a wide range of data analysis tasks beyond simply looking up values. In this article, weโll cover practical ways to analyze data with VLOOKUP.
โ 1. Basic Lookup Analysis
VLOOKUP is primarily used to search for data based on a specific key value.
=VLOOKUP("Hong Gil-dong", A2:D100, 3, FALSE)
Example: Find an employeeโs department by name
โ 2. Condition-Based Analysis
VLOOKUP normally handles only one condition, but you can combine multiple conditions for a lookup.
=VLOOKUP(A2 & B2, CHOOSE({1,2}, A:A & B:B, C:C), 2, FALSE)
โ 3. Detect Missing Data
VLOOKUP makes it easy to find data that is not included in a reference list.
=IF(ISNA(VLOOKUP(A2, B:B, 1, FALSE)), "Missing", "Exists")
Example: Check whether a customer code exists
โ 4. Detect and Handle Data Errors
You can also handle errors in VLOOKUP results when there is invalid data or blank cells.
=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "No data")
โ 5. Compare Data
You can also compare values between two tables. Example: Compare the standard price with the actual price
=VLOOKUP(A2, StandardTable!A:B, 2, FALSE) = B2
โ 6. Consolidated Analysis Across Sheets
VLOOKUP is useful for consolidating or comparing data across worksheets.
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
โ 7. Flexible Analysis with Indirect References
When used with the INDIRECT function, you can set the lookup range dynamically.
=VLOOKUP(A2, INDIRECT(B2), 2, FALSE)
Example: Automatically look up data from department-specific worksheets
โ 8. Approximate Match in a Sorted Range
Setting VLOOKUPโs final argument to TRUE enables approximate-match lookups.
=VLOOKUP(85, StandardTable!A:B, 2, TRUE)
Example: Check a grade based on a score
๐ Summary
- ๐ Basic lookups: Find data based on a key value
- ๐ Conditional searches: Lookups based on multiple conditions are also possible
- ๐งฏ Missing-data and error detection: Effective for data cleanup
- ๐ Comparison and consolidation: Compare data across multiple worksheets and tables
- ๐ง Manage ranges flexibly with INDIRECT
VLOOKUP may seem like a simple function, but with the right combinations, it can be a powerful analysis tool. Use the examples above in your day-to-day work!