Today I am going to show how you can return multiple values based on single criteria in Microsoft Excel.

**Table of Contents**hide

**Download Practice Workbook**

**Return Multiple Values in Excel Based on Single Criteria**

Let us have a look at this data set first. We have the list of all the FIFA World Cups that took place from 1930 to 2018.

We have the years in column **B**, the host countries in column **C**, the champion countries in column **D** and the runners-up countries in column **E**.

Now let us try to extract multiple values based on single criteria from this data set.

**1. Returning Multiple Values in a Single Cell**

First of all, let us try to return multiple values in a single cell.

We will try to extract the names of all the champion countries in one column and the years in which they became champions in the adjacent cells.

To extract the names of the champion countries, we will use the **UNIQUE()** function of Excel.

I make a new column named **Country** and enter this formula in the first cell, **G4**:

`=UNIQUE(D4:D24)`

See, we have got a list of all the champion countries.

Now we will try to extract out the years in which they became champions in the adjacent cells.

We can accomplish this in two ways.

**1.1 Using TEXTJOIN and IF Functions**

**Note:** This method is only possible in **office 365**.

In the adjacent cell of the first cell of the column **Country**, enter this formula:

`=TEXTJOIN(",",TRUE,IF($D$4:$D$24=G4,$B$4:$B$24,""))`

You will see, we get all the years when Uruguay became champion. 1930 and 1950.

Now drag the **Fill Handle** to copy the formula to the rest of the cells.

See, we have got the years of the championship of all the countries beautifully.

- Here
**$B$4:$B$24**is the lookup array. I want to look up for the years. If you want anything else, use that one. **$D$4:$D$24=G4**is the criteria I want to match. I want to match cell**G4**(Uruguay) with the**Champion**column (**$D$4:$D$24).**If you want anything else, use that one.

**1.2 Using TEXTJOIN and FILTER Functions**

**Note:** This method is also only possible in **office 365**.

We can execute the same task using the **TEXTJOIN()** and the **FILTER()** functions of Excel.

Select the first cell of the column Years of Championship and enter this formula:

`=TEXTJOIN(",",TRUE,FILTER($B$4:$B$24,$D$4:$D$24=G4))`

Now drag the **Fill Handle** to copy the formula to the rest of the cells.

See, we have again got all the countries and their years of championship arranged beautifully.

- Here
**$B$4:$B$24**is the lookup array. I want to look up for the years. If you want anything else, use that one. **$D$4:$D$24=G4**is the criteria I want to match. I want to match cell**G4**(Uruguay) with the**Champion**column (**$D$4:$D$24).**If you want anything else, use that one.

### 2. Returning Multiple Values in a Column

The above-mentioned functions are only available in **office 365**. Now if you do not have an **office 365 **subscription, you can follow these methods and return multiple values based on a criterion in a column.

**2.1 Using a Combination of INDEX, SMALL, MATCH, ROW and ROWS Functions**

Letâ€™s try to find out in which years Brazil became the champion.

To accomplish this, I make a new column called **Brazil **and enter this formula there in the first cell:

`=INDEX($B$4:$B$24, SMALL(IF(G$3=$D$4:$D$24, MATCH(ROW($D$4:$D$24), ROW($D$4:$D$24)), ""), ROWS($A$1:A1)))`

As this is an array formula, now I press **Ctrl+ Shift+ Enter.**

You see, I have found the first year when Brazil was champion. Then I drag the **Fill Handle** and get all the other years when Brazil was champion.

- Here
**$B$4:$B$24**is the lookup array. I look for the years. If you have anything else to look up for, use that. **G$3=$D$4:$D$24**is the matching criteria. I want to match the content of the cell**G3**, Brazil with the contents of the cells from**D4 to D24**. You use your criteria.- Again,
**$D$4:$D$24**is the matching column. You use your column.

If you want to know more details about this formula, visit this link.

Now, using the above formula you can extract out the years of the championship of any other country.

For example, to find out the years when Argentina was champion, create a new column **Argentina** adjacent to the one **Brazil**, and drag the formula to the right by using the **Fill Handle**.

See, we got the years when Argentina was champion. The year 1978 and 1986.

You can do it for all other countries.

Before moving to the next section, I have one small question for you. Can you find out the years when the World Cup was won by the host countries?

Yes. You have guessed right. The formula will be:

`=INDEX($B$4:$B$24, SMALL(IF($C$4:$C$24=$D$4:$D$24, MATCH(ROW($D$4:$D$24), ROW($D$4:$D$24)), ""), ROWS($A$1:A1)))`

See, the host country became champion in 1930,1934,1966,1974,1978 and 1998.

**2.2 Using FILTER Function**

If you do not want to use the complex formula as mentioned above, you can accomplish the task pretty conveniently using the **FILTER() **function of Excel.

But the only problem is that the **FILTER()** function is available in **Office 365** only.

Anyway, the formula to sort out the years when Brazil was champion will be:

`=FILTER($B$4:$B$24,$D$4:$D$24=G$3)`

- As usual,
**$B$4:$B$24**is the lookup array. Years in my case. You use your one. **$D$4:$D$24=G$3**is the matching criteria. You use your one.

Now like the earlier method, you can create a new column **Argentina** just beside **Brazil** and drag the **Fill Handle** to the right to get the years when Argentina was champion.

And finally, letâ€™s try out this time in which years the host country became runners-up.

Can you guess the formula?

Yes. You are right. The formula will be:

`=FILTER(B4:B24,C4:C24=E4:E24)`

See, the host countries became runners-up in the years 1950 and 1958.

**3. Returning Multiple Values in a Row**

Finally, if you want, you can return multiple values based on a criteria in a row.

To find out the years when Brazil was champion, select a cell and enter Brazil.

Then write this array formula in the adjacent cell and press **Ctrl + Shift +Enter**.

`=IFERROR(INDEX($B$4:$B$24, SMALL(IF($G4=$D$4:$D$24, ROW($B$4:$B$24)-3,""), COLUMN()-7)),"")`

You get the first year when Brazil was champion, 1958.

Now drag the **Fill Handle** to the right and you will get all the years when Brazil was champion.

See, Brazil was champion five times in the years 1958,1962,1970,1994 and 2002.

- Here
**$B$4:$B$24**is the lookup array. I looked up for years in the range**B4**to**B24**. If you want anything else, use that. **$G4=$D$4:$D$24**is the matching criteria. I want to match cell**G4**(Brazil) with the**Champion**column (**D4**to**D24**). If you want to do anything else, do that.- I have used
**ROW($B$4:$B$24)-3**because this is my lookup array and the first cell of this array starts in row number 4 (**B4**). For example, if your lookup array is**$D$6:$D$25,**use**ROW($D$6:$D$25)-5.** - Â In place of
**COLUMN()-7,**use the number of the previous column of where you are inserting the formula. For example, if you are inserting the formula in column**G**, use**COLUMN()-6.**

If you want to know more details about this formula, visit this link.

Now if you want, you can insert the names of other countries under Brazil, and then drag the **Fill Handle** to sort out the years in which they became champions.

I am going to do this for Argentina, Italy, France, Uruguay and West Germany.

**Conclusion**

So, using these methods, you can return multiple values based on single criteria in a single cell, or in a row and column. Do you know any other method? Or do you have any questions? Feel free to inform us.