Return Only the Matching Item Name with Excel INDEX and MATCH
Question details
The user needs to retrieve the exact item name corresponding to the highest sales value using a formula, without including the appended score, amount, or extra text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up the top-performing item in a POS report and returning just its name string.
- Observed behavior
- Existing formulas may concatenate or return unwanted extra text alongside the item name; the goal is to isolate and return only the matching item name.
Ensure your item names and sales values are organized into separate, clearly defined columns. Double-check that the data ranges used in your formula are exactly the same size (e.g., rows 20 to 40) to prevent #N/A errors.
Use INDEX, MATCH, and MAX Together
Combine the INDEX, MATCH, and MAX functions to pinpoint the highest sales value and return precisely the corresponding text from the name column.
The MAX function finds the highest value in the sales column. The MATCH function locates the row number of that highest value. Finally, the INDEX function uses that row number to extract the exact text from the item name column.
Locate the column containing your item names (for example, B20:B40) and the column containing your sales figures (for example, W20:W40) on your worksheet.
Click on the specific cell where you want the highest-selling item name to be displayed.
Type the formula: =INDEX('AGGG .com POS report MS'!B20:B40, MATCH(MAX('AGGG .com POS report MS'!W20:W40), 'AGGG .com POS report MS'!W20:W40, FALSE)) and press Enter.
The cell will now display only the item name from column B that corresponds to the maximum value in column W, stripping away any extra aggregated data.
Easily Handle Complex Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports Excel's advanced lookup functions like INDEX, MATCH, and MAX. You can seamlessly analyze large POS reports and extract exact data matches without learning new syntaxes, all within a lightweight and completely free application.
- 1. Open your report in WPS Spreadsheet: Launch WPS Office and open your .xlsx POS report file.
- 2. Select the result cell: Click the cell where you want to output the top item name.
- 3. Apply the lookup formula: Type your =INDEX(..., MATCH(MAX(...))) formula and press Enter. WPS will instantly process the lookup.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
An #N/A error usually occurs if the MATCH function cannot find the lookup value, or if the array sizes do not match. Ensure your INDEX range (e.g., B20:B40) and MATCH range (e.g., W20:W40) cover the exact same number of rows.
Can I use XLOOKUP instead of INDEX and MATCH for this task?
Yes. In modern versions of Excel and WPS Office, you can achieve the same result with a simpler syntax: =XLOOKUP(MAX(W20:W40), W20:W40, B20:B40). This directly returns the name matching the maximum sales value.
What happens if two items tie for the highest sales value?
When using the standard MATCH function, Excel searches from top to bottom and will return the row position of the first maximum value it encounters. The subsequent ties will be ignored.
