How to Return the Name with the Highest Value for a Selected Category
Question details
The user needs to extract the name associated with the maximum value in a specific subset or category, without accidentally returning a maximum value from a different category.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering data sets to find the top performer, character, or item within a dynamically selected category without altering the source table.
- Observed behavior
- Standard VLOOKUP or MAX formulas might identify the highest value overall, requiring combined functions to apply the category filter before retrieving the corresponding name.
Ensure your data is formatted as a structured Table or that your reference ranges are locked (absolute references), and clearly identify your columns for Names, Values, and Categories.
Use INDEX, MATCH, and MAXIFS (For Single Results)
This solution is perfect for most spreadsheet users. It combines MAXIFS to identify the highest value in a specific category with INDEX and MATCH to locate the exact row and retrieve the name.
This formula creates a logical array. It ensures two conditions are met: the value must equal the maximum value of the targeted category, and the category must match the user's selection.
Format your dataset as a Table named 'CharacterProfs'. Ensure you have columns for 'Character' (Names), 'Vanilla' (Values), and 'Profession' (Categories).
Select a cell, for example, F10, where you will type the specific category (e.g., a specific Profession) you want to analyze.
In your result cell, enter the formula: =INDEX(CharacterProfs[Character], MATCH(1, (MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10) = CharacterProfs[Vanilla]) * (CharacterProfs[Profession] = F10), 0))
Press Enter to calculate. The cell will now display the name of the character with the highest value in the profession specified in cell F10.
Use the FILTER Function (For Microsoft 365 / Newer Versions)
If there is a chance that multiple names are tied for the highest value in a category, the FILTER function is the best approach to extract all tied names.
Analyze Complex Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced data analysis functions including MAXIFS, INDEX, MATCH, and dynamic arrays, making it incredibly easy to retrieve specific category data.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing data file.
- 2. Apply the lookup formula: Select an empty cell and paste the provided INDEX/MATCH/MAXIFS formula.
- 3. Update category dynamically: Change the reference cell text to instantly see the top performer for any new category.
Frequently Asked Questions
Why is my formula returning a #N/A error?
A #N/A error usually indicates that the MATCH function could not find the exact condition. Ensure that the category typed in your reference cell matches the spelling in your category column exactly, and that your highest value exists.
What if my version of the software doesn't have the MAXIFS function?
If MAXIFS is unavailable, you can substitute it with an array formula using MAX and IF. For example: MAX(IF(CategoryRange=SelectedCategory, ValueRange)). You must press Ctrl+Shift+Enter for this to work in older versions.
How does multiplying conditions with an asterisk (*) work in this formula?
In spreadsheet formulas, the asterisk acts as an 'AND' operator for arrays. It multiplies the True (1) or False (0) results of each condition. Only rows where both conditions are True result in a 1, which the MATCH function then locates.
