HomeОбразованиеRelated VideosMore From: Leila Gharani

Return Multiple Match Results in Excel (2 methods)

1642 ratings | 58296 views
Quickly learn how to lookup one value in Excel and return multiple matches. VLOOKUP and INDEX & MATCH formulas always return the first match they find inside a data table. What if you wanted to return multiple matches? I'll show you two different methods: Method 1: Uses INDEX & AGGREGATE formulas. This doesn't require CSE (control shift enter) and it works on older Excel versions. Beginning: 1:31, End: 10:20 Method 2: Uses the relatively new TEXTJOIN formula (available in Excel 2016 Office 365) and requires CSE. Beginning: 11:40, End: 13:25 Pick the method you find easier to maintain. 🡻 Download the workbook here 🡻 https://www.xelplus.com/return-multiple-match-values-in-excel/ ★ Links to related videos: ★ VLOOKUP basics: https://youtu.be/E7gQ-PgYkMc INDEX MATCH basics: https://youtu.be/F264FpBDX28 Get Unique List: https://youtu.be/7fYlWeMQ6L8 TEXTJOIN: https://youtu.be/TMZEUlFGp1U ★★ My Online Excel Courses ★★ Courses ► https://courses.xelplus.com/ 🕮 Excel Resources I Recommend 🕮 https://www.xelplus.com/resources/ ✉ Subscribe & get my Excel top 10 tips and formulas e-book for free https://www.xelplus.com/free-ebook/
Html code for embedding videos on your blog
Text Comments (159)
Chip Turner (1 day ago)
Complex but very powerful! Keep up the good work!
Mauricio Hernandez (2 days ago)
you are better off using a query
frik steinmann (3 days ago)
Thanks! Worked like a charm!
Jamie Goodell (4 days ago)
Thank You!!!
Marks Man (4 days ago)
You and Mike are the two greatest teachers in Excel, thanks!
123789s1 (5 days ago)
BiLingual & Knows Excel = si senora , si.
Muhammad Adha Hashim (10 days ago)
Hi Miss Leila, is it possible to returns the value into horizontal lists instead of vertical lists?
Leila Gharani (9 days ago)
Check out this video: https://youtu.be/gu4xJWAIal8
Rogue Racing Club (14 days ago)
How is this done if the values are on separate sheets and tables and using an ID to look up the values? Account ID is used to return parts purchases (primary_lines table - Customer_Name, Customer_Account, Net_Sales, Line_Code, Line_Name) Want to display: Line_Name Line_Code Net_Sales based on the account ID that was selected and then change when a different account is selected I am truly stumped on this one. I cannot figure it out
Rogue Racing Club (13 days ago)
+Leila Gharani got it sorted out only thing i need to adjust is to return the top N values (top 20). index/aggregate and some other stuff that i would not have known. i will post the formula in the morning. i do appreciate your reply
Leila Gharani (13 days ago)
It might probably be easier to use PivotTables here....
Robert DeMilo (28 days ago)
This was helpful and I appreciate it, so thank you.
Hossein Marvi (29 days ago)
Thank you very much for your great videos, Leila. I've learned a lot since the time I found your channel. I've tried some formulas to extract unique values in order to make a dynamic drop-down list. They all work but the main problem that I haven't solved yet is the slow calculation of these formulas after the main dataset expands. This AGGREGATE formula in this video performs fast and great, but is there a way to change it in order to produce unique values only?
Richard VanOrden (1 month ago)
Thank you for this video. I have implemented it into my daily worksheets, and it has made an immediate improvement. I would like to use it for another project, but this one has a conditional factor. In your example, you have details in columns A-C, in my spreadsheet, if column D has a value I need to exclude the entire row. I have tried to add an =IF( at the head of the full IF/INDEX/AGGREGATE/ROW(S) function, but my solution will be more complex than that. Can you offer any suggestions to my conditional issue? Thank you again!
Amos Ng (1 month ago)
Hello Leila. G'day! I has been trying to look up from your tutorial to resolve my challenge. How do I extract Certain information from the the String which consist of text and numer. Example. I wanted to extract " GTX1070 " from the string HP OMEN 17-an061TX 17.3"" FHD - I7 7700HQ, 16G, 256G+2TB, GTX1070-8G, WIN10 Thanks.
Ed Gifford (1 month ago)
Excellent tutorial. Question: My master data structure has cells with web hyperlinks associated with them. When I retrieve specific multiple match records I want to retain the original hyperlink reference in the resulting report. Is this possible?
Ed Gifford (1 month ago)
+Leila Gharani The master file has numeric order number fields that have hyperlinks to the web that actually contains the contract. When I index match for a specific report, the numeric order number returns but without the original hyperlink. Can I pull the field attributes that include the original URL?
Leila Gharani (1 month ago)
How about using the hyperlink function? That should return them as hyperlinks....
Thanks for you
Rob Tag (1 month ago)
How could I get the results to go to the right instead of down?
Rob Tag (17 days ago)
Thank you.  That's great info but I came up with a different answer for this: =IFERROR(HYPERLINK("#"&CELL("address",IF(ROWS(XLA!$A$1:$A$1)<=XLA!$A$1,INDEX(DATA!$B$4:$B$18,AGGREGATE(15,3,(DATA!$A$4:$A$18=$A4)/(DATA!$A$4:$A$18=$A4)*(ROW(DATA!$A$4:$A$18)-ROW(DATA!$A$3)),ROWS(XLA!$A$1:$A$1))),"")),IF(ROWS(XLA!$A$1:$A$1)<=XLA!$A$1,INDEX(DATA!$B$4:$B$18,AGGREGATE(15,3,(DATA!$A$4:$A$18=$A4)/(DATA!$A$4:$A$18=$A4)*(ROW(DATA!$A$4:$A$18)-ROW(DATA!$A$3)),ROWS(XLA!$A$1:$A$1))),"")),"")I put in some work and fiddled with your formula from this video and included it into another formula from another source and made my own modifications.  I found that you have to manually change the ROWS statements manually but after doing this for as many columns as you want, you can drag it down and the function will replicate properly.
Leila Gharani (1 month ago)
Check out this video: https://youtu.be/gu4xJWAIal8
shivani reddy (1 month ago)
Hi, Will the first solution provided work if data is from an other sheet
Leila Gharani (1 month ago)
Another sheet (tab) : yes
Jaco Griesel (1 month ago)
Thank you very much, Leila! I'm an instant fan!
Tvrtko Štuka (1 month ago)
Works like a charm when used on the same sheet, but I keep having problems trying to use this in a separate Excel file, anyone got any pointers?
Jose Bernal (1 month ago)
With the textjoin how do I get it not to show duplicates?
Willian de Paula Gomes (1 month ago)
You're amazing teaching us. Congratulations!!! 😍
Jose Bernal (1 month ago)
Leila as always great video, I try to apply it to my file but my file has the table is A5: D23214, the index range is column D. I copy your formula and then adjusted the range the lookup row does goes as far as row 14 after that is not working, can you help?
Jose Bernal (1 month ago)
+Leila Gharani Leila yes I have, I did a little test, I copiedsome date to your file more than 14 rows and could not geti it to work beyond the 14th row
Leila Gharani (1 month ago)
Have you downloaded the file? It might be easier to cross check....The link is in the description of the video.
El Badlis (2 months ago)
Thank you for introduced me to AGGREGATE <3<3<3
Chandra Sharma (2 months ago)
this will help a lot to us.........thank you
sudheer nambiar (2 months ago)
sudheer nambiar (2 months ago)
Martin May (2 months ago)
Very clear - idea of helper cells will be useful.
insha cottex (2 months ago)
Hello Leila... Can u please help me how to get predetermined formula result in particular cell. for example if the value of 1 =260 and 2=520, 3=780 then if i enter 1,2 ,3,4 ans so on in cell it should get the result values in cells automatically.
Leila Gharani (2 months ago)
You can use Vlookup, Choose function, the basic INDEX match or IF function. You'll write your formulas in the cell beside the input field. So if a user inputs a 1 in A1, and you have your formula in B1, then B1 will give 260. Here is the video for Vlookup: https://youtu.be/hwL6KKJP-_I Choose: https://youtu.be/CIJbcBBH88M, Index match https://youtu.be/F264FpBDX28 and for IF https://youtu.be/KkTaQ5OjAGc
Md. Saiful Islam Tuku (2 months ago)
thanks From Bangladesh
Oluwa Hefner (3 months ago)
Thank you so much , love you so much. Fuck Excel what the hell , so much madness
mostafa4321 (3 months ago)
Had to watch it twice to wrap my head around it. Excellent as usual.
Janno Duque (3 months ago)
Hi i have a question. I have 3 criteria per data (=, = and =>) and i have like 20 data. These data should be match with an array. how can I show all the matched in one cell? because if I use index + match I only get the first match.
Leila Gharani (3 months ago)
Hi Janno, TextJoin will give you the answer in one cell (I show it towards the end of this video)
Kailash Rajput (3 months ago)
Very Nice Video!
Pradeep Somani (3 months ago)
hello Leila .. i have a data sheet A with two options ( master and alias ) which has to be imported ( in accounting software ) which has data with two options ( mater and alias ) . I need to figure out what does not match .. what tool should i use .....
redfeather22sa (3 months ago)
Leila, thank you. very helpful. But how did you effortlessly get the Function Arguments to display in the cell (6:57, 6:58, 6:59)? Ultimately I want to find a way of keeping all the arguments viewable in the cells and parsing these to work from. Any ideas? I remember when I did it once for a mega array formula I had, it involved 2 steps, and use of Options, formulas, advanced in the File tab. It seems complicated to people, but I did it once & then used concatonate and Mid to extact these argument results. I just had to remember to untick the options I enabled or disabled to do achieve this, in order to use excel normally again.
RyGuyFlyGuy (4 months ago)
Hello Leila, I opened up your workbook example and noticed your "More -->" worksheet. I'm looking at purchasing one of your online courses and would like to know which would best apply to creating that kind of interactive worksheet. I'm thinking it's your VBA and Macro course? Thanks!
Leila Gharani (4 months ago)
Hi Ryan - I'd only recommend the VBA course, if you're already advanced in Excel. There is a lot of interactivity you can get with normal Excel functions and features a lot of which I show in the Dashboard course. Once you run into limitations with the usual functionality then it's good to turn to VBA. Thank you for your support.
Nitin C (4 months ago)
Your teaching process is exultant,Impress,awesome.
Nitin C (4 months ago)
Do you have any video on Inventory,Please let me know.I have an Idea on Inventory,Please send your email id on [email protected]
Help what if .. I have multiple locations set ifor one item..it's in my stock report.. where in I have to cater both location as well as the quantity..the data dump is 17336 entry..
Leila Gharani (4 months ago)
Maybe this helps? https://youtu.be/ontXHp9cwOQ As an alternative, you can use a pivot table - which is probably easier to setup.
RyGuyFlyGuy (4 months ago)
You just helped me help my boss big time!!!!
Leila Gharani (4 months ago)
That's GREAT!
RyGuyFlyGuy (4 months ago)
You are brilliant
Mistrz Juliusz (4 months ago)
my head blows
Shirish Shelke (4 months ago)
Thanks ! Mam. Something learning from your tutorials ! Thanks. I'm teaching excel from your tutorials. Pl told me how can i count division row in this tutorial (my ans. is 6), how can i found it, if any tutorial for this pl send link,
Leila Gharani (4 months ago)
Do you mean the number of unique divisions? If yes, then it's in this video: https://youtu.be/7fYlWeMQ6L8
Arindam Chattopadhyaya (4 months ago)
you have brilliant skill of teaching
Nitesh Bhatt (5 months ago)
hie ma'am your explanation best
Chandan Somani (5 months ago)
Super Explanation.....
CA Vivek Gupta (5 months ago)
Hii leila. thnxx for the video.. could u help me with this.. i have 2 columns each containing 6 items one below the other.. i want to create a drop down list of all 12 items together.. how to do? don't wanna write all 12 items in another column... is there a formula to combine both the columns items to form one list for drop down..? comma separator doesn't work in data validation list for arrays. Thanks.
Brigh N Quisitive (5 months ago)
who is giving these presentations thumbs down? Thanks Leila
KAMAL SANWAL (5 months ago)
Hi Leila. Thanks for the video. Moreover, I am using Excel 2016 and their is no textjoin function available. Is it hidden somewhere? How to activate (get) it?
Leila Gharani (5 months ago)
Hi Kamal - it's in the office 365 subscription version of 2016.
Ammar Amoum (5 months ago)
Hi Leila, Please if you can help me with that,how I can use excel to distribute data (invoice #,quantities and customer location) to multiple options(trucks capacity) within multiple conditions (quantities and locations)
Theo Tran (5 months ago)
First part waste of time, second part, great
Ashish Jangid (5 months ago)
Thanks Leila :) Very good fnction Jointext Plz help me if we can get only unique values a result if there are duplicates in list.
Leila Gharani (4 months ago)
I think you'll find your answer here: https://youtu.be/7fYlWeMQ6L8
Suleman Abbas (5 months ago)
Try this channel https://www.youtube.com/user/y5jeyfuyf/videos?view_as=subscriber
Dance Virginia (5 months ago)
Best “how to” videos on Excel on the web!
Agent Orange (5 months ago)
forrest gump (5 months ago)
it is nice to be able to do that with excel functions, but I'd rather do it with VBA, and trigger the code on the G4 change event
Adi Calin (5 months ago)
Great video. I like the first method.
Excel para Estagiários (5 months ago)
Hi, I loved your video, I'm from Brazil and have a small channel where I teach a few tricks on Excel. Would be a pleasure to me if we could chat same day. Best Reagrds
Jonas Sami (5 months ago)
hhhh chat :)
Leila Gharani (5 months ago)
You have a great channel! Unfortunately my Portuguese is non-existent. Thank you for your comment & support.
Fahad Hassan (5 months ago)
Awesome all the time.
John Borg (5 months ago)
Loved it!!!! Thanks Leila
M. MUSTAFA BOLAT (5 months ago)
Thank you for sharing this. We can make it also with array row method. But sometimes i use this method too.
David Hansen (5 months ago)
I liked the use of the if function instead of iferror.
Joe from DC (5 months ago)
Power query version pls Question: do u have a method to resolving various problems you wish to share? I am trying to get better at resolving logic questions with in my code writing
Prakash Chandra (5 months ago)
Excellent. However, I would just use filter and copy the visible cells and paste . Any way thanks a lot.
sarfaraj kureshi (5 months ago)
Hello Mam nice video, How I can count same value in excel sheet
Vida (5 months ago)
Really amazing video and nice logic in formulas
Bhupesh pal (5 months ago)
Greetings , Pls tell me if vlookup is possible for shapes in excel.
Leila Gharani (4 months ago)
Hi - this video should help: https://youtu.be/wlW2UKml9CY
Dawood Mohammed (5 months ago)
Very useful video. You make Excel look so simple and helpful.. great work. Many thanks!
MIS Dev Bartwal (5 months ago)
LG you are genius 💕
Wa Wai Chuang (5 months ago)
Great MS Excel skills. Can you show in another video with the same example, where we can have MS Excel return multiple match results one followed by another given 2 or 3 divisions are selected? For example, if we select utility and productivity for division, MS Excel can return its corresponding apps in sequential order, i.e. Accord, Misty Wash, Twenty20, Blend, Sleops, WenCaL in this exact sequence. Thank you very much!
ARUN ARK (5 months ago)
Veryyyyy nice Leila.....👍👍👍👍💐💐💐💐
Raj Shekhar Singh (5 months ago)
Mam, I liked your approach. Please explain how to make list out of the second method using text join because in the current state it is of no use. Thanks
Lucy Ene (5 months ago)
Many many thanks!!!!!😘😘😘
Baci (5 months ago)
What if I only need the games where the revenue is higher than 10.000? Thank you!
ckokse (5 months ago)
Very useful, I can already think of several files I can apply that new knowledge at work. Thanks!
Leila Gharani (5 months ago)
That's great! I'm glad to hear that :)
Sandip Mukherjee (5 months ago)
Very useful and informative video. Thank you. Power Query option also works great. After importing the range into Power Query, we can Group By using "Division". Thereafter adding a custom column using Table.Column function which will create a list of "Apps" column values. Then we can extract those values either through "Expand to new rows" or "Extract values" using a custom delimiter in the same row.
Naren Jain (5 months ago)
We can use advance filter for this task but by the method you explained here it would be dynamic. Thank you for sharing.
Arul Thangavelu (5 months ago)
Explained clearly - Really admire your skill in excel and simple demonstration.
Fabrício Luiz (5 months ago)
Great video! Now, just wondering, if you had used the double unary operator instead of dividing that array by itself, would the AGGREGATE formula still have worked?
Rico S (5 months ago)
I think that would have returned Zero rather than error and so the "Ignore errors" part of the formula would have not triggered to remove those items. See my response in the comments, I think that 1/(array) is a nicer syntax where the divide by zero triggers the error clause.
Avinash Verma (5 months ago)
Thanx ma'am, Its very very usefull for me.I was googling.
aguerojg (5 months ago)
What a fantastic video, thank you very much Leila for your clear explanation, you are doing a great job!
Yulin Liu (5 months ago)
Excellent as always! Thanks Leila!
Doug H (5 months ago)
Great example, thanks for the video!
Auris Z (5 months ago)
Super like 👌
Rico S (5 months ago)
Okay, I have another one.... without using CSE, I've managed to get the TEXTJOIN to work, with only one small adjustment to the worksheet. If you enter a table row number into column D beginning 1 on row 5 through to 10 on row 14, then use: =TEXTJOIN(",",TRUE,INDEX($B$5:$B$14,AGGREGATE(15,3,1/(($A$5:$A$14=$G$4))*(ROW($A$5:$A$14)-ROW($A$4)),N(IF({1},INDIRECT("D5:D"&4+COUNTIF($A$5:$A$14,$G$4))))))) Then you don't have to use CSE. I admit, that's quite a length to go to, but it's sort of almost worth it. It incorporates your formula for returning a single value, but then uses the index function to return an array (which index can do, which is cool) rather than the "k" value and uses an array of number 1 through to x (our row numbers in column D) to refer back to the position of the items in that array (for example, position 1 is 3 if my division is "Game" and so on. If I'd had a little more time, I'd have found some way of writing 1,2,3,4 (in the case of Game, which has 4 instances) in sequence without using CSE or typing the values into column D. Maybe. That would be the utopia of geekness.
Rico S (5 months ago)
Well, I was never going to sleep.... For a non-CSE formula without adding a column of table row numbers, here we have it: =TEXTJOIN(",",TRUE,INDEX($B$5:$B$14,AGGREGATE(15,3,1/(($A$5:$A$14=$G$4))*(ROW($A$5:$A$14)-ROW($A$4)),N(IF({1},INDEX(ROW(INDIRECT("1:"&COUNTIF($A$5:$A$14,$G$4))),,1)))))) I just replaced the previous row numbers in column D with ROW(INDIRECT("1:x") where x is a count of the number of "Game/Utility etc". Then instead of returning a ROW in the Index formula I left this blank to return the entire COLUMN with the 1 at the end of the formula. Effectively the second Index is returning the array {1,2,3...,x) where x is the countif. It all stems for INDEX being able to return an array as well as accept an array input. I'm going to bed.
Tyler Roenicke (5 months ago)
You do such an excellent job giving a higher-level explanation while reviewing basic concepts. I really enjoy it.
K N (5 months ago)
Senthil (5 months ago)
Hi Leila, Gud one.
Bart Titulaer (5 months ago)
Hello Leila. Great video. Excellent explained. John Walkenbach and mr Excel would be proud on your “mega formula”! I prefer the second solution but can you return the result in multiple cells without power query? For less advanced users I would however prefer another solution: add a help column with: =IF(cell=cell-tobe-found, row() , “ “). Then continue with your solution to use SMALL to collect all row numbers.
Rico S (5 months ago)
Nice use of aggregate. Not on excel at the moment but could you have used 1/($A$5:$A$14=$G$4) as opposed to ($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4) to shorten it a little? Assuming it would return the same error. I have a requirement to remove blank rows from a list of addresses. For example Address Line 3 is blank for some customers but not for others. I'm sure I could utilise something like this without having to use an array. Thanks!
Arulmozhi Arumugham (5 months ago)
Nice to learn TEXTJOIN function...I am using Index functions a lot...but the performance is abysmal...could you suggest how we can improve performance using many functions in the excel workbook?
Luciano (5 months ago)
Tutorial bem explicado, o método dois é menos complicado, bons exemplos. Obrigado Leila.
Shivaji Kumbhar (5 months ago)
Thanks, for this video, it is very helpful for me
Dashrath Panchal (5 months ago)
Hey Leila ! as usual you rocks with this vedio, brilliant trick..Keep sharing
Peter Ng'ang'a (5 months ago)
You never cease to amaze. Great job again. Thanks.
krn14242 (5 months ago)
Great approach. Thanks Leila.
José Prieto (5 months ago)
Great!!! Leila, Thank you so much for sharing your knowledge and expertise.
Leila Gharani (5 months ago)
You're very welcome Jose. Thank you for watching.
Abhi Redmi (5 months ago)
Hi Leila, your video is very clear and Good. Great.
Mokhtar Saeed (5 months ago)
Nice work but I am sure there is easyer way to get the list like using power qowery or pivot table
Leila Gharani (5 months ago)
yes - also filter :) This way they're dynamic without refreshing.
arandomusername123 (5 months ago)
You are the best leila, thank you so much for all this!
Leila Gharani (5 months ago)
You're very welcome. Thank you for watching.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.