Working with large datasets in Excel can be intimidating, especially if you need to pull out specific insights—like the top or bottom performers in a list of sales data. Thankfully, Excel’s Index Match combined with the Large and Small functions offers a powerful solution. In this tutorial, I’ll walk you through how to use this function combo to find and extract the top 20 and bottom 20 values from any data set. Along the way, you’ll also learn some tips to maintain flexibility when you need to update or modify your data later on. Ready? Let’s dive in.

Getting Started: Prepping the Data

Before we even touch functions, one key tip is to convert your raw data into a table. Doing this makes it a lot easier to reference areas in your dataset, especially when working with large Excel sheets. Here’s how you do that:

  1. Select your entire dataset (don’t forget the headers).
  2. Navigate to the Insert menu and click on Table.
  3. When prompted, confirm that your dataset has headers (and most do).

This simple step will help smooth the process when you establish your formulas later.

Pulling the Top 20 Values with Large

Want to find the top 20 selling products? Here’s where the combination of Index Match and Large shines. Let’s break it down.

Step 1: Finding the Top Value Using the Large Function

Start by identifying the highest sales value, then pull the rest in the top 20.

  1. Pick the cell where you want your top values to appear (say E2).
  2. In that cell, enter the following formula: =LARGE(B:B, D2) Here, B:B refers to the column that holds the sales values. D2 should contain the rank number you’re searching for—starting with 1 for the highest value. This formula will give you the largest value in your sales column.

Step 2: Dragging for the Top 20

Once you pull the top value, drag down the fill handle to get the values for the top 20. Your formula adjusts automatically as you drag it through.

Step 3: Using Index Match to Find Associated Fruit Names

Now, we’re not just interested in the numbers—we need to know which product (or fruit) corresponds with the sales amount. Time to bring in the Index Match combo:

  1. In the cell next to the top value (e.g., F2), paste this formula: =INDEX(A:A, MATCH(LARGE(B:B, D2), B:B, 0)) Here’s a quick breakdown:
    • A:A refers to the column where your product names are stored.
    • MATCH(LARGE(B:B, D2), B:B, 0) finds the sales amount and returns the row number in your data.
    • INDEX uses that row number to pull the product name from A:A.
  2. Once entered, drag that formula down as well. You now have the top 20 fruits based on sales.

RELATED TOPIC: How to Define and Use Cell Names in Excel Formulas and Functions

Finding the Bottom 20 with Small

Getting the lowest 20 values is virtually the same as finding the top 20—except this time, we swap Large for Small. Let’s get into it.

Step 1: Using Small to Find the Lowest Values

  1. In your chosen cell (let’s say H2), input: =SMALL(B:B, H2)
  2. As with Large, B:B contains the sales numbers, and H2 should house the rank you’re pulling (1 for the lowest, 2 for the second lowest, etc.).
  3. Hit enter, and drag it down for your bottom 20.

Step 2: Finding the Product Names Using Small with Index Match

We follow the same logic as before:

  1. In the adjacent cell, use: =INDEX(A:A, MATCH(SMALL(B:B, H2), B:B, 0))
  2. Drag the formula down for all 20, and Excel lists your lowest performing products.

Why Use Index Match Over VLOOKUP or XLOOKUP?

At this point, some of you may be asking, “Why not just use XLOOKUP or VLOOKUP?” Both are well-known lookup functions in Excel, but here’s why Index Match still has a key advantage.

  • Index Match offers more flexibility, especially when your lookup value isn’t in the first column.
  • It allows for a two-dimensional lookup, whereas VLOOKUP is limited to one direction.
  • Performance-wise, Index Match is often faster when dealing with large datasets, as it doesn’t need to search through an entire data range multiple times.

In short, Index Match remains a versatile choice for those who regularly work with large or complex datasets.

RELATED TOPIC: How to Use the IF Function in Excel

Why Reference Cells Instead of Inputting Rank Numbers?

You might be thinking, why not just type in the rank (like “1” for the highest, “2” for the second highest) in the formula? While you could absolutely do that, it’s not very scalable.

By referencing another cell for your rank, like D2 or E2, you keep the process flexible. If you want to change the ranks you’re pulling, you can easily update those cells—and the formulas will update automatically. This approach saves time, reduces errors, and makes the whole process easier to maintain and scale.

Wrap Up

There you have it—the Index, Match, Large, Small functions to sift through your top and bottom data in Excel. Whether you’re handling 100 rows or 10,000 rows, integrating these functions can cut down on analysis time and boost accuracy. Best of all, you only need to tweak a few cell references when your dataset updates.

If you found this guide helpful, make sure to hit the thumbs-up on the embedded video above, and don’t hesitate to subscribe to keep learning new Excel tips and tricks. Have questions or suggestions? Drop a comment below—I want to hear from you!

Happy Excel-ing!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR