VLOOKUP with an exact match

How it works, step by step

1. 01B2 holds the SKU for this order, for example SKU-1042.
2. 02VLOOKUP searches the first column of Products!$A$2:$C$500, the SKU column, for that exact value.
3. 03FALSE as the last argument forces an exact match. Without it, VLOOKUP assumes the column is sorted and can return the wrong row.
4. 04The 3 returns the value from the third column of the range, column C, which is Unit price.
5. 05Fill the formula down column C of Orders. The $ signs keep the Products range fixed as the formula copies.

The same logic in four tools

=VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE)

=VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE)

SELECT o.order_id, o.sku, p.unit_price
FROM orders o
LEFT JOIN products p ON p.sku = o.sku;

import pandas as pd

result = orders.merge(
    products[["sku", "unit_price"]],
    on="sku",
    how="left",
)

Mistakes that break it

• !VLOOKUP returns the first match only. If a SKU appears twice in Products, the second row is ignored without any warning.
• !A SKU stored as text on one sheet and as a number on the other will not match and returns #N/A. Check with =ISTEXT(B2) and convert one side so both use the same type.
• !The column number 3 is hard-coded. Inserting a column inside Products!A:C shifts the data, and the formula keeps returning column 3, which is now a different field.
• !In SQL and pandas, duplicate SKUs in products multiply the order rows instead of taking the first match. Deduplicate first with products.drop_duplicates("sku") or a DISTINCT ON (sku) subquery.

Common questions

Why does VLOOKUP return #N/A?

The lookup value was not found in the first column of the range. The usual causes are extra spaces, a number stored as text on one side, or the value missing from the lookup table.

What does FALSE mean in VLOOKUP?

FALSE (or 0) sets the last argument, range_lookup, to exact match. TRUE or a missing argument means approximate match, which needs the first column sorted ascending.

What is the SQL equivalent of VLOOKUP?

A LEFT JOIN from the main table to the lookup table on the shared key. Every order row is kept, and orders with no matching product get NULL, the same way VLOOKUP gives #N/A.