A merged supplier price list is one table where the products of all suppliers are matched by a shared key (a barcode or the manufacturer part number), prices are converted to one currency, stock to one set of statuses, and each product gets one offer. The rule for picking it is simple: first in stock, among those the cheapest, and at equal price — your main supplier.
The most expensive mistake is to match by name and take the lowest price. By name, one product turns into three listings, and the cheapest offer is often "on order" or already gone — so the customer gets a cancellation. Below: five steps, an example with three price lists from start to finish, formulas for Excel and Google Sheets, and where a merged list breaks.
When you need a merged price list
A store works with two or three suppliers, and their ranges overlap: the same kettle is everywhere, but at a different price, in a different currency and with different stock. Each supplier sends a price list with their own columns. The owner searches for "merge supplier price lists" or "supplier price comparison template" because checking three files by hand every day no longer fits in the day.
The first question is "how do I find the lowest price". The more important one: how to tell that three price lists describe the same product, and what to do when the cheapest supplier does not have it.
How to merge price lists: 5 steps
Merging always takes five steps, and none can be skipped: an error at step 2 or 3 quietly spoils the price at step 4.

- Collect fresh price lists from all suppliers and note the date of each.
- Align the columns to one layout: key, name, price in your currency, stock.
- Match rows from different price lists that describe the same product.
- Pick the price by a rule: stock → price → supplier priority.
- Update the merged list when a new price list arrives, and remove what is gone.
How to bring price lists to the same columns
Move each price list into one long table with identical columns and add a "Supplier" column. Differences in column names, currency and stock labels are removed right here.

| Columns in the price list | Currency | Stock | |
|---|---|---|---|
| Supplier A | Item no. · Description · Price · Qty | € | number of units |
| Supplier B | Code · Product · Price · Stock | $ | "+", "backorder" |
| Supplier C | SKU · Title · Trade price · Avail. | £ | "yes", "0", "incoming" |
| Master price list | Supplier · Key · Name · Price € · Stock | € | in stock / on order / out of stock |
- Price — convert to your currency at one rate on the date of the merge. Check that all prices either include VAT or exclude it, and that each is per unit, not per pack.
- Stock — reduce all labels to three statuses: a number above zero, "+", "yes" → "in stock"; "backorder", "incoming" → "on order"; "0", blank → "out of stock".
How to tell that different price lists have the same product
By a key that does not depend on the supplier: the EAN/UPC barcode, or the manufacturer part number (MPN) together with the brand. A name cannot be the key — every supplier writes it their own way.

The same kettle in three price lists: "Kettle 1.7 L steel", "Electric kettle 1.7L" and "Kettle KX200". The names differ, the manufacturer part number is the same, but written once as "KX-200" and once as "KX200". So the key is normalized: hyphens and spaces are removed and letters are made uppercase. Formula for Excel and Google Sheets:
=UPPER(SUBSTITUTE(SUBSTITUTE(B2,"-","")," ",""))
Order of matching: first the barcode, if both price lists have it; then the part number together with the brand — the same part number exists at different brands; rows without a barcode or part number are matched by hand, and the pair you found is written down so you do not search for it next time. More cases — matching products with different supplier names.
Example: three price lists, three products
Illustrative example for the arithmetic, prices and rates are not market figures. €1 = $1.25 and €1 = £0.80. The main supplier is A.
| Key | Supplier A | Supplier B | Supplier C | Picked |
|---|---|---|---|---|
| KX-200, kettle | €25.00, in stock | $30 ÷ 1.25 = €24.00, on order | £19 ÷ 0.80 = €23.75, out of stock | A, €25.00 |
| LM-15, lamp | €9.60, in stock | $11.25 ÷ 1.25 = €9.00, in stock | £7.80 ÷ 0.80 = €9.75, in stock | B, €9.00 |
| PR-7, iron | not in the price list | $22.50 ÷ 1.25 = €18.00, in stock | £13.60 ÷ 0.80 = €17.00, on order | B, €18.00 |

