How to Use VLOOKUP Formula in Excel

VLOOKUP is one of the classic Excel formulas people learn when they need to connect two tables. It helps you type one value, such as a product code or employee ID, and return related information from a lookup table. Once you understand the structure, you can use it for price lists, staff records, invoice sheets, stock files, student records, and many everyday spreadsheet tasks.

What VLOOKUP means

VLOOKUP stands for "vertical lookup". It searches down the first column of a table, finds the value you ask for, and returns a value from another column in the same row.

The basic formula is:

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

Here is what each part means:

• lookup_value - the value you want Excel to search for, such as P-1001
• table_array - the table that contains your lookup value and the result columns
• col_index_num - the column number to return from inside the selected table
• range_lookup - use FALSE for exact match, which is best for most office work

Simple example

Imagine you have a product table:

| Code | Product | Price | | --- | --- | --- | | P-1001 | Wireless Mouse | 699 | | P-1002 | USB-C Cable | 299 | | P-1003 | Laptop Stand | 1499 |

If cell A2 contains P-1001, this formula returns the product name:

=VLOOKUP(A2,$A$6:$C$8,2,FALSE)

To return the price instead, change the column number from 2 to 3:

=VLOOKUP(A2,$A$6:$C$8,3,FALSE)

Step-by-step: how to use VLOOKUP in Excel

1. Put the lookup value in the first column of your table. VLOOKUP can only search the first column of the table range you select.
2. Select the full lookup table. Include the lookup column and the columns you want to return.
3. Count the return column. If your selected table starts at Code, then Code is column 1, Product is column 2, Price is column 3, and so on.
4. Use FALSE for exact match. For product codes, IDs, invoice numbers, and names, exact match avoids wrong results.
5. Lock the table range. Use dollar signs like $A$6:$C$8 so the range does not move when you copy the formula.
6. Wrap with IFERROR if needed. IFERROR lets you show a blank cell or friendly message instead of #N/A.

Why exact match matters

The last part of VLOOKUP is easy to ignore, but it matters. If you use TRUE or leave the final argument blank, Excel performs an approximate match. That can be useful for grading bands or tax slabs, but it can return surprising results if your table is not sorted.

For most business templates, use:

FALSE

This forces Excel to return a result only when it finds the exact lookup value.

Common VLOOKUP mistakes

• The lookup value is not in the first column. VLOOKUP searches only the first column of the selected table.
• The column number is wrong. Count from the left edge of your selected table, not from column A of the worksheet.
• The table range moves when copied. Use absolute references such as $A$6:$C$8.
• Extra spaces break the match. P-1001 and P-1001 are not the same value to Excel.
• Numbers are stored as text. Make sure both lookup values use the same data type.
• Approximate match is used by accident. Add FALSE as the final argument.

VLOOKUP with IFERROR

When Excel cannot find a match, VLOOKUP returns #N/A. That is useful while checking errors, but it can look messy in a final template.

Use this pattern to show a blank cell instead:

=IFERROR(VLOOKUP(A2,$A$6:$C$8,2,FALSE),"")

Or show a message:

=IFERROR(VLOOKUP(A2,$A$6:$C$8,2,FALSE),"Not found")

Practice with a free template

The fastest way to learn VLOOKUP is to edit a working file. Download our free VLOOKUP formula template and open the Product Lookup sheet. Choose a product code, watch the formula return product name, category, price, and stock, then inspect the formula cells.

The same workbook also includes an Employee Lookup sheet and a Quick Guide sheet, so you can practice the formula in more than one real-world context.

VLOOKUP vs XLOOKUP

Newer versions of Excel include XLOOKUP, which is more flexible because it can look left, return multiple columns, and has built-in not-found handling. Still, VLOOKUP remains common in older workbooks, office templates, shared spreadsheets, and interviews. If you understand VLOOKUP, you will read and fix many existing Excel files more confidently.

Summary

Use VLOOKUP when you need Excel to search for a value in a table and return related information from the same row. Keep the lookup value in the first column, lock your table range, count the return column carefully, and use FALSE for exact match. For practice, start with the VLOOKUP formula template, then replace the sample lookup tables with your own data.

FAQ

When should I use VLOOKUP versus XLOOKUP?

Use XLOOKUP if you are on Excel 2021 or Microsoft 365 — it is easier, searches in any direction, and does not depend on column position. Use VLOOKUP if you are on an older version of Excel, if your team shares files with people on older versions, or if you already have working VLOOKUPs you do not want to rewrite.

Why does my VLOOKUP return #N/A?

Almost always one of four reasons: the lookup value has extra spaces (use TRIM), it is stored as text while the table has numbers (or the other way around), the lookup column is not the first column of the table range, or the value simply does not exist in the table. Wrap in IFERROR to display a friendly message instead of #N/A.

What does FALSE mean at the end of VLOOKUP?

FALSE (or 0) tells VLOOKUP to find an exact match. TRUE (or 1) tells it to find an approximate match, which requires the lookup column to be sorted and is mostly used for tax slabs or grade bands. For almost every everyday lookup — product codes, IDs, prices — always use FALSE.

Can VLOOKUP search from right to left?

No. VLOOKUP can only look up values in the first column of the table range and return a column to its right. To search leftwards, either rearrange the table so the lookup column comes first, use INDEX + MATCH, or use XLOOKUP if available.