VLOOKUP is one of the most useful formulas in Excel, and once you understand how it works, it will save you hours of manual searching. It lets you look up a value in one column and automatically pull back a related piece of information from the same table — for example, finding a product price from a list by typing the product name. This guide explains every part of the formula in plain English, with a practical example and fixes for the most common errors.
What VLOOKUP Actually Does
VLOOKUP stands for Vertical Lookup. You give it a value to search for, point it at a table of data, tell it which column to return, and it finds the matching row and hands back the value in that column. Think of it like using an index in a book — you look up the topic, find the page number, and turn to it.
A common example: you have a product code in cell A2 and a price list table elsewhere. VLOOKUP can find that product code in the table and return its price automatically — without you scrolling through hundreds of rows.
The Four Arguments Explained
The full syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
1. lookup_value
This is the value you want to find — for example, a product code, a name, or an order number. You can type the value directly (e.g. "Widget A") or reference a cell (e.g. A2). Using a cell reference is usually better because it means you can change the search value without editing the formula.
2. table_array
This is the range of cells that contains your lookup table — for example, D2:F100. The first column of this range must be the column you want to search. VLOOKUP always searches the leftmost column of your table_array, which is one of its main limitations (more on that later).
3. col_index_num
This is a number that tells Excel which column to return the result from. If your table_array is D2:F100, column 1 is D, column 2 is E, and column 3 is F. So if you want the value from column F, you’d enter 3.
4. range_lookup
This is where many people go wrong. You have two options:
- FALSE — exact match. Use this for almost everything. It finds only an exact match to your lookup_value.
- TRUE — approximate match. This is for sorted numerical ranges, like tax bands or commission tiers. It finds the closest value that is less than or equal to your lookup_value. If your data isn’t sorted in ascending order, you’ll get wrong answers.
For most everyday tasks — looking up names, product codes, order numbers — always use FALSE.
A Practical Example: Looking Up a Price
Say you have a list of orders in columns A and B (order ID and product name), and a price list in columns E and F (product name and price). You want to fill column C with the price for each product.
In cell C2, you’d write:
=VLOOKUP(B2, E:F, 2, FALSE)
This tells Excel: look for the value in B2, search column E, and return the corresponding value from column F (the second column of the range E:F). Using entire columns like E:F instead of E2:F100 means you don’t need to update the formula as new rows are added.
Locking the Table with Dollar Signs
If you copy the formula down the column, you need the table_array reference to stay fixed. Without locking it, Excel will shift the reference down as you copy, which breaks the formula.
To lock a reference, add dollar signs: $E$2:$F$100 or just use full column references like E:F. The dollar signs tell Excel not to adjust those references when the formula is copied. Press F4 after selecting a range to add dollar signs automatically.
Common Errors and What Causes Them
#N/A
This means VLOOKUP couldn’t find your lookup_value in the first column of the table. Common causes:
- The value doesn’t exist in the table (check for typos)
- Extra spaces in the lookup value or the table data (use
TRIM()to remove them) - A number stored as text in one place and as a real number in another — they look identical but Excel treats them as different values
#REF!
This usually means your col_index_num is higher than the number of columns in your table_array. For example, if your table is only 2 columns wide and you ask for column 3, you’ll get a #REF! error. Count your columns and adjust the number.
#VALUE!
This typically means the col_index_num argument is less than 1, or contains text rather than a number. Make sure you’ve entered a whole number like 2 or 3, not text.
Exact Match vs Approximate Match in Practice
The approximate match (TRUE) is useful when you have a graded scale. For example, a commission rate table where 0–9 units = 5%, 10–19 units = 8%, 20+ units = 12%. With TRUE, VLOOKUP will find the correct band for any number of units, as long as the table is sorted in ascending order.
For anything else — names, codes, text values — always use FALSE. It’s safer and more predictable.
Limitations of VLOOKUP
VLOOKUP has two significant limitations worth knowing about:
- It can only look right. The search column must always be the leftmost column in your table_array. You can’t use VLOOKUP to search column C and return a value from column A — the return column must be to the right of the search column.
- It uses a column number, not a name. If someone inserts a new column into your table, the col_index_num becomes wrong and your formula returns data from the wrong column. This is a common source of silent errors in spreadsheets.
When to Use XLOOKUP Instead
If you’re using Excel 365 or Excel 2021, consider using XLOOKUP instead. It fixes both limitations above — it can look left, uses direct column references rather than a number, and handles missing values more cleanly. It also has a built-in argument to replace the #N/A error with a custom message like “Not found”. If you’re sharing files with people on older versions of Excel, VLOOKUP is still the safer choice for compatibility.
Quick Tips Before You Start
- Make sure there are no blank rows or columns in your lookup table
- Keep your lookup table on the same sheet or clearly referenced if it’s on another sheet
- If your data has leading/trailing spaces, use
=VLOOKUP(TRIM(A2), ...) - Use
IFERROR(VLOOKUP(...), "Not found")to replace the #N/A error with a friendly message
VLOOKUP is a formula worth practising until it becomes second nature. Once you’re comfortable with it, you’ll use it constantly — and when you’re ready to move beyond it, XLOOKUP is waiting.
