Return Multiple Match Values in Excel

Method #1: INDEX and AGGREGATE

(Places all returned answers in separate cells)

Pros:

• Works with any version of Excel
• Does not require the use of CTRL-Shift-Enter to create an array formula

Cons:

• More complex than Method #2

Method #2: TEXTJOIN

(Places all returned answers in a single cell as a delimited list)

Pros:

• Simpler than Method #1

Cons:

• Requires Excel 2016 and Office 365
• Requires the use of CTRL-Shift-Enter to create an array formula

Objective: Have the user select a Division name and use the selected Division to return a list of associated Apps.

In our table, the selected Division names should return lists like the following:

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.

Learn More

Method #1 – INDEX and AGGREGATE

This method will use the INDEX function with the AGGREGATE function to locate the associated Apps for the selected Division and compile the results into a new list.  We will also integrate an IF test to visually suppress any errors that may show up when the returned items do not fully populate the results list area.

To begin, select cell G4 and enter the Division “Game”.

NOTE:  We could provide the user with a drop-down list to ease the selection process for Division, but for simplicity, we will hardcode the Division name.  For a tutorial on creating unique dropdown lists from existing multi-valued lists, click the link below.

Excel: Extract unique items for dynamic data validation drop down list

The most common function people use when finding items in an Excel list is VLOOKUP.  If you require a refresher on the use of VLOOKUP, click the link below.

Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples

The problem with using VLOOKUP in this scenario is that VLOOKUP will always stop on the first encountered matching item in the search list.  If we are searching for “Game”, the VLOOKUP will always stop in cell A7.

We want to build a list where the 1^st occurrence of a “Game” App is placed in cell G5; the 2^nd occurrence is placed in cell G6; the 3^rd in cell G7; etc.

We will use the INDEX and AGGREGATE functions to create this list.  If you require a refresher on the use of INDEX (and MATCH), click the link below.

How to use Excel INDEX MATCH (the right way)

Select cell G5 and begin by creating an INDEX function.

=INDEX(array, row_num, [column_num])

The INDEX function has the following parameters:

• Array = the cells to have items extracted from and returned as answers.
• Row_num = the “up and down” position in the list to move to extract data.
• Column_num = the “left to right” position in the list to move to extract data.

We want to extract App names from cells B5:B14.

=INDEX($B$5:$B$14

We want to return the App named “Fightrr” from the 3^rd position in the App list, so as a test we will hard-code the number “3”.

=INDEX($B$5:$B$14,3)

If we fill the formula down the cells in column “G”, the App named “Fightrr” appears repeatedly, a behavior like the earlier VLOOKUP results.  We need to find a way to have the row_num’s return value change from “3” to “4” to “5” to “7”.  We cannot simply increase the value of the row-num parameter by 1 every time we repeat the formula; the parameter needs to change based on the position of the associated Division in column “A”.

We will use the AGGREGATE function to generate a list of rows (i.e. positions) where the selected Division (“Game”) is discovered.  The advantage of using the AGGREGATE function is that it can accept multiple answers without the use of CTRL-Shift-Enter.

The AGGREGATE function has the following parameters:

• Function_num = a number corresponding to a function in the AGGREGATE list.

We will use the SMALL function (number 15).

• Options = a number corresponding to a behavior for handling errors, hidden data, and other AGGREGATE and SUBTOTAL functions when mixed with data.

We will use option #3 to ignore all other issues.

• Array = the values to be aggregated.
  We will select cells A5:A14.
• [k] = optional value when using selection functions, like SMALL or LARGE.
  We will save this parameter for later.

TIP: To focus on one problem at a time, we will build the AGGREGATE function off to the side in column “H”.  Once the AGGREGATE function is working to our satisfaction, we will fold the logic of AGGREGATE into the INDEX function.

Select cell H5 and enter the following formula:

=AGGREGATE(15,3,$A$5:$A$14)

If we highlight the array parameter and press F9, we will see that the supplied array returns the words located in cells A5:A14.

Our objective is not to find the smallest word; we want to know which cells in the array match our selected Division.  To do this, we will test each cell in the array to see if it matches the selected Division.  Modify the array as follows.

=AGGREGATE(15,3,($A$5:$A$14=$G$4))

If we highlight the array parameter and press F9, we will see the following test results.

The problem here is that Excel interprets a FALSE response as 0 (zero) and a TRUE response as 1 (one).  If we use the SMALL function for discovery, the 0 will be selected first.  We want to convert the FALSE responses to errors and the TRUE responses to position numbers within the list (i.e. 3, 4, 5, 7).  To do this, we will divide the array by itself.

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)))

Because 0 ÷ 0 = ERROR and 1 ÷ 1 = 1, we get the following list of responses.

To change the 1’s to position numbers, we will multiply each 1 by its corresponding row number.

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14)))

This will provide the following responses.

Because we didn’t start our list on row 1, our positions in the list are offset; in this case by 4 rows.  Since the header for this table is in row 4, we will subtract the header row’s position value from the previous list of answers.

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4))

This will provide the following responses.

It’s now time to loop back and utilize the [k] option (the 4^th parameter) of the AGGREGATE function.  We want the SMALL function to increment by one for each copy of the AGGREGATE function.  This will be accomplished by a cleaver trick of counting the cells in an ever-increasing range selection.

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4),N)

In this case, “N” will be the following function.

ROWS($F$5:F5)

When used on the first AGGREGATE function, the result will be 1.  By anchoring the beginning of the range to cell $F$5 as an absolute reference and leaving the ending of the range F5 as a relative reference, the range will change its size as the range ending moves further away from the range beginning.

The formula with the intelligent row counter should appear as follows.

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5))

Now copy the formula (without the equals sign) from cell H5 and paste it into the row_num parameter of the INDEX function in cell G5.  The updated formula should appear as follows.

Original formula

=INDEX($B$5:$B$14,3)

Updated formula

=INDEX($B$5:$B$14,AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5)))

Fill the formula down through cells G5:G14.  We now have the list of associated Apps to the selected Division as well as errors for when our return list is shorter than our expected maximum list length.

We want to hide the errors.

The simple (read “lazy”) way of hiding the errors is to nest the entire INDEX/AGGREGATE function in an IFERROR function like the following.

=IFERROR(INDEX($B$5:$B$14,AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5))),””)

The problem is that the IFERROR must fully process the INDEX/AGGREGATE function to determine if an error is generated.  This can lead to many wasted CPU cycles when generating long lists of associated Apps.

The better tactic is to use an IF statement to count the number of times the selected Division appears in the list of Divisions and then compare that against the current App list’s length.  If the App list length exceed the number of times the selected Division appears in the original Division list, the IF function will not execute the INDEX/AGGREGATE formula.

To give the IF function something to compare against, select cell F4 and enter the following “helper” formula.

=COUNTIF(A5:A14,G4)

The updated IF function will perform the following test.

=IF(ROWS($F$5:F5)<=$F$4,INDEX/AGGREGATE,"")

This formula will test the ever-expanding range that begins in cell F5 to determine if the range height exceeds the value supplied by the helper cell F4.  The updated formula will appear as follows.

=IF(ROWS($F$5:F5)<=$F$4,INDEX($B$5:$B$14,AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5))),"")

Because “Game” has 4 entries in column “A”, when we get to the 5^th iteration the ROWS function in the IF function will generate a 5.  The test becomes “5<=4” which generates a FALSE condition, thereby display the empty text supplied by the two double quotes.

BONUS:

If you don’t want to have the “helper” formula in cell F4, you can fold the COUNTIF logic into the IF function.  The updated formula would appear as follows.

=IF(ROWS($F$5:F5)<=COUNTIF($A$5:$A$15,$G$4),INDEX($B$5:$B$14,AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5))),"")

NOTE: When the COUNTIF was in a “helper” cell, we did not make any of the references absolute because we were not repeating the formula across any other cells.  If we fold the COUNTIF logic into the IF function, we need to make the references absolute due to the repeated nature of the formula in column “G”.

The only drawback to this consolidation is that the counting of the selected Division must be repeated for each IF function.  In longer lists (i.e. hundreds of thousands of rows), this could negatively impact processing time.  By performing the COUNTIF as a separate “helper” calculation, it must only be performed once.

Method #2 – TEXTJOIN

Using the TEXTJOIN function (available in Excel 2016 of Office 365) we will perform

The TEXTJOIN function has the following parameters:

• Delimiter – the character that separates the returned values.
  We will create a comma-delimited list, so we will enter “,”.
• Ignore_empty – This determines whether to include any empty cells in the results list.
  We are unsure as to our needs, so we will use FALSE and change it later if needed.
• Text1 – This is the range of cells containing the source data.
  We will select A5:A14.

Begin by selecting cell G4 and replacing the Division “Game” with “Utility”.

Select cell H5 and entering in the following formula.

=TEXTJOIN(“,”,False,B5:B14)

This provides the following result.

Accord,Blend,Fightrr,Hackrr,Kryptis,Misty Wash,Perino,Sleops,Twenty20,WenCaL

We do not want the complete list of Apps, we only want Apps that are associated with the selected Division (cell G4).  To filter the list to only associated Apps, we will perform a logical test, using IF, to compare each of the Apps’ associated Division with the selected Division.  Update the formula as follows.  Remember to finish the formula by pressing CTRL-Shift-Enter.

=TEXTJOIN(",",FALSE,IF(A5:A14=G4,B5:B14,""))

This provides the following result.

Accord,,,,,Misty Wash,,,Twenty20,

If we highlight the IF function in the formula and press F9 we will see the following responses.

To remove the empty cell notations, update the formula by changing the Ignore_empty parameter from FALSE to TRUE. Remember to finish the formula by pressing CTRL-Shift-Enter.

=TEXTJOIN(",",TRUE,IF(A5:A14=G4,B5:B14,""))

This provides the following result.

Accord,Misty Wash,Twenty20

Feel free to Download the Workbook HERE.

LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses