How to return the supplier with the lowest price for an item
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10)Find the item's lowest price with MINIFS and look up the row with that item and price: =XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10).
- Result on the example
- Corvex in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
MINIFS(C2:C10,A2:A10,G1) is the best toner price, $86.75. The multiplied tests are 1 only where the item is toner and the price equals that minimum, and XLOOKUP returns the supplier on that row, Corvex. Testing the item as well stops a cheaper price for a different item from matching.
=XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10)Result"Corvex"
A2:A109 rows × 1 col: "Copy paper (case)", "Toner cartridge", "Copy paper (case)", "Toner cartridge", …A range: 9 cells in one column.
G1"Toner cartridge"The value in G1.
A2:A10=G1{9 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
C2:C109 rows × 1 col: $42.50, $89.00, $39.90, $94.50, …A range: 9 cells in one column.
MINIFS(C2:C10,A2:A10,G1)86.75MINIFS: Smallest value that meets every condition.- min_range
C2:C109 rows × 1 col: $42.50, $89.00, $39.90, $94.50, … - criteria_range1
A2:A109 rows × 1 col: "Copy paper (case)", "Toner cartridge", "Copy paper (case)", "Toner cartridge", … - criteria1
G1"Toner cartridge"
- min_range
C2:C10=MINIFS(C2:C10,A2:A10,G1){9 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1){9 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
B2:B109 rows × 1 col: "Acme Supply", "Acme Supply", "BrightCo", "BrightCo", …A range: 9 cells in one column.
XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10)"Corvex"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1){9 rows × 1 col} 0, 0, 0, 0, … - return_array
B2:B109 rows × 1 col: "Acme Supply", "Acme Supply", "BrightCo", "BrightCo", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10). We ran it in Google Sheets mode on the same data and it returns Corvex.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(SORT(FILTER(B2:C10,A2:A10=G1),2,1),1,1) | Corvex | Filter the item's suppliers, sort by price from low to high and take the first. Excel 2021, Microsoft 365 and Google Sheets. |
Common mistakes
- Looking up the minimum price alone,
XLOOKUP(MINIFS(…),C2:C10,B2:B10), can return a supplier of a different item at the same price. - Two suppliers tied on price return the first one listed.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return the supplier with the lowest price for an item
G2, return the supplier offering the lowest unit price for the item in G1.Questions
How do I return the price too?
Next to it, use =MINIFS(C2:C10,A2:A10,G1).
How do I list all suppliers sorted by price?
Use =SORT(FILTER(B2:C10,A2:A10=G1),2,1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.