VLOOKUP Function in Excel: Complete Guide (2025)
Quick Answer: VLOOKUP finds values in tables: =VLOOKUP(lookup_value, table_range, column_number, FALSE). Example: =VLOOKUP("A123", A:C, 3, FALSE) finds A123 in column A, returns column C value. Use FALSE for exact match, TRUE for approximate.
What is VLOOKUP?
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a corresponding value from another column in the same row. It's Excel's most popular lookup function.
Simple Example:
=VLOOKUP("A123", A2:C100, 3, FALSE)
// Find "A123" in column A
// Return value from column C (3rd column of range)
// FALSE = exact match onlyVisual: Searches DOWN column A → Finds match → Goes RIGHT to column 3 → Returns that value
Product Lookup
=VLOOKUP("SKU123", Products, 2, FALSE)
Find product SKU, return price
Employee Data
=VLOOKUP(EmpID, A:E, 4, FALSE)
Find employee ID, return department
💡 Common Use Cases
- • Price lists: Find product code, return price
- • Employee databases: Look up ID, return name/dept/salary
- • Customer data: Find customer ID, get contact info
- • Grade lookups: Find score, return letter grade
- • Inventory: Search SKU, return quantity/location
VLOOKUP Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
1. lookup_value (required)
The value to search for in the first column of the table.
"A123" ← Text valueB5 ← Cell reference1001 ← Number2. table_array (required)
The table range to search. First column must contain lookup values.
A2:D100 ← Table rangeProducts ← Named range$A$2:$D$100 ← Absolute reference (for copying)3. col_index_num (required)
Column number in table_array to return value from (1st column = 1).
2 ← Return 2nd column5 ← Return 5th column⚠️ Counts from table_array start, not sheet column letters!
4. range_lookup (optional)
Match type: FALSE (exact) or TRUE (approximate).
FALSE or 0 ← Exact match (recommended)TRUE or 1 ← Approximate match (requires sorted data)💡 Always use FALSE unless you need grade ranges or tax brackets
⚠️ VLOOKUP Limitations
- • Right-side only: Can only return columns to the RIGHT of lookup column
- • First column search: Lookup value must be in first column of table_array
- • Exact column number: Must know column position (breaks if columns added/removed)
- • Single match: Returns only first match found
- 💡 Modern alternative: Use XLOOKUP (Excel 365) or INDEX MATCH for more flexibility
Example Breakdown
=VLOOKUP(B2, Products!A:D, 3, FALSE)
B2: Look for value in cell B2
Products!A:D: Search in columns A-D on Products sheet
3: Return value from 3rd column (column C)
FALSE: Exact match only, no approximation
15+ VLOOKUP Examples
1. Basic Exact Match
=VLOOKUP("SKU123", A2:C100, 2, FALSE)Find "SKU123" in column A, return value from column B (2nd column)
2. Cell Reference Lookup
=VLOOKUP(E2, A2:D100, 3, FALSE)
Look up value from E2, return 3rd column of table
3. Absolute Reference (Copy Formula)
=VLOOKUP(A2, $F$2:$H$100, 2, FALSE)
$ locks table range when copying formula down
4. Named Range
=VLOOKUP(B2, PriceList, 3, FALSE)
Use named range "PriceList" for cleaner formula
Error Handling
5. Handle #N/A Error
=IFERROR(VLOOKUP(A2, B:D, 2, FALSE), "Not Found")
Returns "Not Found" if lookup fails
6. Return Blank Instead of Error
=IFERROR(VLOOKUP(A2, Table, 3, FALSE), "")
Shows blank cell if no match
7. Check If Value Exists
=IF(ISNA(VLOOKUP(A2, B:C, 1, FALSE)), "Missing", "Found")
Approximate Match (Grade Ranges)
8. Letter Grade Lookup
=VLOOKUP(B2, GradeTable, 2, TRUE)
Find closest grade (90=A, 80=B, etc.) - table must be sorted!
9. Tax Bracket
=VLOOKUP(Income, TaxBrackets, 2, TRUE)
Find tax rate based on income range
Advanced Techniques
10. Cross-Sheet Lookup
=VLOOKUP(A2, Sheet2!A:D, 3, FALSE)
Look up data from another sheet
11. Dynamic Column Number
=VLOOKUP(A2, Table, MATCH("Price", Table[#Headers], 0), FALSE)Find column by header name instead of number
12. Wildcard Search
=VLOOKUP("*apple*", A:B, 2, FALSE)Find cells containing "apple" anywhere
13. Two-Way Lookup (VLOOKUP + MATCH)
=VLOOKUP(A2, Data, MATCH(C1, Headers, 0), FALSE)
Lookup both row (A2) and column (C1) dynamically
14. Multiple VLOOKUPs
=VLOOKUP(A2, Table1, 2, FALSE) + VLOOKUP(A2, Table2, 2, FALSE)
Sum values from multiple tables
15. Nested VLOOKUP
=VLOOKUP(VLOOKUP(A2, Table1, 2, FALSE), Table2, 3, FALSE)
Use result of first lookup as input for second
🎯 Pro Tips
- • Always use FALSE: Unless you specifically need approximate match
- • Lock ranges: Use $ to create absolute references: $A$2:$D$100
- • Named ranges: Easier to read and maintain
- • IFERROR wrapper: Always handle #N/A errors for better UX
- • Consider alternatives: XLOOKUP (Excel 365) or INDEX MATCH for more flexibility
Frequently Asked Questions
Why is my VLOOKUP returning #N/A?
#N/A means value not found. Common causes: 1) Lookup value doesn't exist in first column, 2) Extra spaces (use TRIM function), 3) Numbers stored as text vs actual numbers, 4) Typos or case differences, 5) Table range doesn't include lookup column. Use IFERROR to handle gracefully.
What's the difference between FALSE and TRUE in VLOOKUP?
FALSE (or 0) = Exact match only, returns #N/A if not found. TRUE (or 1) = Approximate match, finds closest value less than or equal to lookup (requires sorted data ascending). Use FALSE 99% of the time unless doing grade ranges or tax brackets.
Can VLOOKUP look to the left?
No, VLOOKUP can only return columns to the RIGHT of the lookup column. For left lookup, use INDEX MATCH: =INDEX(return_column, MATCH(lookup_value, lookup_column, 0)). Or use XLOOKUP in Excel 365 which allows any direction.
Why does VLOOKUP break when I insert columns?
Column number (col_index_num) is hardcoded. If you insert columns into table_array, the number becomes wrong. Solutions: 1) Use MATCH to find column dynamically, 2) Use named ranges, 3) Switch to INDEX MATCH or XLOOKUP which don't have this issue.
How do I VLOOKUP from another sheet or workbook?
Another sheet: =VLOOKUP(A2, Sheet2!A:C, 2, FALSE). Another workbook: =VLOOKUP(A2, '[OtherFile.xlsx]Sheet1'!A:C, 2, FALSE). The other workbook must be open. For closed workbooks, use INDIRECT or Power Query.
VLOOKUP vs INDEX MATCH - which is better?
INDEX MATCH is more flexible: works left/right, won't break if columns inserted, slightly faster for large datasets. VLOOKUP is simpler and more widely known. Use VLOOKUP for simple right-side lookups, INDEX MATCH for complex cases. Or use XLOOKUP (Excel 365) which combines best of both.