VLOOKUP in Excel: price lists, keys and matching

You have an order list and a separate price list. Orders contain product codes, but your report also needs product names and prices. VLOOKUP lets you find the appropriate row by its code and bring back a value from that row. In Polish Excel, the function is called WYSZUKAJ.PIONOWO.

I will use a small synthetic price list to explain the formula. Most of the attention goes to exact matching, duplicate keys and copying references. These are the places where a spreadsheet can produce a convincing answer to a different question from the one you intended.

What VLOOKUP does

VLOOKUP searches the first column of a specified range. When it finds a match, it returns a value from another column in the same row. You count that return column within the selected range, starting at one. The Microsoft documentation describes this structure and the available matching modes.

I start by defining the key. For a price list, that might be a product code. For an employee directory, it could be an employee identifier. A product name is often a weaker choice: names can change, two products can have similar descriptions, and an extra space can prevent an expected match.

The grain of the data matters as well. A current price list should have one relevant row per product code. If it contains price history, the code alone cannot identify the appropriate price. You also need an effective date. VLOOKUP cannot infer that business rule from the appearance of the worksheet.

Build a small price list

Enter the following synthetic data in A1:C4. Product codes are text and prices are ordinary numbers. Taxes, discounts and other pricing rules are outside this example.

Enter P-20 in E2. Put this formula in F2:

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

Excel searches A2:A4 for P-20. The match is in the second data row, which is worksheet row 3. The third column of A:C is C, so the returned value is 18. Change the third argument to 2 and the result becomes “Binder.”

The final argument, FALSE, requests an exact match. You do not need to sort product codes for this mode. These examples use English function names and commas as argument separators. If your regional settings require semicolons, adjust the separators. In Polish Excel, the equivalent formula is:

=WYSZUKAJ.PIONOWO(E2;$A$2:$C$4;3;FAŁSZ)

Function language and argument separators are configuration details. If Excel rejects a formula as you enter it, check those details before changing the data or the intended calculation.

The first column belongs to the selected range

“First column” means the leftmost column of the range you pass to the function. It does not always mean worksheet column A. If your lookup range is D:F, the search takes place in D and return column number 3 refers to F.

Suppose codes are in B, names in C and prices in D. B2:D4 is a suitable lookup range, and the price still has column number 3. Using 4 because D is the fourth worksheet column produces a reference error: the selected range only contains three columns.

Ordinary VLOOKUP returns from the key column or a column to its right. If prices are in A and codes are in B, selecting A:B does not solve the problem. Excel would search column A. In a newer workbook I would consider XLOOKUP instead, since it takes separate lookup and return ranges.

Why I enter FALSE explicitly

Leaving out the fourth argument selects approximate matching by default. That mode requires the first column to be sorted in ascending order. In an unsorted dictionary, the returned value can look plausible while being inappropriate for the requested key. For product codes, I therefore write FALSE explicitly.

An error-free cell is not sufficient evidence that a report is correct. If a product code is missing, I want a visible missing-match signal. A price borrowed from a different product would be worse, because it could flow quietly into a total or a presentation.

Exact matching is case-insensitive, as confirmed by the worksheet-function documentation. If your source system treats ab and AB as different identifiers, you need a solution that preserves that distinction. The word “exact” describes this function’s matching mode; it does not automatically implement every identity rule used by your source system.

For text lookup values, FALSE also supports wildcard characters: an asterisk and a question mark. Literal occurrences in a real identifier need a preceding tilde, as described in the documentation. Our P-10, P-20 and P-30 codes contain neither character. This is another reason to understand the source identifier format before choosing a matching rule.

When approximate matching is useful

Approximate lookup is useful for threshold tables. Create another synthetic dictionary in H1:I4:

Put 14 in K2 and use:

=VLOOKUP(K2,$H$2:$I$4,2,TRUE)

The result is “Medium.” The largest threshold that does not exceed 14 is 10. For 20, the result is “Large”; for 9, it is “Small.” In this model, the Medium category includes 10 and excludes 20.

Notice that the table contains lower bounds. A business table of upper bounds describes a different rule. Before choosing a formula, write down which category owns a boundary value and whether each endpoint is included.

A lookup value smaller than the lowest threshold produces a missing-match error. A negative quantity in this example should also prompt a question about the data. It might represent a return, a mistake or a document type that this classification was never intended to cover.

Copy the formula without moving the dictionary

The dollar signs in $A$2:$C$4 lock both columns and rows. Copying the formula from F2 to F3 changes E2 to E3 while leaving the price list fixed. That is what you want when the next rows of E contain other orders.

Without dollar signs, A2:C4 would move to A3:C5. The first product would drop out of the search range and an extra cell would enter at the bottom. The first answer could be correct while later rows start failing. I therefore inspect formulas near the middle and bottom of the result column as well as the first one.

For a growing dictionary, an Excel table can make source references easier to maintain. Structured references identify table columns by name. A short cell range keeps this first example easy to follow. The guide to absolute and relative references explains the locking mechanics in more detail.

Duplicate keys require a decision

Add a second P-20 row with price 19 and extend the lookup range to include it. Exact VLOOKUP returns the first match from the top. It does not warn that a code appears twice, and it does not automatically choose the newest price.

I treat that as a question about the source. Do both rows describe the same product? Is one historical? Does the price depend on the branch? The answers determine the correct key. You might need another identifying field, a filter for current records or a corrected dictionary.