- KX-200: C is the cheapest (€23.75) but out of stock; B (€24.00) is on order. Only A has it in stock — A is picked, although it costs €1.25 more.
- LM-15: all three have it in stock, B is the cheapest at €9.00.
- PR-7: only B has it in stock at €18.00; the cheaper C is on order.
If the rule were simply the lowest price, two of three products would go to the storefront at the price of a supplier who will not ship them. From the picked cost, calculate the selling price with markup, shipping and fees — the markup and margin calculator does it for one product.
Formulas for Excel and Google Sheets
Keep all price lists on one tab as a long table: A — supplier, B — key, C — price in euros, D — stock. Put your main supplier's rows first: at equal price the formula takes them.
| Column | What it calculates | Formula in row 2 |
|---|---|---|
| F — Key | list of unique products | =UNIQUE(B2:B5000) (in F2 only) |
| G — Price | minimum among "in stock"; if there are none — among "on order" | =IF(MINIFS(C$2:C$5000,B$2:B$5000,F2,D$2:D$5000,"in stock")>0,MINIFS(C$2:C$5000,B$2:B$5000,F2,D$2:D$5000,"in stock"),MINIFS(C$2:C$5000,B$2:B$5000,F2,D$2:D$5000,"on order")) |
| H — Stock | status of the picked offer | =IF(MINIFS(C$2:C$5000,B$2:B$5000,F2,D$2:D$5000,"in stock")>0,"in stock",IF(G2>0,"on order","out of stock")) |
| I — Supplier | who gave the picked price | =XLOOKUP(F2&"|"&G2&"|"&H2,B$2:B$5000&"|"&C$2:C$5000&"|"&D$2:D$5000,A$2:A$5000,"") |
MINIFS returns 0 when no row matches — that is why the formula checks "in stock" first, and a zero in column G means no supplier has the product. UNIQUE and XLOOKUP work in Google Sheets and in Excel 2021 and Microsoft 365; MINIFS — from Excel 2019. In Google Sheets, wrap the formula in column I in ARRAYFORMULA(…); with a European locale, replace commas with semicolons.
Where a merged price list breaks
Most often not in the formulas but in the data that goes into them.
- Pack instead of unit. One supplier quotes per unit, another per pack of 10. Without a "units per pack" column, the wrong one wins.
- VAT. Prices with and without VAT in one comparison pick the wrong winner.
- Stale price list. A week-old price list wins on price because the supplier has already raised it. Record the date of each price list and leave out those older than your threshold.
- A product disappears from the price list. If the row is missing in the new file, the offer must become "out of stock", not keep the old price.
- Exchange rate. Convert foreign-currency prices daily, at one rate for all suppliers.
- The supplier moved columns. The price lands in the stock column — check headers at every update.
If price lists live in Google Sheets, how to pull them in with formulas and where that approach ends — in Google Sheets price list.
When to hand the merge to software
When you have more than two or three suppliers, price lists arrive daily and products number in the thousands: merging by hand stops keeping up with the price lists, and old prices reach the storefront.
In Elbuz you do not need a separate merged table. Each price list loads on schedule, columns are recognized by their headers, and rows are matched to your catalog in a queue: first pairs you have already confirmed, then an exact match of the manufacturer part number (5 characters or more), then an exact match of the name, and the rest goes to AI matching or to you. When a product is in several price lists, the app takes the minimum, average or maximum price by your rule, and your main supplier and in-stock products can be preferred over a cheaper offer that is only on order. More on the supplier price list automation page.
Who it is not for: with two suppliers, a few dozen shared products and weekly price lists, the formulas in this article are enough.
Frequently asked questions
What is a merged supplier price list?
One table with the products of all suppliers, where identical products are matched by a key, prices are in one currency, and each product has one offer picked by a rule.
How do I combine several supplier price lists into one?
Bring all price lists to the same columns, match products by barcode or manufacturer part number, and pick each product's price by the rule "stock → price → supplier priority". The order is in 5 steps.
How do I match the same products with different names?
By barcode, or by manufacturer part number together with the brand, after removing hyphens and spaces from the part number. The name does not work for matching.
Which price do I take if several suppliers have the product?
The lowest among those who have it in stock. If no one has it in stock — the lowest among "on order"; at equal price — your main supplier's.
How do I find the minimum price with a condition in Excel?
With MINIFS: the price range, the key range with the key you need, and the stock range with the condition "in stock". It is available in Excel 2019 and later and in Google Sheets.
How do I use XLOOKUP with multiple criteria?
Join the criteria with & in both the lookup value and the lookup array, as in column I above: key, price and stock become one string, and XLOOKUP returns the matching supplier.
How often should I update a merged price list?
Every time a new price list arrives from at least one supplier. If price lists arrive daily, the merge must be updated daily too, or old prices and stock stay on the storefront.
Sources
- Microsoft Support: MINIFS function, XLOOKUP function, UNIQUE function.
- Google Docs Editors Help: MINIFS, XLOOKUP, UNIQUE.
Checked on October 31, 2026.
