Why you need this
The classic Vertical Lookup. It searches for a value in the first column of a table and returns a value in the same row from a specified column.
Warning: It breaks if you insert a column. Use XLOOKUP if possible.
One-Click Copy
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Copy
Quick context
- • Works in both Excel and Google Sheets.
- • Use when you need consistent, auditable results.
- • Copy the snippet above and adjust only the ranges.
When to use
Learn how to use the Excel VLOOKUP Formula formula.
Disclaimer: While we strive for accuracy, these formulas are provided "as is" without warranty of any kind. Please verify all results before use.
Common Mistakes
- ✕ Forgetting 'FALSE' or '0' for exact match. The default match type is approximate, which can return incorrect results for unique identifiers like IDs or Names.
- ✕ Table array not locked. When dragging the formula, the reference range shifts if not locked with absolute references (e.g., $A$1:$B$10).
- ✕ Lookup value not in first column. VLOOKUP can only look to the right. The lookup value MUST be in the very first column of your selected table array.
Best Practices
- ✓ Use XLOOKUP if available. XLOOKUP replaces VLOOKUP, offering better defaults, no column counting, and left-lookup capabilities.
- ✓ Use Tables. References like Table1[Column] are dynamic and automatically expand when new data is added, unlike static ranges.
- ✓ Lock references with F4. Always press F4 to toggle absolute references ($) for your table array to prevent it from moving when you copy the formula.
Scalability Warning
VLOOKUP breaks at 10k rows and is fragile to column inserts.
See data Alternatives