Return Multiple Values in Excel Based on Single Criteria

Dragging TEXTJOIN Formula through Fill Handle in Excel

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

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.

A Data Set in Excel

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)

UNIQUE Function in Excel

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,""))

TEXTJOIN and IF Functions in Excel

You will see, we get all the years when Uruguay became champion. 1930 and 1950.TEXTJOIN and IF Functions in Excel

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))

TEXTJOIN and FILTER Function in Excel

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

Dragging a TEXTJOIN Formula through Fill Handle in Excel

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.

A Complex Formula in Excel

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.

Dragging the Fill Handle for an Array Formula in Excel

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

A Complex Array Formula in Excel

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)))

A Complex Array Formula

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)

FILTER Function in Excel

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

Dragging Fill Handle to the Right in Excel

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)

FILTER Formula in Excel

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)),"")

A Complex Array Formula in Microsoft Excel

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.

Dragging the Fill Handle for array formula in Excel

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.

Dragging the Fill Handle for array formula in Excel

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo