Excel VLOOKUP Formula Example

Reading the VLOOKUP syntax on its own rarely makes the function click the way a worked example does. This page walks through a realistic scenario - matching product prices from a price list into a sales sheet - showing exactly what each argument looks like when it's filled in with real data instead of placeholders.

After the example, you'll also see how Turbo Excel Lookup performs the identical match using a visual interface instead of a formula, which is worth knowing if your own data isn't as clean as the example below.

Imagine Sheet1 contains a list of orders with a Product ID in column A, and Sheet2 contains a product catalog with Product ID in column A and Price in column C. To pull each order's price into Sheet1 column B, the formula in B2 would be:

Here, A2 is the Product ID being searched for, Sheet2!A:C is the full range containing the catalog, 3 tells Excel to return the third column of that range (Price), and FALSE forces an exact match so a partial or approximate Product ID doesn't return the wrong price.

1. Enter the formula once in the first data row, e.g. B2.
2. Change the table_array to absolute references, e.g. Sheet2!$A$1:$C$500, so it doesn't shift when you copy the formula.
3. Select B2, copy it, and paste it into the remaining rows of column B.
4. Check for #N/A results - these indicate Product IDs in Sheet1 that don't exist in the Sheet2 catalog.

This locking step trips up a lot of beginners: without the dollar signs anchoring the range, copying the formula down shifts the table_array by one row each time, quietly breaking the lookup for every row after the first.

In Turbo Excel Lookup, the same task looks different: you load the orders file as the Base File and the catalog as the Lookup Table, add a Key Column Pair matching Product ID to Product ID, tick the Price column to pull back, and click Run Lookup. There's no absolute-reference step to remember and no risk of the range silently shifting, because the match runs once across every row in a single operation rather than being copied cell by cell.

The app also reports a match percentage automatically, so instead of scrolling through hundreds of rows hunting for #N/A, you get an instant count of how many Product IDs failed to match and can export just those rows for review.

A small but frequently requested variation on the example above is showing a blank cell or custom message instead of #N/A when a Product ID doesn't have a match. Wrapping the formula in IFERROR handles this cleanly:

The empty quotes at the end mean any unmatched row shows a blank cell instead of an error, which looks cleaner in a printed report but also makes it easy to overlook genuinely missing data if you're not checking separately. A safer middle ground is using a visible placeholder like "Not in catalog" instead of an empty string, so unmatched rows remain noticeable without cluttering the sheet with red error triangles.

The two-row example above scales conceptually to a real order sheet with thousands of rows without any change to the formula itself - only the range needs to be locked and copied further down. In practice, though, larger datasets are exactly where the manual steps (locking references, copying formulas, scanning for #N/A) become the most time-consuming part of the task, which is the main reason a dedicated matching tool starts to make more sense as row counts grow into the thousands or tens of thousands.

Even a correctly built VLOOKUP formula benefits from a final review pass before its results are used for something important, like an invoice or a payroll run. Spot-checking a handful of rows against the original source data, beyond just confirming the formula returns something rather than an error, catches cases where a formula technically "worked" but matched the wrong record due to a coincidental duplicate key elsewhere in the table. This same review habit applies just as well to a Turbo Excel Lookup export - checking the match-percentage statistic alongside a manual spot-check of a few rows before relying on the result for anything consequential.

Working through a concrete example, argument by argument, is the fastest way to internalize how VLOOKUP behaves in practice. Once this specific example makes sense, adapting it to a new dataset with different column names and ranges becomes a matter of substitution rather than relearning the function from scratch each time.