VLOOKUP for Category Matching
Automatically look up and return information based on a category or transaction name.
=VLOOKUP(lookup_value, table_range, column_index, FALSE) How It Works
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. In personal finance, this is perfect for auto-categorizing transactions or looking up budget amounts.
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])- search_key: The value to search for
- range: The table containing the data
- index: Column number to return (1, 2, 3, etc.)
- is_sorted: FALSE for exact match (almost always use FALSE)
Example
Auto-Categorize Transactions:
Category Lookup Table (Sheet2):
| Merchant | Category |
|---|---|
| Amazon | Shopping |
| Walmart | Groceries |
| Netflix | Entertainment |
| Shell | Gas |
Transaction Sheet:
| Merchant | Amount | Category |
|---|---|---|
| Netflix | $15.99 | =VLOOKUP(A2, Sheet2!A:B, 2, FALSE) |
Result: “Entertainment”
Common Scenarios
Budget Lookup
Look up the budgeted amount for a category:
=VLOOKUP(A2, BudgetTable, 2, FALSE)Where BudgetTable has Category in column 1, Budget in column 2.
Tax Rate Lookup
Find tax rate based on income bracket:
| Income Threshold | Rate |
|---|---|
| 0 | 10% |
| 11000 | 12% |
| 44725 | 22% |
=VLOOKUP(income, TaxTable, 2, TRUE)Use TRUE for approximate match with sorted data.
Account Type Lookup
Categorize accounts as Asset/Liability:
=VLOOKUP(A2, AccountTypes, 2, FALSE)Variations
Handle Errors with IFERROR
=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")Returns “Not Found” instead of #N/A error.
Partial Match with Wildcards
=VLOOKUP("*"&A2&"*", Table, 2, FALSE)Matches if the lookup value appears anywhere in the first column.
Case-Insensitive Match
VLOOKUP in Google Sheets is case-insensitive by default.
Pro Tips
-
Always use FALSE for the last parameter unless you specifically need approximate matching
-
Lock the table range with $:
=VLOOKUP(A2, $D$2:$E$20, 2, FALSE) -
Use named ranges for cleaner formulas:
=VLOOKUP(A2, CategoryTable, 2, FALSE) -
XLOOKUP is newer and more flexible if available in your version
Common Errors
| Error | Cause | Fix |
|---|---|---|
| #N/A | Value not found | Check spelling, use IFERROR |
| #REF! | Column index too large | Reduce index number |
| #VALUE! | Invalid lookup value | Check for empty cells |
VLOOKUP vs. Modern Alternatives
- XLOOKUP: More flexible, can search right-to-left
- INDEX/MATCH: More powerful, works with any column arrangement
- VLOOKUP: Simple, widely understood, good for basic lookups