Do not remove rows solely because their product code repeats if those rows contain valid variants. You could destroy useful information. Conversely, an order list can legitimately contain many rows for one code. If the question is the total value across those rows, you need aggregation, such as SUMIFS, rather than a single returned price.

Two conditions change the key

After the first successful lookup, somebody may ask for the price in a particular warehouse. If P-20 occurs in two warehouses, the original code no longer identifies one row. Extending the range or changing the return-column number will not resolve that ambiguity. The question now concerns a product-and-warehouse combination.

A helper key can represent that combination, provided the dictionary has one relevant row per combination. If you concatenate fields, consider collisions: AB plus C and A plus BC produce the same text without a separator. A separator also needs to be compatible with the characters allowed in the source fields. For larger processes, I prefer keeping conditions as separate columns and using a join that supports multiple keys.

Historical prices need similar care. Selecting the last row does not establish which price applied on the order date. Later corrections, future prices and multiple contract versions all complicate that rule. Write down the required relationship before selecting a formula.

When the task involves joining large tables, retaining multiple matches and reporting unmatched records, I consider Power Query. VLOOKUP remains useful for a clearly defined dictionary that returns one value for one key.

Understand a missing match before hiding it

P-99 is absent from the original price list, so an exact lookup returns #N/A. Before replacing the error with a message, I check that the code really is missing. Other causes include numbers stored as text, stray spaces and a lookup range that stops too early.

Pay particular attention to identifiers with leading zeros. Text code 0012 can mean something different from numeric value 12. Converting every identifier into a number removes that information. Choose the key’s data type from its definition, rather than from a desire to get rid of an error quickly.

If missing products are an expected case, IFNA can handle the #N/A result specifically. The Microsoft IFNA documentation describes this narrower replacement:

=IFNA(VLOOKUP(E2,$A$2:$C$4,3,FALSE),"Missing from price list")

Other errors remain visible, helping distinguish an unknown code from a broken reference. The message is text, so this result column now mixes numbers and text. For calculations further downstream, I often prefer a separate status column and a separate price column. A missing price should remain visible to the report’s reader.

Zero is not a universal missing-value marker

A legitimate price can be zero, for example for an item supplied without an additional charge. Replacing every missing match with zero would make an unknown product indistinguishable from a free one.

A blank price needs attention too. Finding a product code and finding a usable price are separate conditions. The dictionary should define whether an absent price is permitted, who maintains it and what the report should display. The lookup does not validate the completeness of the row it finds. Before calculating order values, I therefore review missing prices even when every product code has a match.

Layout changes and another worksheet

The return-column number is written into the formula. If you insert a column before the price, check whether the third argument still points at the intended field. A formula can start returning product descriptions or categories without producing an error. This is easy to overlook after an apparently cosmetic worksheet change.

The dictionary can live on another worksheet. For a sheet called “Product prices,” the formula becomes:

=VLOOKUP(E2,'Product prices'!$A$2:$C$4,3,FALSE)

The apostrophes enclose a worksheet name containing a space. Selecting the source range while building the formula lets Excel enter the sheet name for you. Keeping the dictionary on a separate sheet can improve organization, but says nothing about whether its prices are current.

A link to another workbook introduces dependencies on that file, its location and its availability. When sharing a report, check that the recipient also has access to the source. A saved result visible on your computer does not prove that somebody else can refresh the workbook successfully.

Review the report through known cases

I choose several known codes and identify their expected dictionary rows manually. I want a product at the beginning, another at the end and one in the middle. I also add an absent code, because correct handling of missing data is part of the report’s behavior. One successful example tells me little about an entire column.

For threshold lookup, I inspect boundary values and their neighbors. In our synthetic example, I would use 9, 10, 19 and 20. The expected classes are Small, Medium, Medium and Large. These cases expose an incorrect assumption about which category includes a boundary.

Finally, I record where the dictionary came from and when its prices applied. A formula can be technically correct while using an outdated price list. Ownership of the dictionary and its effective date matter alongside the choice of FALSE.

Hand over a workbook somebody else can understand

Before sharing a file, I check its headings. “Price from current dictionary” says more than “Result.” If the report also contains the price actually paid, the difference in meaning should be visible even when the numbers happen to agree.

The recipient should know where to add orders and where to maintain the source dictionary. Overwriting one formula with a manually entered price is quick, but that cell may never update again. Inspect a few cells further down the result column to confirm they still contain formulas.

If I freeze the results as values for a one-off report, I label the export date. The report will then stop responding to dictionary changes. That can be appropriate, provided the reader understands which moment it represents. A saved snapshot and a refreshable workbook need different handover notes.

As a final exercise, add a “Price source” column and a short explanation of missing codes. Describe the workbook so that somebody can use it without asking you to explain each formula. A clear key, range, return field, matching mode and missing-data rule make the lookup an understandable part of the reporting process.

Exercise: names, prices and an absent code

Return to the original three products. Enter P-10, P-30, P-99 and P-20 in E2:E5. Create one column returning names and another returning prices. Both formulas should use the same fixed dictionary range and exact matching.

For the first row, expect Notebook and 12. For the second, expect Folder and 7. The third should identify a missing match. The fourth should return Binder and 18. Then add a product at the bottom of the dictionary and inspect whether the formulas include it.

If you can explain each result, you have a useful foundation. The next step is to compare the same task with XLOOKUP and choose according to the recipient’s Excel version and the structure of the source data.