Formula Errors

How to Rank Salespeople by Monthly Sales with Excel INDEX and MATCH

Tauseeq Magsi Oct 12, 2026 868 views

Question details

The user needs to construct a reliable formula to rank salespeople based on their monthly sales, which functions correctly when dragged across multiple columns.

Rank Salespeople by Monthly Sales with Excel INDEX and MATCH

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic monthly sales ranking table where formulas can be easily copied across columns for consecutive months without referencing errors.
Observed behavior: Basic INDEX and RANK formulas work for the first month but fail when copied across subsequent columns because absolute and relative references are not structured correctly.

Before you start

Ensure your dataset is organized consistently, with salesperson names in a fixed column (e.g., Column A) and monthly sales data in adjacent columns. Verify that there are no identical duplicate sales values in a single month, as this can cause the MATCH function to return the same salesperson twice.

Solution 1Recommended

Use INDEX, MATCH, and LARGE Functions

This combination extracts the name of the salesperson corresponding to the Nth largest sales value, utilizing strategic absolute and mixed references so the formula can be dragged across columns.

By nesting LARGE inside MATCH, and then wrapping it in an INDEX function, you look up the specific sales volume's rank and retrieve the exact corresponding name. Locking the name column and ranking rows ensures stability.

Set up a ranking criteria area

Create a space below your data for the new table. Enter the rank numbers 1, 2, 3, 4, etc., down a single column (for example, in cells A8 through A11).

Enter the formula

Click on the first results cell (e.g., B8) and input the formula: =INDEX($A$2:$A$5,MATCH(LARGE(B$2:B$5,$A8),B$2:B$5,0))

Fill down the formula

Press Enter. Select cell B8, click the small square at the bottom right of the cell, and drag it down to fill the ranks for the first month.

Fill across the columns

Keep the column of newly calculated formulas selected, then click the fill handle again and drag it across the right to apply the ranking logic to subsequent months.

How the Formula Works: The LARGE function pulls the specific sales amount for the rank number you referenced. MATCH finds the position of that sales amount within the monthly column, and INDEX uses that position to return the salesperson's name from Column A.

Solution 2

Use the SORTBY Function (For Newer Versions)

If you are using a modern version of Excel that supports dynamic arrays, you can use SORTBY to rank the entire list automatically without needing a helper column.

Advanced Formulas in WPS

Rank Sales Data Dynamically in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookups and dynamic arrays, allowing you to rank your data effortlessly using INDEX, MATCH, or SORTBY formulas with the exact same syntax as Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open your monthly sales data document.
2. Apply the ranking formula: Select an empty cell and input your preferred formula, such as =INDEX($A$2:$A$5,MATCH(LARGE(B$2:B$5,$A8),B$2:B$5,0)).
3. Drag to copy: Use the fill handle at the bottom right of the cell to drag the formula down the rows and across your monthly columns.

100% compatibility with Microsoft Excel formulas, including INDEX, MATCH, and modern dynamic arrays.Lightweight architecture handles complex nested formulas across large datasets without lagging.Free to use with a highly intuitive user interface for seamless spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX and MATCH formula return duplicate names for different ranks?

If two salespeople have the exact same sales amount in a given month, the MATCH function will always find the position of the first occurrence, returning the same name twice. To resolve this, you can create a tie-breaker by adding a minuscule unique decimal to the source data (e.g., row number divided by 10000) so no two sales values are identical.

What is the purpose of the LARGE function in this ranking formula?

The LARGE function extracts the N-th highest value from a dataset. In this specific formula context, LARGE(B$2:B$5, 1) pulls the absolute highest sales amount for the month, LARGE(B$2:B$5, 2) pulls the second highest, and passes that exact value to the MATCH function to find who achieved it.

Why am I getting a #NAME error when using the SORTBY formula?

A #NAME error indicates that the spreadsheet software does not recognize the function you typed. This typically happens if you are using an older version of Excel or WPS Office that was released before dynamic array functions (like SORTBY) were introduced. If this occurs, use the INDEX, MATCH, and LARGE solution instead.

Excel & Data Problems