VLOOKUP in Excel: Explanation with Examples

With VLOOKUP, you look up a value in the first column of a table and retrieve the corresponding result from a column to the right. The basic syntax is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Use FALSE for an exact match, which you almost always need.

How Does VLOOKUP Work?

VLOOKUP stands for “vertical lookup.” The function takes a value you know (like a product code), searches for it top to bottom in the leftmost column of your table, and then returns a value from the same row but from a column you specify by number. This lets you link a code to a price, a customer number to a name, or an item to its stock level in seconds.

There’s an important limitation: VLOOKUP always looks to the right. The lookup column must be the first column of your table_array, and the column with the answer must be to its right. VLOOKUP cannot look left. Remember that, and half of the common error messages are already solved.

VLOOKUP Syntax Explained

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: the value you’re searching for, e.g., a cell with a product code or the text “C310”.
table_array: the entire range where Excel searches. The first column of this range is the lookup column.
col_index_num: the column number of the answer, counting from the first column of the table_array. The first column is 1, the second is 2, and so on.
[range_lookup]: use FALSE for an exact match. TRUE (or omitted) gives an approximate match and requires the first column to be sorted in ascending order.

When to Use VLOOKUP?

VLOOKUP is at its best when you want to link two lists based on a shared key. Think of an order list with only product codes where you want to look up descriptions, prices, or stock levels from a separate item list. Instead of typing everything manually, let Excel fetch the correct value. If the source list changes, your results update automatically. That saves time and prevents typos.

VLOOKUP Example with a Table

Suppose you have an item list in the range A2:C5. Column A contains product codes, column B descriptions, and column C prices in euros. You want to look up both the price and description for code C310.

Product Code (column A)	Description (column B)	Price (column C)
A100	USB-C cable	€ 12.50
B205	Wireless mouse	€ 34.00
C310	Mouse pad XL	€ 8.75
D415	Mechanical keyboard	€ 129.00

If you want to rebuild this example yourself, you’ll need Excel alongside this data. Are you working with an expired trial version or an older version without the latest features? Then this is a genuine, one-time Office 2021 with Excel included, delivered within minutes by email, including an invoice with VAT.

For the price, the answer is in the third column of the table_array, so you use column index 3:

=VLOOKUP("C310", A2:C5, 3, FALSE)

The result is 8.75. Excel searches for “C310” in the first column (A), finds the row, and returns the value from column 3 of the table_array, which is column C. If you want the description instead, count the columns again: the description is in the second column, so =VLOOKUP("C310", A2:C5, 2, FALSE) returns “Mouse pad XL”. If you don’t want to type the code each time, reference a cell: if the code is in E2, use =VLOOKUP(E2, A2:C5, 3, FALSE).

Entering VLOOKUP in Five Steps

Click in the cell where the answer should appear and type =VLOOKUP(.
Click on the cell with the lookup value, or type it, and add a comma.
Select the entire table_array, including the lookup column on the left, and add a comma.
Type the column number with the desired answer and add a comma.
Type FALSE, close with a parenthesis, and press Enter.

Partial Search with Wildcards

If you only know part of the lookup value, a wildcard helps. The asterisk (*) stands for any number of characters. With =VLOOKUP("C*", A2:C5, 3, FALSE), Excel finds the first code starting with “C”. Handy for long article names, but be careful: if there are multiple matches, you only get the first one.

Tip: Lock the Table Array with Absolute References

If you want to copy the formula down for a whole list of codes, lock the table_array with dollar signs. Without locking, the range shifts down and you’ll get errors at the bottom. So use:

=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)

The dollar signs in $A$2:$C$100 keep the range fixed while E2 moves down to E3, E4, and so on. Press F4 after selecting a range to quickly add the dollar signs.

Handling Errors Gracefully with IFERROR

If you don’t want ugly #N/A messages in your overview when a code is missing, wrap VLOOKUP in IFERROR. That function shows your own text when the lookup fails:

=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "Not found")

If Excel finds the code, you get the price; if the code is missing, it neatly says “Not found” instead of an error. This keeps your overviews readable, even with large lists.

Why Does VLOOKUP Return #N/A?

The error #N/A (“not available”) means VLOOKUP couldn’t find the lookup value in the first column. The most common causes:

The value really isn’t there, or it’s not in the leftmost column of the table_array.
A number is stored as text (or vice versa). “00123” as text is different from the number 123 to Excel. Make sure both are the same type.
There are spaces before or after the value. Use =TRIM(A2) to remove extra spaces.
You forgot the fourth argument. Without FALSE, Excel does an approximate lookup and may return an error or the wrong answer.

Other Common Errors: #REF! and Wrong Answer

If you get #REF!, then col_index_num is greater than the number of columns in your table_array. If you ask for column 4 in a table of three columns, that column doesn’t exist. Lower the number or expand the table_array.

If the formula is correct but the answer seems illogical, you almost certainly forgot FALSE. With TRUE (approximate), Excel picks the closest lower value, and that only works reliably if the first column is sorted ascending. Add FALSE and the problem is solved.

VLOOKUP vs XLOOKUP

In Office 2021 and Microsoft 365, there’s a modern successor: XLOOKUP. It can look to the left, has an exact match by default, and uses a cleaner syntax where you specify the lookup column and result column separately. The basic form is:

=XLOOKUP(lookup_value, lookup_array, return_array)

For our example, that would be =XLOOKUP("C310", A2:A5, C2:C5). No column counting, no remembering FALSE, and the result column can be left of the lookup column. If you work with a team that still uses older Excel versions, VLOOKUP remains the safest choice because XLOOKUP isn’t available there. For new files in Office 2021, XLOOKUP is usually more convenient.

Summary

VLOOKUP is one of the most useful functions in Excel: link two lists together with a single formula. Remember the four arguments, always use FALSE for an exact match, lock the table_array with dollar signs when copying, and make sure the lookup column is on the left. If you run into #N/A, check text vs number and extra spaces. And if you want the modern route, try XLOOKUP in Office 2021.