Excel Challenge 16 : Find the Lowest Price

What You Will Learn

Five suppliers are quoting the same office paper product. You need to know three things: what is the cheapest price, which supplier offered it, and which row in the table is the winner.

Three formulas work together here: MIN finds the lowest price, XLOOKUP traces that price back to the supplier name, and IF marks the winning row inside the table so the best deal is visible at a glance.

Here is how the data is arranged:

A2:A6 — supplier names
C2:C6 — quoted prices
B9 — your MIN formula (cheapest price)
B10 — your XLOOKUP formula (winner supplier)
D2:D6 — your IF formula (row flag)

The Three Formulas

1. Cheapest Price — MIN

MIN scans the price range and returns the smallest number. Instead of scanning the column by eye looking for the lowest bid, MIN gives you the answer immediately. That number becomes your anchor — both the supplier lookup and the row flag depend on it:

=MIN(C2:C6)

2. Winning Supplier — XLOOKUP

Knowing the cheapest price is useful, but procurement also needs to know who offered it. XLOOKUP searches the price column for the value in B9 and returns the matching supplier name from column A:

=XLOOKUP(B9, C2:C6, A2:A6)

XLOOKUP is used here instead of VLOOKUP because it lets you point at the return column directly — you do not have to count column positions. It also supports both vertical and horizontal lookups, which makes it the more flexible choice for modern worksheets. If you only have access to older Excel, INDEX(MATCH(...)) can achieve the same result.

3. Row Flag — IF

The flag column brings the answer back into the table. It checks whether each row's price matches the lowest value in B9. If it does, the cell shows "LOWEST". If not, it stays empty — so the winning row is obvious without reading the summary first:

=IF(C2=$B$9,"LOWEST","")

The dollar signs on $B$9 lock the reference so every row compares against the same minimum cell. Without them, the comparison would shift downward as you fill and miss the correct value.

MIN finds the price, XLOOKUP returns the supplier, IF flags the row.

Going Further: Category Minimums with MINIFS

If you later need the cheapest price within a specific category instead of across the whole list, MINIFS adds a filter. For example, the cheapest paper quote across multiple categories:

=MINIFS(C2:C50, B2:B50, "Paper")

This is useful when your quote sheet grows to include different product types and you need per-category minimums.

MINIFS finds category-specific minimums for larger quote sheets.

If two suppliers quote the same lowest price, XLOOKUP returns the first one it finds. For sheets where ties matter, you may want to add a tie-breaking rule before relying on the result.