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:

1. Set up your data in Excel. Make sure you have a clear column or row labels for your criteria and values.
2. Decide where you want to display the results. Choose an empty cell or range of cells where you want the matching values to appear.
3. 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))),"")

4. 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.

5. 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.
6. 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.

1. 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

2. Choose an empty cell where you want the matching values to appear (e.g., cell E2).
3. 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.

4. Press Ctrl + Shift + Enter to turn the formula into an array formula.
5. Drag the formula down to cells E3, E4, etc., to return additional matching values.
6. 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.