In the example below we have the list of all the **FIFA World Cups** from 1930 to 2018. We have the **Year **in **Column B**, the **Host Country** in **Column C**, the **Champion** countries in **Column D,** and the **Runners-up** countries in **Column E**. Let’s use it to demonstrate how you can extract multiple results from a given criterion.

## Method 1 – Returning Multiple Values Based on a Single Criteria in a Single Cell

We’ll extract the names of all the champion countries to one column and will add the years in which they became champions to the adjacent cells. Let’s say we want to extract the names of the champion countries in **Column G **named **Country.**

- Enter this formula in cell
**G5:**

`=UNIQUE(D5:D25)`

**D5:D25** refers to the **Champions**.

- Press
**Enter**. - All the champions are listed in
**Column G.**

*Note**: While using Microsoft 365, there is no need to use the Fill Handle to get all the values. All values will appear automatically.*

### Case 1.1. Using TEXTJOIN and IF Functions

In the following dataset, we have unique **Champion **countries in **Column G** based on the previous results. We need to find out the **Years **of these **Champion **teams in one cell individually.

- Copy this formula in the
**H5**cell:

`=TEXTJOIN(",",TRUE,IF($D$5:$D$25=G5,$B$5:$B$25,""))`

- Press
**Enter**to get the output as**1930,1950**. - Use the
**Fill Handle**by dragging down the cursor while holding the right-bottom corner of the**H5**

- We’ll get the outputs like this.

**Formula Explanation**

- Here
**$B$5:$B$25**is the lookup array. We want to look up for the years. **$D$5:$D$25=G5**is the criteria we want to match. We want to match cell**G5**(**Uruguay**) with the**Champion**column (**$D$5:$D$25).**

### Case 1.2. Combining TEXTJOIN and FILTER Functions

- Input this formula in
**H5:**

`=TEXTJOIN(",",TRUE,FILTER($B$5:$B$25,$D$5:$D$25=G5))`

- Press
**Enter**. - Use the
**Fill Handle**to copy the formula to the rest of the column.

**Formula Explanation**

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

## Method 2 – Return Multiple Values Based on Single Criteria in a Column

### Case 2.1. Using a Combination of INDEX, SMALL, MATCH, ROW, and ROWS Functions

Suppose we need to find out in which years **Brazil **became the champion. In the following dataset, we need to find it in cell **G5**.

- Copy this formula in cell
**G5**:

`=INDEX($B$5:$B$25, SMALL(IF(G$4=$D$5:$D$25, MATCH(ROW($D$5:$D$25),`

`ROW($D$5:$D$25)), ""), ROWS($A$1:A1)))`

- As this is an array formula, you need to press
**Ctrl + Shift + Enter**. - We’ll find the years in which
**Brazil**became champion as output.

Using the above formula, you can extract the years of the championship of any other country.

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

**Formula Explanation**

- Here
**$B$5:$B$25**is the lookup array. We look for years. If you have anything else to look up for, use that. **G$4=$D$5:$D$25**is the matching criteria. We want to match the content of the cell**G4**,**Brazil**with the contents of the cells from**D5**to**D25**. You use your criteria.- Again,
**$D$5:$D$25**is the matching column. You use your column.

- Let’s also find out the years when the
**World Cup**was won by the host countries. The formula will be in the**H5**cell like this:

`=INDEX($B$5:$B$25, SMALL(IF($C$5:$C$25=$D$5:$D$25, MATCH(ROW($D$5:$D$25),`

`ROW($D$5:$D$25)), ""), ROWS($A$1:A1)))`

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

### Case 2.2. Applying FILTER Function

The **FILTER** function is available in Office 365 only.

- The formula in cell
**G5**to sort out the years when**Brazil**was the champion will be:

**=FILTER($B$5:$B$25,$D$5:$D$25=H$4)**

**Formula Explanation**

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

- Press
**Enter**to get the outputs.

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

The output will look like this.

## Method 3 – Return Multiple Values in Excel Based on Single Criteria in a Row

Let’s find out the years when specific countries were champions in a different way.

- Select a cell and enter Brazil. In this case, we’ll use
**G5**. - Copy this array formula in the adjacent cell i.e.
**H5**, and press**Ctrl + Shift + Enter**.

`=IFERROR(INDEX($B$5:$B$25, SMALL(IF($G5=$D$5:$D$25,`

`ROW($B$5:$B$25)-3,""), COLUMN()-7)),"")`

- Press
**Enter**. - Excel will find the years of different specific countries when they became champions first. It will happen automatically in Microsoft 365 without using the
**Fill Handle**. - Use the
**Fill Handle**to drag the entire column to the right to get the other results.

- We’ll get the output like this.

**Formula Explanation**

- Here
**$B$5:$B$25**is the lookup array. We looked up for years in the range**B5**to**B25**. If you want anything else, use that. **$G5=$D$5:$D$25**is the matching criteria. I want to match cell**G5**(**Brazil**) with the**Champion**column (**D5**to**D25**). If you want to do anything else, do that.- I have used
**ROW($B$5:$B$25)-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 where you are inserting the formula. For example, if you are inserting the formula in column**G**, use**COLUMN()-6.**

**Download Practice Workbook**

**<< Go Back to Lookup | Formula List | Learn Excel**

SUPERB VERY GOOD LESSON FORA NOVICE.

THANK YOU SO MUCH

Hello

Selam,You are most welcome.

Regards

ExcelDemy