## Lookup 1 Value, Return Many Matches

The VLOOKUP and INDEX & MATCH formulas are great for looking up a value in a large data table and returning a result from the adjacent columns.

But how can you return multiple results?

**What if your lookup value isn’t unique? What if it’s repeated in your data set? **

The standard formulas always return the first match.

You’d like to have a list of all the matches and you’d like to have it in a dynamic way.

### In the video below I show you 2 different methods that return multiple matches:

**Method 1** uses INDEX & AGGREGATE functions. It’s a bit more complex to setup, but I explain all the steps in detail in the video. It’s an array formula but it doesn’t require CSE (control + shift + enter).

**Method 2** uses the TEXTJOIN function. This is a relatively new function in Excel 2016, Office 365. It’s quite simple to setup but it’s an array formula that requires CSE.

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

Utility | Productivity | Game |
---|---|---|

Accord | Blend | Fightrr |

Misty Wash | Sleops | Hackrr |

Twenty20 | WenCal | Kryptis |

Perino |

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

Cell | Function | Range Height (in cells) |
---|---|---|

H5 | ROWS($F$5:F5) | 1 |

H6 | ROWS($F$5:F6) | 2 |

H7 | ROWS($F$5:F7) | 3 |

H8 | ROWS($F$5:F8) | 4 |

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.

**Excel Dashboards that Inform & Impress**

YasserKhalilAugust 30, 2018 at 6:35 pmThat’s awesome tutorial. Thanks a lot

Leila GharaniSeptember 3, 2018 at 4:03 pmI’m glad you like them :)

YenenehSeptember 7, 2018 at 5:44 amGreat tutorial. Thanks a lot.

MavisSeptember 25, 2018 at 4:52 pmAm loving you more with your awesome tutorials. Thanks Sweetie.

ZahidOctober 10, 2018 at 6:58 pmAwesome tutorials….I love them. I have below situation where data is coming in Col A,B,C and I want comments based on A & B (sample is below) any suggestion

A B C Comments

Invoice 5001 100 Paid

Payment 5001 -100 Paid

Pending 5002 -200 Pending

Invoice 5002 200 Pending

Invoice 5003 500 Open

SybilleNovember 15, 2018 at 4:56 pmAwesome Leila, thank you so much. I like the way you describe and explain each step. It helps understand what I’m doing rather than blindly copy and paste the formula without any idea of what I’m doing.

Leila GharaniNovember 20, 2018 at 12:32 pmThank you Sybille! I’m glad you find the tutorials easy to follow.

chahine atallahJanuary 25, 2019 at 5:56 pmwhy we cannot use the function small instead of aggregate

=INDEX($B$5:$B$14,SMALL(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14)-ROW($A$4),ROWS($A$5:A5)),)

Joseph Daniel GarfieldFebruary 14, 2019 at 5:51 amYou really are amazing!!! Thank you for your helpful YouTube videos and wonderful documentation/instructions!

Leila GharaniFebruary 17, 2019 at 5:34 pmI’m glad you find them useful Joseph. Thank you for your support.

NeilFebruary 17, 2019 at 10:39 pmHi, I am loving this example, I am wondering if it’s possible to with the results it brings up (i.e. in G5 it brought up Fightrr) to know where it got that information from.

What I mean is it has pulled Fightrr from B7, I am wondering if that information is back traceable to then be able to show say the value to the right (so 11,649 in H5 for example)?

Leila GharaniFebruary 20, 2019 at 5:04 amHi Neil – you could add another cell and return the address of the cell returned instead of the content of the cell. The ADDRESS function can come in handy here. Something like: ADDRESS(ROW($A$4)+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)),2)

NeilFebruary 20, 2019 at 10:36 amThat’s so cool. How can I take that and wrap it so it then shows what is in that cell? so it is pulling “$B$6” if I change the last bit to ,3) it is bringing up “$C$6”. Is it possible to wrap this formula to then take “$C$6” and instead put the value of that cell. So for the first one in your example will show “Blend” in G5 and 17990 in H5?

