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

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.

TJJuly 22, 2019 at 11:33 pmI ran in to some #errors in a slightly modified version I was trying to make – had to convert to absolute references; i.e. – change from:

{=TEXTJOIN(“, “,TRUE,IF(F2:F999999=J1,G2:G999999,””))}

to

{=TEXTJOIN(“, “,TRUE,IF($F$2:$F$999999=K3,$G$3:$G$999999,””))}

(But then I ran into this crashing Excel when trying to apply it to a couple hundred thousand lines at once … so couldn’t really use it quite as I was hoping.)

PS – love the tutorial, especially the step-by-step aspect. Well done!

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

Allen SungchangApril 20, 2019 at 7:14 amHi there. In my excel worksheet, textjoint is not working? Need help please.. Thank!

Bryon SmedleyApril 22, 2019 at 6:32 pmIs it not working, or is it not an available function? Can you be more specific as to the problem?

The XelPlus Team

sabrinaApril 23, 2019 at 1:04 pmHi Leila,

Need help please.. Thanks

Kindly assist me on this..how to text join the values for dates?

I’ve tried your sample table it appears as per below:-

FIND multiple matches

Apps for Division:

Division dates Revenue 3 1107002568

1803002508 02/2018 17,760 02/2018 #NAME?

1803002508 01/2018 17,990 03/2019

1803002508 07/2018 11,649 05/2018

1803002508 01/2019 18,701

1803002508 03/2018 7,718

1803002508 04/2018 30,400

1803002508 11/2018 15,033

1107002568 02/2018 11,022

1107002568 03/2019 20,400

1107002568 05/2018 14,432

Bryon SmedleyMay 5, 2019 at 11:17 amMicrosoft Excel Tech Community

The XelPlus Team

MarkoApril 28, 2019 at 8:16 amI WANNA MARRY U.. many tnx. my issue has been solved finally

cheers

DonMay 13, 2019 at 4:46 pmHi,

I’ve implemented the formula for using the first method and it works when I enter the entire text that I’m looking for. Is it possible to modify the formula so if I enter only a fragment of a word, it will return all the possible matches. For example, if I were to enter ca, it would return cat, car, cabin.

Any help is appreciated!

ChrisAugust 23, 2019 at 7:30 pmHi Donovan – Leila covers this with user forms inside her VBA course. An alternative are the new Dynamic arrays. Check out Leila’s video on that here.

WigiJune 14, 2019 at 8:42 amHi, very very awesome tutorial.

Quick question though.

In the index-aggregate formula, Is it really necessary to subtract the header’s row position?

Wherein the formula is using the small function, in the example above, 7 is the smallest number which will still return “Fightrr” since 7 will be the smallest number or am I missing somethin?

Leila GharaniJune 19, 2019 at 2:48 pmIn this example – yes – it’s necessary because otherwise the aggregate function would return the wrong address to the Index function. Index will move down 7, starting from the first row of data instead of the adjusted number.

TigerBellyJune 19, 2019 at 5:10 pmHello, this has been really helpful! I’ve been working on this for hours before I found your tutorial.

Question – Is there a way to get the output to be horizontal – ie. in the same row but across different colums?? Thank you!

Leila GharaniJune 25, 2019 at 7:37 amYes you can get it horizontal. Check out this video: https://www.xelplus.com/find-multiple-matches-in-excel-dependent-drop-down-list/

GinoJune 20, 2019 at 12:38 pmJust curious why my comments are still awaiting moderation. Subsequent comments by others appear to post without that warning. Thank you!

Leila GharaniJune 25, 2019 at 7:36 amHi Gino – I can’t see your other comments….

GinoJuly 1, 2019 at 7:58 pmI’m sorry, Leila. I can see them so I assume they are stuck somewhere along the way! :) Here’s what I wrote previously.

Your comment is awaiting moderation.

Amazing work as always, Leila! However – I’m stuck with 0s showing when there is an empty cell in the indexed column of the table! Any ideas on how to mask that 0?

Your comment is awaiting moderation.

Sorry – meant to add the data table column I’m indexing has blank cells. I don’t want to just “hide” the zeros returned but rather have the formula skip over the blank cell row to the next non-blank value. For example, return this:

My comment is 1.

My comment is 3.

My comment is 6.

instead of…

My comment is 1.

0

My comment is 3.

0

0

My comment is 6.

Sure hope that makes sense! Thank you so much for all you do!

Cheers,

Gino

mirJune 21, 2019 at 3:01 pmI spent a full day trying to do what this function does. I finally found this tutorial and got it to work fairly quickly! I’m eternally grateful!!

Leila GharaniJune 25, 2019 at 7:35 amI’m very happy to hear that : )

Tim JJune 28, 2019 at 6:19 pmAbsolutely BRILLIANT, Leila!!!!!!

I knew there had to be a way to do this, but until today I had no idea of the AGGREGATE() function!

I was trying to do something similar with INDEX, MATCH using CSE (Ctrl+Shift+Enter) array formulas, but alas, you just can’t use MATCH with array results.

Your tutorial breaks each solution down beautifully.

Thanks SO much!!!!!!

Leila GharaniJuly 4, 2019 at 6:46 amYou’re very welcome Tim!

Robin HallJuly 9, 2019 at 4:02 pmHey Leila,

This is an excellent tutorial, thank you. One challenge I’ve had though is that Excel seems to have a glitch. I’ve structured my formula to deliver an array of numbers and then operate on them. When I press f9 the array show’s correctly however Excel doesn’t want to give me the correct answer after this.

{=SUM(INDIRECT(ADDRESS(ROW(),WEEKDAY($C$12:$C$19),1)))} [Ctrl-Shift-Enter pressed]

Breaking it down the Weekday function gives me:

{3;6;2;2;2;2;4;5}

The Address function gives me:

{“$C$5″;”$F$5″;”$B$5″;”$B$5″;”$B$5″;”$B$5″;”$D$5″;”$E$5”}

The Indirect function give me:

{5;5;7;7;7;7;5;7}

And the sum function gives me:

50

But…

When I Ctrl-Shift-Enter I simply get 5

I’m using Excel for Mac v16.26 of Microsoft 365 subscription.

Any thoughts?

Thanks for anything you can offer.

Rob

Leila GharaniJuly 10, 2019 at 9:29 amIn my Excel version your formula works correctly without pressing Control+Shift+Enter (I have INSIDER version though with the new Calc. engine)

Dustin JamesJuly 10, 2019 at 5:35 amI’m going crazy. I think this tutorial will help, but I’m still at a loss. I am trying to pair data based on two parameters and then return an identifying number for further connectors.

1. the account id matches

2. the smallest number of days between open date and close date.

TABLE 1

oppID acctID closeDate

abc12 12345 1/17/2019

abc00 12345 11/05/2018

TABLE 2

acctID openDate oppID

12345 1/08/2019

12345 11/02/2018

12345 11/02/2018

A new column “oppID” needs to be added to TABLE 2. “abc00” (from TABLE 1) should be applied to the two rows with an openDate of 11/02/2018 since the acctID and the closeDate has the smallest days different.

The two Tables could have upwards of 20k-60k rows.

I can use MATCH(MIN(ABS(TABLE1[closeDate]-[@[openDate]])), ABS(TABLE1[closeDate]-[@[openDate]]), 0) within an INDEX on TABLE 2 oppID column, but this will return any date that is closest to the current rows openDate.

I want to drill down and find all rows where the acctID matches and then find the smallest days different.

Would I be on the right track using AGGREGATE in my formula somewhere, or should I go back to the drawing board?

This tutorial is phenomenal!

Robin HallJuly 10, 2019 at 3:15 pmLeila,

Thanks very much for checking this for me.

Rob.

VishalJuly 24, 2019 at 2:59 pmHi Leila,

