How to Use INDEX and MATCH to Return Multiple Values in Excel

To use INDEX and MATCH to return multiple values in Excel, you can use an array formula or combine it with other functions like IFERROR and SMALL. In this tutorial, we will use an array formula approach to return multiple values.

Preparing the Data

Before starting, ensure you have a data set with a lookup column and a column containing the values you want to return. For example, you can have a table of sales data with columns for the salesperson, month, and sales amount.

Using INDEX and MATCH to Return Multiple Values in Excel

Let's assume we want to return all the sales amounts for John Doe in the data above.

1. First, find an empty cell where you want the results to be displayed. For this example, let's use cell E1.
2. In cell E1, enter the following array formula:

{=IFERROR(INDEX($C$1:$C$5, SMALL(IF($A$1:$A$5="John Doe", ROW($A$1:$A$5)-ROW($A$1)+1), ROW(1:1))), "")}

3. Instead of pressing Enter, press Ctrl + Shift + Enter. This will create an array formula, and you will see curly brackets { } around the formula in the formula bar.
4. Now, the first sales amount for John Doe should appear in cell E1.
5. To return the additional sales amounts for John Doe, you need to copy the formula down to other cells.
6. Select cell E1, move your cursor to the bottom right corner of the cell until it turns into a plus sign (+). Then, click and drag it down to fill as many cells as needed.
7. As you fill the cells, the formula will automatically update the ROW(1:1) part, and you will see the other sales amounts for John Doe.

Example

Sales Data

Results

In this example, we used INDEX and MATCH to return all sales amounts for John Doe from the sales data. The results are displayed in a separate column with each sales amount in a different row.