Return the Item for the Largest Sales Value in Excel
Question details
Find the largest sales quantity in column S and return the corresponding item name from column Q.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sales quantities are in S76:S146, with corresponding item names in Q76:Q146.
- Observed behavior
- The desired result is the item name from the same row as the largest quantity.
Check that S76:S146 contains numeric quantities and that Q76:Q146 contains the corresponding item names on the same rows. Adjust both ranges if your data covers different rows.
Use XLOOKUP to return the item with the largest quantity
Use this formula if your version of Excel supports XLOOKUP.
Click an empty cell where you want the item name to appear.
Type =XLOOKUP(MAX(S76:S146),S76:S146,Q76:Q146) and press Enter.
Compare the returned item name with the row containing the largest quantity in S76:S146.

Use INDEX and MATCH in older Excel versions
Use this alternative if XLOOKUP is unavailable in your version of Excel.
Find the Item with the Largest Quantity in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use INDEX, MATCH, and MAX to return the item name corresponding to the largest sales quantity.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Select a result cell: Click an empty cell where you want the item name to appear.
- 3. Enter the formula: Type =INDEX(Q76:Q146,MATCH(MAX(S76:S146),S76:S146,0)) and press Enter.
- 4. Save the workbook: Select File > Save to keep the formula in your workbook.

Frequently Asked Questions
What happens if two items have the same highest quantity?
Both formulas return the item from the first matching row in the specified ranges.
Can I use the formula if item names are to the left of the quantities?
Yes. XLOOKUP and INDEX with MATCH can return names from column Q while searching quantities in column S, provided the ranges cover the same rows.
What if Excel does not recognize XLOOKUP?
Use =INDEX(Q76:Q146,MATCH(MAX(S76:S146),S76:S146,0)) in an empty cell instead.
How do I adapt the formula to a different range?
Replace S76:S146 with your quantity range and Q76:Q146 with your item-name range. Make sure both ranges start and end on corresponding rows.




