Quick Answer: VLOOKUP searches for data vertically in columns, while HLOOKUP searches horizontally in rows. Both functions let you find a value in one column/row and return related information from another. Use =VLOOKUP(lookup_value, table_range, column_number, FALSE) for exact matches or =HLOOKUP(lookup_value, table_range, row_number, FALSE) for horizontal lookups.
What VLOOKUP and HLOOKUP Actually Do
VLOOKUP (Vertical Lookup) and HLOOKUP (Horizontal Lookup) are Excel’s search functions.
They work like digital phonebooks. You provide a name, and Excel finds the matching phone number.
VLOOKUP searches down columns. Your lookup value must be in the leftmost column.
HLOOKUP searches across rows. Your lookup value must be in the top row.
Excel Versions Supported: These functions work in Excel 2007, 2010, 2013, 2016, 2019, Excel 2021, and Microsoft 365.
Note: Microsoft now recommends XLOOKUP for newer Excel versions. It’s more flexible and easier to use.
Understanding VLOOKUP Function
VLOOKUP Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Four arguments explained:
- lookup_value: What you’re searching for (like an employee ID)
- table_array: Where to search (your data range)
- col_index_num: Which column contains your answer (count from left)
- range_lookup: FALSE for exact match, TRUE for approximate match
VLOOKUP Example: Finding Employee Information
Let’s say you have an employee database. You know someone’s ID and need their department.
Your data looks like this:
| Employee ID | Name | Department | Salary |
| 101 | Sarah | Marketing | $65,000 |
| 102 | James | Sales | $58,000 |
| 103 | Maria | IT | $72,000 |
| 104 | David | HR | $55,000 |
To find the department for employee 103:
=VLOOKUP(103, A2:D5, 3, FALSE)
Result: IT
Why this works:
- 103 is your lookup value
- A2:D5 is your table range
- 3 means return the value from the 3rd column (Department)
- FALSE means find an exact match
Step-by-Step VLOOKUP Tutorial
Step 1: Click the cell where you want the result.
Step 2: Type =VLOOKUP(
Step 3: Click the cell containing your lookup value (or type it directly).
Step 4: Type a comma, then select your data table range.
Step 5: Type a comma, then enter the column number you want returned.
Step 6: Type ,FALSE) for exact match and press Enter.
Pro Tip: Use $ symbols to lock your table range: $A$2:$D$5. This prevents errors when copying formulas.
Understanding HLOOKUP Function
HLOOKUP Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
The difference from VLOOKUP:
- Data is arranged in rows instead of columns
- You specify a row number instead of column number
- Lookup value must be in the first row
HLOOKUP Example: Monthly Sales Report
Your data looks like this:
| Jan | Feb | Mar | Apr | |
| Sales | $45K | $52K | $48K | $55K |
| Costs | $30K | $35K | $32K | $36K |
| Profit | $15K | $17K | $16K | $19K |
To find March sales:
=HLOOKUP(“Mar”, B1:E4, 2, FALSE)
Result: $48K
Why this works:
- “Mar” is what you’re looking for
- B1:E4 is your table range
- 2 means return the value from row 2 (Sales)
- FALSE ensures exact match
Step-by-Step HLOOKUP Tutorial
Step 1: Select your result cell.
Step 2: Type =HLOOKUP(
Step 3: Enter or click your lookup value.
Step 4: Add a comma and select your table range.
Step 5: Type the row number containing your desired value.
Step 6: Type ,FALSE) and press Enter.
Exact Match vs. Approximate Match
Using FALSE (Exact Match)
Most people need exact matches. FALSE finds precisely what you’re looking for.
=VLOOKUP(“James”, A2:D5, 2, FALSE)
If “James” doesn’t exist, you get a #N/A error.
When to use FALSE:
- Looking up employee records
- Finding product codes
- Matching invoice numbers
- Any situation requiring precision
Using TRUE (Approximate Match)
TRUE finds the closest match less than your lookup value.
Important: Your first column/row must be sorted in ascending order.
=VLOOKUP(85, A2:C10, 2, TRUE)
If your lookup value is 85 but only 80 and 90 exist, Excel returns the 80 row.
When to use TRUE:
- Tax brackets
- Grade ranges
- Pricing tiers
- Discount levels
Common VLOOKUP and HLOOKUP Errors
#N/A Error
What it means: Excel can’t find your lookup value.
Common causes:
- Misspelled lookup value
- Extra spaces in data
- Lookup value doesn’t exist
Fix: Check spelling, use TRIM function to remove spaces, or verify data exists.
#REF! Error
What it means: Your column/row number is invalid.
Common causes:
- Column number exceeds your table range
- Deleted columns changed your table
Fix: Verify your column/row number is within your table range.
#VALUE! Error
What it means: Wrong data type or invalid reference.
Common causes:
- Column/row number is less than 1
- Non-numeric value in number field
Fix: Ensure column/row numbers are positive integers.
#NAME? Error
What it means: Excel doesn’t recognize the formula.
Common causes:
- Misspelled function name
- Missing quotation marks around text
Fix: Check spelling and add quotes around text values.
Advanced VLOOKUP Techniques
Using Cell References
Instead of typing values, reference cells for dynamic formulas:
=VLOOKUP(F2, A2:D100, 3, FALSE)
Change F2’s value, and your result updates automatically.
Combining with IFERROR
Avoid ugly #N/A errors with a friendly message:
=IFERROR(VLOOKUP(A2, C2:E10, 2, FALSE), “Not Found”)
Result: Shows “Not Found” instead of #N/A when lookup fails.
Looking Up from Another Sheet
Reference different sheets in your formula:
=VLOOKUP(A2, Sheet2!A1:D100, 3, FALSE)
The Sheet2! tells Excel where to search.
Wildcard Searches
Use asterisks (*) for partial matches:
=VLOOKUP(“*smith*”, A2:C50, 2, FALSE)
Finds “Smith”, “Goldsmith”, “Smithson”, etc.
Advanced HLOOKUP Techniques
Absolute References
Lock your table range when copying formulas:
=HLOOKUP($B$1, $A$1:$F$5, 2, FALSE)
The $ symbols prevent range from shifting.
Multiple Criteria Workaround
HLOOKUP doesn’t support multiple criteria directly. Create a helper row combining values:
Helper row: Concatenate month and region: “Jan-North”
Then lookup the combined value.
Cross-Workbook HLOOKUP
Reference external workbooks:
=HLOOKUP(A2, [Budget2024.xlsx]Sheet1!$A$1:$G$10, 3, FALSE)
Both workbooks must be open for this to work properly.
When to Use VLOOKUP vs. HLOOKUP
Use VLOOKUP When:
- Data is organized in columns
- Categories run down the left side
- You’re working with typical database-style tables
- Most common scenario (95% of cases)
Use HLOOKUP When:
- Data is organized in rows
- Headers run across the top
- Working with summary reports or dashboards
- Monthly or quarterly comparison tables
Consider XLOOKUP Instead
Modern Excel versions offer XLOOKUP, which:
- Works in any direction
- Doesn’t require data organization
- Returns multiple values
- Handles errors better
Learn more about XLOOKUP from Microsoft
Combining VLOOKUP and HLOOKUP
You can nest these functions for two-way lookups.
Example: Find sales for a specific region and quarter:
=VLOOKUP(“West”, A3:E8, HLOOKUP(“Q2”, B1:E2, 2, FALSE), FALSE)
How it works:
- HLOOKUP finds which column contains “Q2”
- VLOOKUP uses that column number to find “West” sales
- Result returns the intersection value
Troubleshooting Tips
Formula Returns Wrong Value
Check these issues:
- Verify your column/row count is correct
- Ensure data is sorted for approximate matches
- Look for hidden columns affecting your count
- Check for duplicate lookup values
Formula Works Once But Fails When Copied
Problem: Relative references shift when copying.
Solution: Use absolute references with $ symbols:
=VLOOKUP(A2, $C$2:$E$100, 3, FALSE)
Case-Sensitive Lookups
VLOOKUP and HLOOKUP ignore case. “SMITH” matches “smith”.
For case-sensitive lookups: Use EXACT function combined with INDEX/MATCH.
Lookup Value Has Spaces
Trailing spaces cause lookup failures.
Solution: Wrap lookup value in TRIM function:
=VLOOKUP(TRIM(A2), C2:E100, 2, FALSE)
Best Practices
Always Use Absolute References for Tables
Lock your table range to prevent errors:
$A$2:$D$100
Prefer FALSE for Range Lookup
Exact matches prevent unexpected results. Use FALSE unless specifically needing approximate.
Name Your Ranges
Create named ranges for easier formulas:
=VLOOKUP(A2, EmployeeData, 3, FALSE)
Much clearer than cell references.
Document Your Formulas
Add comments explaining complex lookups. Future you will appreciate it.
Test with Known Values
Before deploying, test your formula with values you can verify manually.
Frequently Asked Questions
Can VLOOKUP Look to the Left?
No. VLOOKUP only searches right from the lookup column.
Workaround: Rearrange your data or use INDEX/MATCH functions instead.
Why Does My Formula Show #N/A?
Your lookup value doesn’t exist in the first column/row.
Solutions:
- Verify spelling matches exactly
- Check for extra spaces
- Use IFERROR to handle missing values gracefully
Can I Use VLOOKUP with Multiple Criteria?
Not directly. VLOOKUP searches for a single value.
Workarounds:
- Create a helper column combining criteria
- Use SUMIFS or INDEX/MATCH/MATCH instead
- Consider using FILTER function in Excel 365
How Many Rows Can VLOOKUP Handle?
VLOOKUP works with Excel’s row limit: 1,048,576 rows.
However, large tables slow performance. Consider using Tables or Power Query for big datasets.
Should I Use VLOOKUP or INDEX/MATCH?
VLOOKUP advantages:
- Simpler syntax
- Easier for beginners
- Faster for small datasets
INDEX/MATCH advantages:
- More flexible
- Can look left
- Better for large datasets
- More dynamic
For modern Excel, consider XLOOKUP which combines the best of both.
Real-World Examples
Example 1: Product Price Lookup
Scenario: You have product codes and need to find prices.
Data structure:
| Product Code | Product Name | Price |
| A101 | Widget | $15.99 |
| A102 | Gadget | $24.99 |
| A103 | Doohickey | $8.99 |
Formula:
=VLOOKUP(“A102”, A2:C4, 3, FALSE)
Result: $24.99
Example 2: Grade Lookup with Ranges
Scenario: Assign letter grades based on numeric scores.
Data structure:
| Score | 0 | 60 | 70 | 80 | 90 |
| Grade | F | D | C | B | A |
Formula for score 85:
=HLOOKUP(85, A1:F2, 2, TRUE)
Result: B
Notice TRUE allows approximate match for grade ranges.
Example 3: Multi-Sheet Employee Directory
Scenario: Search employee info stored on different sheets.
Formula:
=VLOOKUP(A2, Employees!$A$2:$D$500, 4, FALSE)
This searches the “Employees” sheet for department information.
Alternative Functions to Consider
XLOOKUP (Excel 365)
Modern replacement for VLOOKUP/HLOOKUP. More powerful and flexible.
Microsoft XLOOKUP documentation
INDEX and MATCH
More versatile combination. Works in any direction.
FILTER Function
Returns multiple matching records instead of just one.
Power Query
Best for large datasets and complex lookups.
Conclusion
VLOOKUP and HLOOKUP remain essential Excel skills. They turn sprawling spreadsheets into searchable databases.
Key takeaways:
- VLOOKUP searches vertically, HLOOKUP searches horizontally
- Use FALSE for exact matches in most situations
- Lock table ranges with $ symbols when copying formulas
- Handle errors gracefully with IFERROR
- Consider XLOOKUP for modern Excel versions
Master these functions and you’ll save hours of manual data searching.
Next steps:
- Practice with your own data
- Experiment with exact vs. approximate matches
- Explore XLOOKUP for enhanced functionality
- Learn INDEX/MATCH for advanced scenarios
Additional Resources:
- Official Microsoft VLOOKUP documentation
- Official Microsoft HLOOKUP documentation
- Microsoft’s guide to lookup functions
- VLOOKUP tutorial from Spreadsheeto
- HLOOKUP tutorial from Spreadsheeto
Meta Description: Learn how to use Excel’s VLOOKUP and HLOOKUP functions with step-by-step examples. Master exact and approximate matches plus troubleshooting tips.
Tags: Excel, VLOOKUP, HLOOKUP, Excel formulas, Excel functions, lookup functions, Excel tutorial, spreadsheet tips, data analysis, Excel 365, Microsoft Excel, Excel tips, productivity