# How to Return Multiple Values Based on Single Criteria in Excel

To return multiple values based on a single criterion in Excel, you can use a combination of the INDEX, SMALL, IF, and ROW functions. The process involves creating an array formula that checks the criteria and then returns the matching values. Here's a step-by-step guide on how to do it:

- Set up your data in Excel. Make sure you have a clear column or row labels for your criteria and values.
- Decide where you want to display the results. Choose an empty cell or range of cells where you want the matching values to appear.
- In the first cell of the chosen range, enter the following array formula:

`=IFERROR(INDEX(ValueRange,SMALL(IF(CriteriaRange=Criteria,ROW(CriteriaRange)-ROW(FirstCellInCriteriaRange)+1),ROW(1:1))),"")`

- Replace the following placeholders in the formula with the appropriate cell references:

`ValueRange`

: The range of cells containing the values you want to return.`CriteriaRange`

: The range of cells containing the criteria you want to match.`Criteria`

: The cell containing the specific criterion you want to use for matching.`FirstCellInCriteriaRange`

: The first cell in the`CriteriaRange`

.

- After entering the formula, press Ctrl + Shift + Enter to turn it into an array formula. Excel will surround the formula with curly braces
`{}`

to indicate it's an array formula. - Drag the formula down or across the range of cells where you want the results to appear. The formula will return the matching values based on the specified criterion.

## Example

Let's say you have a list of sales representatives and their sales amounts, and you want to return all sales amounts for a specific sales representative.

- Set up your data:

```
A B
1 Sales Rep Sale Amount
2 John 1500
3 Jane 2000
4 John 1200
5 Jane 2500
6 Jim 1800
```

- Choose an empty cell where you want the matching values to appear (e.g., cell E2).
- In cell E2, enter the following array formula:

`=IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$D$1,ROW($A$2:$A$6)-ROW($A$2)+1),ROW(1:1))),"")`

In this example, `$B$2:$B$6`

is the `ValueRange`

, `$A$2:$A$6`

is the `CriteriaRange`

, `$D$1`

is the `Criteria`

, and `$A$2`

is the `FirstCellInCriteriaRange`

.

- Press Ctrl + Shift + Enter to turn the formula into an array formula.
- Drag the formula down to cells E3, E4, etc., to return additional matching values.
- Now, if you enter a sales representative's name in cell D1 (e.g., "John"), cells E2 and below will display the sales amounts for that sales representative.

Did you find this useful?