How to Return Each Player's Highest Score and Not Out Value in Excel
Question details
The user needs to extract the maximum score for each individual player from a dataset and retrieve the corresponding "Not Out" status for that specific innings.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing sports records (like cricket) with multiple entries per player to find and display peak performance statistics alongside contextual match data.
- Observed behavior
- A consolidated list of unique players alongside their highest score and the specific "Not Out" value from that match, automatically generated using array functions.
Ensure your spreadsheet software supports dynamic array formulas such as LET, MAP, and HSTACK. Verify that your dataset has no blank rows in the player column to avoid returning empty or erroneous results.
Use a Combined Dynamic Array Formula
This all-in-one dynamic formula extracts unique players, calculates their highest score, and fetches the corresponding "Not Out" value automatically.
By nesting functions inside the LET function, you can define variables for unique players, their maximum scores, and the lookup results. This keeps the formula clean and efficient without needing helper columns.
Verify that your player names are in A2:A42, scores in B2:B42, and Not Out values in C2:C42.
Click on the top-left cell where you want the resulting summary table to appear (for example, cell E2).
Type the following formula: =LET(players,UNIQUE(A2:A42),maxScores,MAP(players,LAMBDA(p,MAXIFS(B2:B42,A2:A42,p))),notOut,MAP(players,maxScores,LAMBDA(p,s,XLOOKUP(1,(A2:A42=p)*(B2:B42=s),C2:C42,""))),HSTACK(players,maxScores,notOut))
Press Enter. The formula will automatically spill down and across to create a three-column table showing the player, highest score, and Not Out value.
Analyze Complex Sports Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions and array formulas, allowing you to easily track player statistics, find maximum scores, and extract conditional data without relying on complex VBA code or outdated methods.
- 1. Open your dataset: Launch WPS Spreadsheet and open your cricket or sports dataset workbook.
- 2. Select the output area: Click on an empty cell where you want the dynamic summary table to begin.
- 3. Apply the array formula: Paste the provided dynamic array formula into the formula bar and press Enter to instantly generate the player statistics.
- 4. Format the results: Use WPS Spreadsheet's built-in table formatting and alignment tools to style your new results table for better readability.
Frequently Asked Questions
What if my version of Excel doesn't support the LET or MAP functions?
If you are using an older version of Excel, dynamic arrays will not work. You will need to use helper methods: first extract unique players using Data > Remove Duplicates, use the MAXIFS function to find the highest score, and use an INDEX/MATCH combination entered as an array formula (Ctrl+Shift+Enter) to get the Not Out value.
How does the XLOOKUP part of the formula work with multiple criteria?
The XLOOKUP function is set to search for the value 1 within an array of boolean values generated by the expression (A2:A42=p)*(B2:B42=s). This effectively creates an exact match requirement for multiple conditions (matching both the player name and their maximum score) to retrieve the corresponding Not Out value.
Why am I getting a #SPILL! error when I press Enter?
A #SPILL! error occurs when the dynamic array formula doesn't have enough empty adjacent cells to display the generated table. To fix this, clear any text, spaces, or data in the columns and rows immediately below and to the right of your formula cell.
