Find the Highest Bid and Winning Customer for Each SKU in Excel
Question details
Find the maximum bid for each SKU and return the customer who submitted that bid, with a clear method for handling tied maximum bids.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A bid table contains SKU, customer, and bid amount columns, and the user needs a winning customer result for every SKU.
- Observed behavior
- Multiple customers can submit the same highest bid for one SKU, so the result must either return the first match or list every tied customer.
Make sure bid amounts are stored as numeric values and that each row has an SKU, customer name, and bid amount. For the formula examples, assume SKU is in column A, Customer is in column B, and Bid is in column C.
Use MAXIFS and FILTER to return every customer tied for the highest bid
Use this method in Excel 365 when you want a formula-based result that can list all customers sharing the maximum bid for a selected SKU.
MAXIFS calculates the largest bid for an SKU. FILTER then returns each customer whose bid equals that SKU's maximum value, so ties are not lost.
In a results area, enter the SKU you want to check in E2, such as AW1-123.
Select F2 and enter =MAXIFS($C$2:$C$100,$A$2:$A$100,E2). This returns the largest bid in column C for the SKU in E2.
Select G2 and enter =FILTER($B$2:$B$100,($A$2:$A$100=E2)*($C$2:$C$100=F2),"No matching bid"). The formula spills every customer whose bid matches the maximum.
To show a single customer instead, enter =XLOOKUP(1,($A$2:$A$100=E2)*($C$2:$C$100=F2),$B$2:$B$100,"No match") in G2.
Use Power Query to group bids by SKU and choose the winning customer
Use Power Query when the bid list is large, refreshed regularly, or needs a repeatable transformation rather than worksheet formulas.
Identify the Highest Bid and Winning Customer in WPS Spreadsheet
WPS Spreadsheet can calculate the highest bid for each SKU with MAXIFS and return the matching customer with FILTER or XLOOKUP. Use FILTER when tied bids should display every winning customer, or use XLOOKUP when one first-listed winner is required.
- 1. Open the bid worksheet: Open the .xlsx file in WPS Spreadsheet and confirm that the SKU, Customer, and Bid columns are correctly aligned.
- 2. Set up a results area: Enter an SKU in a blank result cell, such as E2, and reserve adjacent cells for the maximum bid and customer result.
- 3. Calculate the maximum bid: In the highest-bid cell, enter =MAXIFS($C$2:$C$100,$A$2:$A$100,E2).
- 4. List tied winning customers: In the customer-result cell, enter =FILTER($B$2:$B$100,($A$2:$A$100=E2)*($C$2:$C$100=F2),"No matching bid").
- 5. Choose a single winner when required: Replace the FILTER formula with =XLOOKUP(1,($A$2:$A$100=E2)*($C$2:$C$100=F2),$B$2:$B$100,"No match") to return the first matching customer.
Frequently Asked Questions
How do I return the highest bid for each SKU in Excel?
Use MAXIFS with the bid range as the maximum range and the SKU range as the criteria range. For example, =MAXIFS($C$2:$C$100,$A$2:$A$100,E2) returns the highest bid for the SKU entered in E2.
How can I show every customer tied for the highest bid?
Use FILTER with both the SKU and maximum-bid conditions: =FILTER($B$2:$B$100,($A$2:$A$100=E2)*($C$2:$C$100=F2)). This spills all customer names that meet both conditions.
Why does XLOOKUP return only one customer when two bids are equal?
XLOOKUP returns the first matching row by default. If tied customers must all be shown, use FILTER instead, or sort the source data first if the first returned customer must follow a specific priority.
Can Power Query return all customers with the maximum bid for an SKU?
Yes. After grouping rows by SKU, calculate the maximum Bid within each group and filter the nested rows to Bid values equal to that maximum. Expand the filtered rows or combine the customer names to display all tied winners.
