Return Multiple Values in Excel Based on Single Criteria (3 Options)

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

Download Practice Workbook

3 Options to 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.

Option 1: Return Multiple Values in Excel Based on Single Criteria 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:

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.

i. 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:

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.

ii. 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:

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.

Read More: How to Extract Data from Cell in Excel (5 Methods)

Option 2: Return Multiple Values in Excel Based on Single Criteria 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.

i. 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:

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:

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

ii. 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:

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

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

Read More: How to Extract Data from Excel Based on Criteria (5 Ways)

Similar Readings

• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• How to Import Data from Text File into Excel (3 Methods)
• Excel VBA: Pull Data Automatically from a Website (2 Methods)
• How to Convert Text File to Excel Automatically (3 Suitable Ways)
• How to Convert Notepad to Excel with Columns (5 Methods)

Option 3: Return Multiple Values in Excel Based on Single Criteria in a Row

Finally, if you want, you can return multiple values based on 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.

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.

Read More: How to Extract Data From Table Based on Multiple Criteria 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.

Related Articles

• How to Transfer Data from One Sheet to another in Excel Using Macros
• Import Data from Excel into Word Automatically Using VBA (2 Ways)
• How to Extract Data from a List Using Excel Formula (5 Methods)
• Pull Data from Multiple Worksheets in Excel VBA
• How to Extract Year from Date in Excel (3 Ways)
• Extract Text After a Character in Excel (6 Ways)