Excel VLOOKUP Basics — Pull Values from Another Table

Ever spent time flipping between two spreadsheets, copying prices or contact details row by row? VLOOKUP eliminates that. Type a product name once and Excel pulls the matching price, department, or phone number from another table automatically — even across hundreds of rows. This guide walks through the syntax, a hands-on example, common errors, and when to graduate to XLOOKUP.

VLOOKUP Syntax and Arguments

The full formula looks like this:

=VLOOKUP(lookup_value, table_range, col_index, FALSE)

Here is what each argument does:

• lookup_value — The value you want to search for. Type it directly or reference a cell, e.g. A2 (a cell containing a product name).
• table_range — The full range of your lookup table. The value you are searching for must be in the first column of this range. Example: $E$2:$G$100. Lock it with F4 (adds $ signs) so the range does not shift when you copy the formula down.
• col_index — Which column of table_range to return. Columns are numbered left to right starting at 1. If you want the second column, enter 2.
• FALSE (or 0) — Exact match. In almost every real-world scenario this is what you want. Using TRUE or omitting the argument enables approximate match, which requires the first column to be sorted in ascending order and can silently return wrong results if it is not.

Step-by-Step Example — Fetch a Unit Price by Product Name

Say column A holds an order list (product names) and columns E–G hold a price table (product name, unit price, stock). You want B2 to show the unit price automatically.

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

• A2 — the product name in your order list (lookup value)
• $E$2:$G$100 — the price table; column E contains the product names, locked with F4
• 2 — return the second column of the price table (unit price)
• FALSE — exact match only

Enter the formula in B2, then drag the fill handle downward. Because the table range is locked with absolute references, it stays fixed as you copy the formula to every row.

Common Errors and How to Fix Them

#N/A — Value Not Found

VLOOKUP returns #N/A when the lookup value does not exist in the first column of table_range. The most common culprits are a trailing space in one of the values or a spelling difference. To display a friendlier message instead, wrap the formula in IFERROR:

=IFERROR(VLOOKUP(A2, $E$2:$G$100, 2, FALSE), "Not found")

If a match is found, the price appears. If not, "Not found" is shown instead.

Wrong Column Index

If col_index is larger than the number of columns in table_range (for example, entering 4 when the range only has 3 columns), Excel returns #REF!. Count the columns in your table range before entering the number.

Forgetting Absolute References

If you copy a formula down and the results look wrong, check whether table_range has dollar signs ($). Without them, the range shifts down with each row. Click inside the range in your formula bar and press F4 to add absolute references: $E$2:$G$100.

XLOOKUP — The More Flexible Modern Alternative

If you have Microsoft 365 or Excel 2021 or later, consider XLOOKUP. It separates the search range from the return range, so the lookup value does not have to be in the first column — you can look up by any column and return values to the left or right freely.

=XLOOKUP(lookup_value, lookup_range, return_range)

Example: =XLOOKUP(A2, $E$2:$E$100, $F$2:$F$100). Exact match is the default, so no extra argument is needed. If you share files with colleagues on older Excel versions or Google Sheets, VLOOKUP remains the safer, universally compatible choice.

Pair It with These Tools

Once your spreadsheet is ready, you may need to export it as a PDF for a client or import a PDF table back into Excel for further analysis. PDF to Excel conversion does this in the browser without any installation — handy for quotes, invoices, and reports.

Frequently Asked Questions

Q. Is FALSE required at the end of VLOOKUP?
In practice, yes. Without it (or with TRUE), VLOOKUP uses approximate match, which requires the first column of your table to be sorted in ascending order. If it is not sorted, the function can silently return the wrong value.

Q. What if the value I want to look up is in the second column, not the first?
VLOOKUP can only search the first column of table_range — it cannot look left. Rearrange your table so the lookup column is first, or use XLOOKUP (=XLOOKUP(lookup_value, lookup_range, return_range)) if your Excel version supports it.

Q. My formula results look wrong after copying it down. What happened?
The table_range is probably missing absolute references. Click inside the range in the formula bar and press F4 to lock it: $E$2:$G$100. Then copy the formula again.

Q. Can I display a blank cell or zero instead of #N/A?
Yes. Use =IFERROR(VLOOKUP(...), "") for a blank cell or =IFERROR(VLOOKUP(...), 0) for zero. Replace the second argument of IFERROR with whatever you want displayed when no match is found.