This was such a great tutorial and I have watched the video also which was very intuitive and easy to follow. In the example that you provided, how would you eliminate the issue of duplicates in the field (Apps) that you want to extract i.e. what if there are multiple Fightrr in the Apps column listed? I’d be grateful for the solution.

Vishal

Leila GharaniJuly 25, 2019 at 6:46 amThis is much easier to do with the new dynamic array functions (coming to Excel for Office 365). With the old way, you’d have to combine this technique to the solution. For large data sets this will also become slow. Another option would be to use Get & Transform from the data tab or a Pivot table.

JunaidAugust 18, 2019 at 10:13 amHi Leila,

How to pick one entry from the Apps category as Fightrr appears twice.

Division Apps

Game Fightrr

Utility Accord

Game Fightrr

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

Thank you.

MuhaiminSeptember 3, 2019 at 4:45 pmHi. Leila Ghaharani.

Before asking, I say I love you, I’m really lucky to find your channel. and help the crowd.

I thank you very much. wish you a long life.

Jit NagarSeptember 13, 2019 at 5:34 pmI love this formula especially the Index with Aggregate nested in. It’s absolutely amazing and the explanation with the video is superb. Thank you so so much.

MuhammadNovember 6, 2019 at 12:34 pmthat is why i love you, nobody answered this question for me in the past, multiple match, i am using text join now

George LongmanNovember 17, 2019 at 3:42 pmHi Leila,

Loved the content of your site with full explanations, and wondered if you can address another problem as follows:

I have a lookup table which relates individual Product Code to sales Category. Each Category is comprised of several Product Codes, according to our own criteria. The converse is not true – can’t have one Product code across several Categories.

We get in a contractors report, which lists the value of sales against each of the Product Codes sold in a given month. The report doesn’t contain all of the possible Product Codes, as most rows would be blank.

Nor does monthly report contain our Category. I’m looking for a formula to sum up the value of sales for a given month according to our Category definition, using our own lookup table. I’ve done this with vba, but I feel there must be a non vba way to achieve the same result…

By comparison with your example – it’s a formula to get the value of sales in the “Games” category from a sales report which only lists the sales values against particular individual Apps.

Will be very happy if you can present a solution for this – I expect it would be quite a popular solution for many others too.

Best regards,

George, Edinburgh, Scotland

ChrisDecember 6, 2019 at 10:14 amHi George,

With formulas you need to bring the category – so games in the example – to your main table. You can do this with VLOOKUP. Once it’s there, you can use SUMIFS to get the total of Games. If you’d like to keep the tables separate, you could use Get & Transform from the data tab. You can upload the individual tables – connect them together and then Group the results by the categories. If you like, feel free to send us a sample file to our email and we can consider it for a future video.

JanieNovember 21, 2019 at 12:13 amHi Leila. I’ve recently discovered your chart and dashboard courses and they are awesome. I am going through your blogs and found this one and realised I have been using a different solution to return multiple matches using INDEX & SMALL. In the example above I would have used this function:

=IFERROR(INDEX($B$5:$B$14,SMALL(IF($A$5:$A$14=$F$4,ROW($A$5:$A$14)),ROW(1:1))-4,1),””) – it needs CSE.

Anyway just thought I would include it here as an alternative. Thank you so much for all your Excel tutorials and videos :-)

DeeDecember 2, 2019 at 1:55 amThank you for this. I was able to use Method 1 to accomplish what I needed. This saved me a ton on time!

JohnDecember 7, 2019 at 12:24 pmThe ‘legacy’ formula can be shortened by substituting the LARGE() function, for the SMALL() function within AGGREGATE():

=IF(ROW(A1)<=$F$4,INDEX($B$5:$B$14,AGGREGATE(14,4,($A$5:$A$14=$G$4)*(ROW($A$5:$A$14)-4),$F$4+1-ROW(A1))),"")

– the calculation to generate errors can thus be avoided and, with no need to ignore anything now, the AGGREGATE function should (in theory) calculate more quickly.