How to compare supplier quotes in Excel
Two or more suppliers, quoted at the same time, for the same list of items — and the job is to find which supplier is cheapest for each one. This is a comparison ACROSS suppliers, not a before-and-after comparison of one supplier's own price list over time. CostRift runs this same comparison in one step, if you'd rather skip the lookups.
What you need before you start
- A quote from each supplier for the same set of items — two or more — as CSV, XLSX, or whatever format each supplier sent.
- An item identifier column — a SKU or supplier item code — in every quote.
- A unit cost column in every quote. Quantities are optional, needed only if you also want a total basket cost.
The method in Excel, step by step
Build one master list with a column per supplier
Start one sheet with a single column of item identifiers — every SKU across all the quotes, with no duplicates. Add one column per supplier to its right: Supplier A, Supplier B, and so on. This is the opposite layout from a before/after comparison, where each file gets its own sheet — here, every supplier needs to sit side by side in the same row for the same item.
Pull each supplier's price into its column
For each supplier column, look up the item identifier in that supplier's own quote sheet and bring back the price, exact match only:
=XLOOKUP([@SKU], SupplierA[SKU], SupplierA[Price], "")
Use an empty string, not zero, as the not-found default — zero would read as the cheapest price in the next step and win by mistake. Repeat the lookup for each supplier column, pointing at that supplier's own sheet.
Find the lowest price per item
With every supplier's price in its own column on the same row, the lowest price for that item is a MIN across the row — but blanks need to be ignored rather than treated as zero:
=MIN(IF(B2:D2<>"", B2:D2))
Enter it as an array formula (Ctrl+Shift+Enter on older Excel; current Excel does this automatically), or use MINIFS with a not-blank condition if your version supports it.
Identify which supplier won
Match the lowest price back to the supplier header row with INDEX and MATCH:
=INDEX($B$1:$D$1, MATCH(MIN(B2:D2), B2:D2, 0))
If two suppliers quote the exact same lowest price, MATCH returns only the first one it finds — decide up front whether you want to flag exact ties separately rather than silently pick whichever supplier's column comes first.
Handle items only one supplier quoted
An item with only one non-blank price has nothing to compare it against. Flag these separately — a single offer is not automatically the best price, it's the only data point you have.
Flag currency or unit mismatches before comparing
If suppliers quote the same item in different currencies or units of measure, the raw numbers aren't comparable. Convert explicitly with a rate you control, or exclude the item from the automated MIN/INDEX comparison and resolve it by hand — never let a lower number in a different currency win by default.
Total the basket, single-supplier versus mixed
With a quantity column mapped, sum quantity × each supplier's own price for a single-supplier total, and sum quantity × the row's minimum price for the mixed-supplier total. The gap between the two is what sourcing each item at its cheapest quote could save over buying the whole list from one supplier.
Where this gets harder to maintain by hand
Two or three suppliers and a stable item list is manageable with formulas. It gets heavier with every supplier added — one more column, one more lookup to repeat correctly — and heavier again when supplier item codes don't match cleanly, when quotes arrive in different layouts each round, or when the comparison needs to run every time a new round of quotes comes in rather than once.
Compare the quotes in one step instead
CostRift's Supplier Comparison runs this same comparison without the per-supplier lookups. Add a quote from each supplier — CSV or XLSX, mapped independently so column names can differ — and it matches items across suppliers by identifier, marks the cheapest usable quote for each one, and flags items only one supplier quoted, currency or unit mismatches, and ambiguous duplicates for review rather than guessing. With quantities mapped, it also shows the mixed-supplier basket total against buying everything from one supplier. Files are read and compared in your browser and are not uploaded to run the comparison.
A worked example
Three suppliers, four items. Numbers are illustrative and in any currency.
| Item | Supplier A | Supplier B | Supplier C | Winner |
|---|---|---|---|---|
| SKU-100 | 10.00 | 9.20 | 9.80 | Supplier B |
| SKU-200 | 4.50 | 4.50 | 4.60 | Tied (A, B) |
| SKU-300 | — | 18.00 | 17.50 | Supplier C |
| SKU-400 | 6.00 | — | — | Supplier A (only offer) |
SKU-200 is quoted identically by A and B, so it's a tie rather than an arbitrary pick. SKU-300 was never quoted by Supplier A, so only B and C are compared. SKU-400 was quoted only by Supplier A — a single offer, not a proven best price.
Frequently asked questions
How do I compare quotes from multiple suppliers in Excel?
Put every supplier's quote in its own column against one master list of item identifiers — one row per item, one column per supplier. Use MIN to find the lowest price per row, and INDEX/MATCH to find which supplier column that price came from.
What formula finds the cheapest supplier per item?
Once each supplier's price sits in its own column next to the item, MIN across that row gives the lowest price, and INDEX combined with MATCH(MIN(range), range, 0) against the header row of supplier names returns which supplier it belongs to.
What if a supplier didn't quote an item at all?
Leave that cell blank rather than entering a zero or a guess — a zero would look like the cheapest price and win every time. MIN and MATCH both need the blank handled explicitly (for example with an IF that skips blank cells) so a missing quote never wins by accident.
How is this different from comparing an old and a new price list?
This is a different axis entirely. Comparing multiple suppliers finds the cheapest option right now, across several quotes for the same purchase. Comparing an old and new list from one supplier finds what changed for that same supplier over time. The spreadsheet mechanics and the business question are both different.
How do I handle suppliers quoting in different currencies or units?
Don't compare the raw numbers directly. Flag any item where suppliers quote in different currencies or units of measure, and either convert explicitly with a rate you control, or leave the item out of the automated comparison and review it by hand.
Can I see the total cost of buying everything from one supplier versus the cheapest per item?
Yes, if you also map a quantity per item. Sum quantity × that supplier's price for a single-supplier total, and sum quantity × the row's minimum price for the mixed-supplier total — the difference is the potential saving from sourcing each item at its cheapest quote.
Where to go next
For the same comparison across more suppliers without the lookups, see CostRift's comparing supplier quotes. For a purchasing-team framing of the same underlying comparison, see procurement price comparison. If the question is instead what changed in one supplier's own price list over time, that's a different comparison — see comparing two supplier price lists in Excel.
Compare your supplier quotes
Add a quote from each supplier and see the cheapest price per item in one comparison.
Compare supplier quotes