FrankMarch 29, 2019 at 4:16 amYou can use the indirect function to obtain what is contained in the cell.

=indirect(“location of cell reference”)

Fabian DiazFebruary 18, 2019 at 1:27 amThank you so much for this. I have always tried to do do it

Leila GharaniFebruary 20, 2019 at 4:59 amYou’re very welcome Fabian.

IshaFebruary 19, 2019 at 3:22 pmHi! This is awesome, and I was able to use it in a fairly small data group. Now I have a problem. I have about 6000 rows where I am trying to look up for values to join using the TEXTJOIN formyla, and it only gives me a #VALUE error. Any options to make this work?

Leila GharaniFebruary 20, 2019 at 5:15 amWhen using text join make sure you don’t press enter to leave the formula. Instead you need to press Control + Shift + Enter to get the curly brackets on there. I show this step in the video.

JeremyFebruary 20, 2019 at 6:46 pmThanks for the great tutorial. It’s going to be helpful once I take this newfound Index Aggregate method and try to use it for my mailbox permissions reporting for Office 365 migration purposes.

Erik VegaFebruary 20, 2019 at 8:30 pmThank you sooo much, Leila!! You are absolutely the best at explaining things in an easy way and always recommend any friend that wants to learn Excel to follow your youtube channel.

Just a little question. I am doing this but instead of a fixed range, I am using a table and for some reason, the formula ignores the first result. If I change Table13[[#Headers],[Main Skill]] for D1 it does the same and if I change it for D2 then ignores the last result.

=AGGREGATE(15,3,(Table13[Main Skill]=$X$1)/(Table13[Main Skill]=$X$1)*ROW(Table13[Main Skill])-ROW(Table13[[#Headers],[Main Skill]]),ROWS($V$2:V3))

Leila GharaniFebruary 23, 2019 at 6:10 pmHi Erik – how about if you change the last rows reference to ROWS($V$2:V2)?

Erik VegaFebruary 27, 2019 at 8:40 amWorks like a charm!! You are the greatest!! (^_-)

Mark McKenzieMarch 9, 2019 at 8:56 pmYou’re the bomb. Thank you so much

Amos GarciaMarch 12, 2019 at 12:41 pmHi! Thanks for sharing your knowledge this help a lot in my job. Now I have a problem and is that I have a data, that are comments written in the database, that the formula has to look for but it begins with a dash (-) and that give a #value! error. How you thing will be the best approach to solve it?

Leila GharaniMarch 13, 2019 at 6:33 pmHi Amos, If you import the data from the data tab, you can define in the wizard (or power query view) that the column should be imported as text.

Michael AngleseyMarch 19, 2019 at 8:33 pmVery good video! How can I use this to have the same effect when my data set is inside a table that will be resorted? The rows that the data sits on changes depending which column the table is sorted by.My formula only works if that particular column in my original data set is sorted largest to smallest:

=AGGREGATE(15,3,(Table1[NB Count YTD]=Table5[NB Count YTD])/(Table1[NB Count YTD]=Table5[NB Count YTD])*(ROW(Table1[NB Count YTD])-ROW($J$2)),5)

Bryon SmedleyApril 19, 2019 at 1:05 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

The XelPlus Team

Mitchel ArawMarch 20, 2019 at 4:20 pmHi, can you help as to how to code in excel?

A1 Contains Abc def-111 ghi def-112 jkl def-113 mno def-114 I want to transfer to B1 Result would be def-111,def-112,def-113,def-114

Bryon SmedleyApril 7, 2019 at 7:51 pmI would recommend using Power Query to split the data by a custom delimiter. Your custom delimiter would be “Abc “; this includes a space at the end. This will create the list exactly as you wish. You can then dump the results back into Excel and delete the query if you have no need for updates.

Thanks – XelPlus Team

AnupMarch 21, 2019 at 1:01 pm=INDEX($B$4:$B$13,SMALL(IF($A$4:$A$13=$E$3,ROW($B$4:$B$13),””),ROW(A1))-ROW($A$3))

AnupMarch 21, 2019 at 1:15 pmis their any necessity of using aggregate function in above.

this works perfectly…. or maybe i don’t know the difference

Bryon SmedleyApril 7, 2019 at 7:57 pmCan you please explain what you are referencing? I will be happy to answer with a bit more clarity of your question.

Thanks – XelPlus Team

Jesse HamiltonMarch 21, 2019 at 9:01 pmHi! Lets say one of the cells in the Apps column is blank for Productivity. In the textjoin formula it returns a 0. What can be done so the 0 doesn’t show?

Bryon SmedleyApril 9, 2019 at 11:00 pmTry this on for size:

=SUBSTITUTE(TEXTJOIN(“,”,TRUE,IF(A5:A14=G4,B5:B14,””)),”0,”,””)

We generate the list with the zeroes using the TEXTJOIN function; afterwards, we replace the zeros with nothing using the SUBSTITUTE function.

Thanks for the question. I’m sure this will come up for many people.

The XelPlus Team

David LewisMarch 22, 2019 at 3:48 amHi Leila,

You’ve come very close to helping me with the problem I’m having, thank you!

I’m attempting to search a column of names to find matches using an cell reference for input and this simple formula:

=INDEX($A$2:$A$1500,MATCH(G10,A:A,1))

It’s basically a search function on a long list of names (1300+). This helps to prevent entering duplicate data. this works except that only the 1st result is returned. To account for similar/multiple names [using Excel 2010] I thought to nest my INDEX | MATCH with this formula however I’m not winning.

Any guidance suggestions?

Bryon SmedleyApril 7, 2019 at 7:48 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

XelPlus Team

RossApril 4, 2019 at 3:45 pmI’m having difficulty with textjoin. If there are dashes in my data, will that disrupt this? For Example, I need all occurrences of UNIQUE ID: 301200, it should spit out 9999, and 11405-1. This is also in a different sheet, if that matters.

Bryon SmedleyApril 7, 2019 at 7:07 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

Ale ArboApril 14, 2019 at 10:43 pmHi Leila Thank you so much for this tutorial i am using method 1 i have Ex 2013. It works like a charm with small sets of data but when I tried to use it on large sets of data (380K rows) excel gives me a msg that reads:

“You entered too many arguments for this function”

Do you have any suggestions.

=AGGREGATE(15,3,(Table1[ELM]=Sheet2!$A$3)/(Table1[ELM]=Sheet2!$A$3)*(row(Table1[ELM])-row(Table1[[#Headers],[ELM]])),rows($A$3,A3))

Thank you so much for your help.

Bryon SmedleyApril 19, 2019 at 1:01 pmIt appears as if your ROW function has two arguments. The ROW function only supports a single argument. I’ve highlighted the problem area below.

=AGGREGATE(15,3,(Table1[ELM]=Sheet2!$A$3)/(Table1[ELM]=Sheet2!$A$3)*(row(Table1[ELM])-

row(Table1[[#Headers],[ELM]])),rows($A$3,A3))Thanks – The XelPlus Team

Vo Duy MInhApril 16, 2019 at 11:15 amDear you

I have a problem and hope to have a good solution from you.

Your instruction is for a drop-down list,

But I want to extract data from a sheet to another sheet. In the data I have some lookup value completely similar to one another. With INDEX and MATCH fuction, naturally, the results are the same (the first name, almost similar to your Vlookup example). Can you give me a solution to apply INDEX/AGGREGRATE to row list instead of drop-down list?

Thank you very much

Bryon SmedleyApril 19, 2019 at 12:57 pmMicrosoft Excel Tech Community

The XelPlus Team