Return Multiple Match Results in Excel (2 methods)
Articles Blog

Return Multiple Match Results in Excel (2 methods)

August 11, 2019

In today’s video, I’m going to show you two
methods that help you find multiple match results in Excel. I have a VLOOKUP Basics video and an INDEX
& MATCH Basics video. This is a question that I often get, “How
can we use VLOOKUP or how can we use INDEX & MATCH to return multiple match results?” Let me show you. Method number one is a bit more complex, but
it doesn’t require CSE, so you don’t need to do this control+shift+enter, and it works
for any Excel version. Method number two is a much simpler formula,
but it does require that you have Excel 2016 and Office 365, and it does require CSE. So, just pick the one that fits for you best. This is a sample dataset. It’s the same dataset that I used in my INDEX
& MATCH video, and because a lot of you asked, “How do we find multiple matches? For example, if we select game, how do we
find Fightrr, Hackrr, Kryptis, and Perino in here?” Let’s start with method number one. First thing I’m going to do is to type in
the divisions. I’m going to type in, “Game.” Now, if you do want to have a dropdown list
here, a list of unique values in division, I have a video on that, and I’m going to link
to it in the description below, but to keep this simple, I’m just going to type in the
division. Now, what I want to happen is I get Fightrr
pop up in this cell, then below it, I want to have Hackrr, Kryptis, and right here, I
want to have Perino. Now, the usual INDEX & MATCH or the usual
VLOOKUP, they’re always going to find the first occurrence, right? So that’s going to work fine with my first
cell, but when I drag that formula down, I want the second occurrence, and then the third
occurrence. Here, I’m going to use index, together with
the aggregate function. Let’s start out with index first. We need the array, and the array is basically
the area where your answer is, right? So it only cares about the answer area and
not anything else. If you’re not 100% sure how index works, check
out my INDEX & MATCH video. Link to that is also below. If the array is answer area, what range
is that here? You want the list of apps, right? So it’s only this. I’m going to press F4, because I want to fix
it, since I’m planning to pull this formula down to get the second occurrence, and so
on. Now, the next argument is the row number. Inside this range, which row do we want returned
to get the app that we need? It would be number three, right? So one, two, three, that’s where we see game,
but if I pull this down, I need that three to become a four, and then I need that four
to become a five, but then when I come here, I don’t want it to become a six. I need it to become a seven, right? Because Perino is the seventh position in
here. And if you were looking for the first match,
we’d be using the match function in here to get that. But now we can’t do that, so instead, we’re
going to use the aggregate function to give us these numbers. I’m going to do the aggregate function here. Once we get it to work, we can plug it in
the index formula. Now, the good thing about the aggregate function
is that it is a pre-programmed CSE formula, so just like index, you don’t need to push
control+shift+enter, and we get to use these functions, like small, in there. So if we used small without aggregate, we
do need to press control+shift+enter, but because we’re using it as a part of the aggregate
function, we don’t need to do that. The next one is one of the best traits of
the aggregate function. You can ignore stuff. For example, you can ignore error values,
which is something that we’re going to need, and you’re going to see why in a second. The next option is the actual array that we
want. Normally, you give the small function numbers,
right? So I would highlight a bunch of numbers and
then say I want the first smallest, or the second smallest. That would be the K argument, but I don’t
have numbers. I have a list of apps. I want the list of app positions, so my numbers
are actually my positions here, which means I need to translate these to positions, and
then only take into account the positions that have game for division, so basically,
position three, four, five, and then position seven, and ignore everything else. How do we turn these into positions? We do a logical test on them. I’m going to open bracket, because whenever
I’m doing a logical test and I have the equals sign there, I put it inside its own brackets,
and my first logical test is if this area … I’m going to press F4 to fix it. If that equals this, and press F4 to fix that,
close the bracket. Now, I’m just going to show you what that
gives, so let’s highlight this and press F9, and you can see here, false, false, true,
true, right? Because these are false. It’s not equal to game. These are true. Okay, so we’re on the right track. What we ultimately need is to get rid of these
false values, because in Excel, false is zero and true is a one, and if we’re looking for
the smallest value, those zeros are going to get in the way, right? So we want to kick them out. So somehow, we want these false values to
give us an error, and the true values, instead of them all being the same one, we want them
to give the position, you know, what we needed before. So we want the first true to be number three,
and then number four, five. That should be an error, and then number seven. As a first step, let’s get rid of those false
values. I’m just going to press control+Z to go back. To get rid of them, I’m going to divide that
with itself. What happens when I divide this with itself? Let’s just take a look, press F9. I get an error, right? Because I’m dividing false by a false, which
means zero by a zero. For these ones, I’m dividing true with a true,
which is one divided by one. Now, what I need to do is turn this one, one,
one to the position it’s in the range. In this case, this is position number three,
then this should be number four, and so on. And here, I can use the row function. I’m going to multiply this with the row they’re
sitting on, fix this, but now I need to make an adjustment, because this is row number
five. I want this range to start reading from one,
which means I need to deduct the row of the header here, and I’m going to fix that. And actually, I’m just going to put these
in brackets. So, let’s see if that gives us what we need. Error, error, three, four, five, error, seven,
right? That’s exactly what we need. Now comes the last bit. When we’re on the first cell, we want the
small function to strip out the first smallest value, which is number three, but when we
pull this down, we want the small function to strip out the second smallest value. So if I just quickly fix this to one here,
I get three. I get the third position. This is what I needed here. But when I bring this down, I want this to
become a two, because I need to strip out number four. And for this, you can use index numbers. You can type in one, two, three, four, and
reference this cell, right? So then, you know this is the first match,
second match, and so on, or you can use a formula to do this. A great formula that gets this done is either
the row or the rows function. So here, I’m going to use the rows, which
means how many rows are highlighted inside a range. So, if I do this row with itself, this is
just one row, but if I fix the first cell reference by pressing F4, don’t fix the last
one, what happens to this? The row stretches. Now, there are two rows in there, right? So that’s how I’m getting my one, two, threes
dynamically in there. Let’s see if it works here. This one gives us a seven. We skip the six, which is perfect. So I’m going to copy this, paste it for the
row argument of index. Now I’m going to pull this down. We get our four matches, and then we get a
bunch of errors, because it doesn’t find anything. The laziest way to get rid of this error is
to use the IFERROR function. We can just wrap it around IFERROR, and then
pull this down, but a better way is to use the if statement. Now, why is that better? The problem with IFERROR is that it runs this
whole formula, and then it checks is it an error or not. The advantage of just using the if statement
is you don’t run this formula if the condition doesn’t happen, right? So you only run it if that condition is true. Now, what is the condition that we’re checking
for? We’re going to get the count of game. We’re going to see how many game divisions
we have repeated in here, and then check how many have we already found. If we haven’t found enough, we can run this
formula. If we’ve already found all of them, just don’t
run the formula at all. For that, we can use a helper cell and do
a COUNTIF. First argument is the range. That’s this one. Let’s fix it with F4, and the second argument
is our division, which is this one, and I’m going to fix it. Actually, I don’t really need to fix it, because
it’s just one cell. I’m not planning to drag this down. Now what I’m going to do is check against
this, so if my rows value, here … I’m going to copy this. If this value is less than or equal to this
one, now I have to fix this, then it should run my formula, okay? So let me just press F2 so that we can do
it here. Otherwise, it should give me nothing back,
right? And the reason we write rows here is basically
we’re checking how many have we found yet. We know that the moment we pass the fourth
row here, we’re going to be getting errors because we don’t have more than four games
in there. Let’s drag this down to here. Okay, so obviously you have to drag your formula
down to more cells than you have answers, so in case you get another game division,
it’s going to pop up in here. Actually, let’s test that. Let’s change the last one to game, and we
see WenCaL pop up in here. Now, let’s just do control+Z to go back, and
I’m going to change this to utility, and we see everything changed automatically. Okay, so this required a longer formula, but
it didn’t require CSE, and it works on all Excel versions. Now, the second version to get this done gives
you the answer in one cell, so basically, we’re going to get these three in one cell,
and for this one, I have to give a shout-out to Bill [Sizzes 00:11:24], because he mentioned
that formula in one of my videos, and I made a video on that. I’m going to show you, quickly here, it can
come in handy, especially if you have the latest version of Excel, and it’s a super,
super simple formula. All you have to do is to use TEXTJOIN. First argument is the delimiter that you want. So remember, I said it gives you all the answers
in one cell, so we need to split that with a delimiter. Next argument is if we want to ignore empty
cells or include empty cells. So let’s say we decide to include empty cells,
okay? And then we can see if we need to adjust that
later. Now, what does this TEXTJOIN do is that you
can give it different cells, so let’s say I would highlight this, and I close the bracket,
press enter. It gives me these three in one cell. Now, how can we use this to get back the list
of apps that match this? All we have to do is use an if function here. So, if the values in this range equal to this,
then we want the values in this range, and I’m not fixing anything right here, because
I’m not planning to pull the formula down. I’m getting all the answers in one cell. But wait, we’re not done yet. We need to say what we want it to do if the
value is false, and we want nothing. Check this out. I’m going to highlight this, press F9. Accord, Misty Wash, and Twenty20, and all
the other ones are empty. Just press control+Z to go back. Now, because we’re using the if function in
an array format, we’re giving it so many cells to check, we need to use control+shift+enter,
all right? So if I close this and I press enter, I get
a list of everything. I don’t want that, control+shift+enter, and
I get this. But notice those empty ones. I’m going to get rid of them, right? That’s this argument right here. Instead of false, we’re going to go with true,
and don’t forget to press control+shift+enter, and we just see what we need to see. So let’s change this to game, change it to
productivity, and we see this. So these were the two methods. Yes, the first one is more complex to write,
but the good thing is, I have the workbook available, so just use the link below this
video, download the workbook, play around with the formulas. If you like this video, if you found it useful,
do give it a thumbs up, and if you haven’t subscribed to this channel and you like what
you see, you want to become more advanced in Excel, consider subscribing.

Only registered users can comment.

  1. Thank you! It is really useful! For more advance, how can i find the result if the word in the yellow box is not complete but i want the same result be shown? Like if I enter "Gam" or "ame", the same results will be given out. How can I deal with it? Comment From Hong Kong =D

  2. Wow – thanks for the tips. I never really knew that IFERROR slowed down large data formulas – I'll be switching over to the IF now. Oh and this video just made my job much easier – Thanks again!

  3. This is truly impressive. I've only watched a couple of your videos and I have for long time now considered myself an advanced Excel user. Yet, each time I watch one of your videos I'm left in awe. Phenomenal job!

  4. i need help
    if a cell with multiple number like (1,2,3,4,5) and another sheeet with 5 cells to 1 to 5 so how i match cell (1,2,3,4,5) with second sheet

  5. Hello Leila thanks for explaining a complex function like this in a very simple way I have become huge fan of Index and Match function now, but recently I was having problem in using the same as my data is very large and contains multiple duplicate data value please upload a solution video for same.

  6. Thanks a ton Leila for explaining such complex formula in such a simple and easy way. This one really helped me out. Also can you please make a video on how to use or any other alternate option for the "textjoin" formula if we dont have the latest version of excel.

    Once agin thank you very much for this video.

  7. Great video! I'm not nearly an advanced user, so I am wondering what the applications of this method are. For most of my uses a pivot table or filter can get me the results needed there, so in what situation would this be preferable? Please forgive my ignorance

  8. Thank you for this solution. I have used the Index+Aggregate in multiple spreadsheets and it works beautifully. I now have a more complex problem. In your example you have data in A:C, using this scenario, how would I pull these results, excluding a result based on a value in Column D?

  9. I want to match with you on a dating site so I can match my DNA with you? Can you advise on best way to do this?

  10. Hey Leilah,

    Many thanks for the great video!
    One more way to go with array formula instead of using the aggregate function is that:

  11. The second formula was easier to digest ?, while the first one was intimidating..I am a regular follower of your tutorials…this one seemed to be the most challenging…keep updating…

  12. Hi Leila,
    Love your tutorial first of all. I have a question on returning a X customer's Y percentage based on their Z spend amount.
    Ie: I have a list of 5000 customers, Various min and max spend and its corresponding discount. For customer John, i can locate John by index match among the list of customers. Then when at customer John, John has 3 different discount structure based on John's spend ($0-$1000 returns 10%, $1001 to $2000 returns 20%, $2001 to $3000 returns 30%). John has spend $1500, therefore the output I am after is 20%. How can I write this formula?
    I have tried using SUMPRODUCT(Customer array 5000 = John)*(Spend1500=> Min amount)*(spend1500=<Max amount)*(discount array)
    if you could kindly shed some light Leila, it would be much appreciated thanks.

  13. Great video as usual. However I am running into a problem. My formula is as follows; =textjoin(",",FALSE,IF(A5:A14=G4,B5:B14,"")) . Which is inline with what you have in your video; however, I am receiving a name error. What could be causing this?

  14. 3:48 Aggregate(Small) is used and not =Aggregate(Large); because a list starts with Small values (Eg. 1,2,3…)

    Aggregate(Small) will output: Fightrr, Hackrr, Kryptis, Perino
    Aggregate(Large) will output: Perino, Kryptis, Hackrr, Fightrr

  15. Hi, Have been looking for a spreadsheet balance calculator (multiple sheets/balance for each as input, is shown) & now achieved with great help of video, learnt the index and match functions, took me 2 days, however have arrived and prodominently because of this video, thanks everso much, you have been a fantastic help, fully recommended!!!!

  16. Very helpful video and I reference it every so often to help with this issue. If I have duplicates that I want to exclude is there a good way to do that?

  17. I really have to congratulate you on your videos! I've seen a few and all are very well explained. Even as an advanced Excel user, i've learned excelent tips from you! I've had to do something similar to what you did in this video, using VBA, and your approach with just formulas is really great! Keep it up!

  18. Amazing tutorial, really very well explained. A huge thank you! was exactly what I was looking for.

  19. Hi Leila,
    Excellent work, really need it.
    I have made some adjustments for vertical and horizontal returns

    a. Vertical Return Multiple Match Results w/o the Helper Column: "=IF(ROWS($G$5:G5)<=COUNTIF($A$5:$A$15;$G$4);INDEX(TableDiv[Apps];AGGREGATE(15;3;(TableDiv[Division]=$G$4)/(TableDiv[Division]=$G$4)*(ROW(TableDiv[Division])-ROW(TableDiv[[#Headers];[Division]]));ROWS($G$5:G5)));"")".

    b. Horizontal Return Multiple Match Results w/o the Helper Column: Copy to Cell H5: "=IF(COLUMNS($H$5:H5)<=COUNTIF($A$5:$A$15;$G$5);INDEX($B$5:$B$15;AGGREGATE(15;3;($A$5:$A$15=$G$5)/($A$5:$A$15=$G$5)*(ROW($A$5:$A$15)-ROW($A$4));COLUMNS($H$5:H5)));"")".

    Thank you again,

    Christos (Chris) Papagiannis

  20. Ma'am i believe that array formula will slow down the calculation if the data is huge so thumps up for first formula?

  21. Dear Leila,
    Did things change in the past 9 months since the release of this video of yours? B/c I think the magical TEXTJOIN() function is not anymore available in MS Office 2016, but only in MS Office 365 and Office 2019. At least, the help page of the function on the website of MS OFFICE SUPPORT says so.

    I don't like the GUI of Office 365; yet I need to work with TEXTJOIN() occasionally. I was hoping Office 2016 would come as a remedy. Alas, they removed it from there?

  22. I am sure if I see your videos regularly so In a long run I definitely become an omneceant teacher.
    Thanks for your precious videos.
    Keep going on

  23. Can the aggregate – method be dragged sideways?…..
    For example I don't want to look game alone from the range, i want to look at more values

  24. Great video. I like to use aggregate. On that example. What if we had 2 criteria and want to create a list that is going to be 2 division names combined.
    If we can select game and utility and extract the names from both divisions in order. how would you modify the aggregate to check both selections?
    Thank you.

  25. Is there a way to search for a name within a column and then, assuming that names appears more than once, have the formula also add together the numbers (from a different column) in the rows where that name is present. Lets say A1 to 10 is populated with names and Joe Bloggs comes up three times, but i want the formula to find each instance of Joe Bloggs within A1 to A10 and then instead add together the corresponding rows within column D – is this possible with Index and match?

  26. Dear Leila,
    Can u help me
    I need to prepare chart for the below data.
    I have many warehouse names each warehouse will have multiple products such as hand towel, bedsheets, face towel etc. Aslo I need to show data for each product in graph. For example in warehouse one how many hand towel was there and no of washes for this product. To be very clear if there are 100 hand towel 50 towel may be of 40 washes and 10 towels have gone for &20 washes and remaining is fresh. I need this in graph. Pls help

  27. Very helpful. Can i use this equation if i want to search with a partial string match? i.e. if cell G4 = "Ga" , can i still get all of the "Game" information from column B?

  28. Hello! Is there a way to separate each matched value in Part 2 so that the apps returned are in separate cells in the same column? Can this column then be reference for a drop down box? Thanks!

  29. My table start at A1 and the search value is at G2. To run multiple match it is possible in column D to add the following formula : =IF(A2=$F$2,MAX($D$1:D1) + 1. and in G3 we use the following formula : =IF(ROW(A1)<=COUNTIF($A$2:$A$11,$G$2),INDEX($B$2:$B$11,MATCH(ROW(A1),$D$2:$D$11,0)),"")

  30. I love the explanation! Wouldn't a more efficient way of getting an error out of the array for the first formula be one devided by it instead of dividing by itself? Not in front of a computer to check but I think I remember that boolean values get translated implicitly when used in algebraic calculations.

  31. U shouldn't ask if video was useful coz ur videos r always useful for excel users… Awesome video as always!!

  32. A masterclass. You have a great teaching style. Clear, concise, personable and your cadence is just right. I've watched many Excel tutorials and you're up there with the best

  33. Hi Leila, can u pls reply me with the formula to return values in next , next column instead of next rows, Please urgent, I tried with other channels it is not clear

  34. Excellent! … AWESOME … you are awesome in every aspect …. Good Teacher … Cool appearance … Good experience …. everything is awesome. Overall sum up is EXCELLENT. Thank you.

  35. Thank you so much, i wasted more than a day trying to find out a solution which you showed in couple of min

  36. My spreadsheet has repeated values ​​in the first column and I can not delete it. I need my col_index_number to display only the largest value (Data)

  37. Another great tutorial, very well explained!!!!
    Is there any way to use the same formula for a partial match? I am using the wildcard asterisk in the target cell but it doesn't work.
    I want to search for productiv let's say instead of productivity.

  38. Thank very much indeed, it worked great using it for a large table. The only problem I have is with one of the resulting columns the text wrap will not work. If I drag down the column then it will adjust. However, in the end I’ll have the tabled locked so that won’t be an option. My formula is =IF(ROWS('Table 4 UN'!$AF$5:AF5)<=$A$3,INDEX('Items Listing'!$C$5:$C$4289,AGGREGATE(15,3,('Items Listing'!$B$5:$B$4289='Table 4 UN'!$B$3)/('Items Listing'!$B$5:$B$4289='Table 4 UN'!$B$3)*(ROW('Items Listing'!$B$5:$B$4289)-ROW('Items Listing'!$B$4)),ROWS('Table 4 UN'!$AF$5:AF5))),""). The specific column is C. Will I need to add something or is is something much more simpler? Thank you


  39. what if I have another condition and I only need 1 unique outcome. Means I just want utility – accord as result. Can we add another match formula on top of what you already have?

  40. lady, you're wicked smart.
    and it's super nice of you to slowly explain each step of the formula, gives viewers a chance to learn the logic and apply parts of it (creatively) in another problem/situation.
    very good

  41. very good video. further can we extract values by vlookup from a cell having already a formula of vlookup in it ?

  42. Thanks Great Turorial, i was trying this function to create a hierachy based on a EID + Qtr combination, however it does shows the employee reporting under same EID, Can you help me fix

  43. Thanks for such amazing knowleadge , can i use if or and if and with such combination to find mutiple values in one equation

  44. That's a great tutorial and helped me to solve one part of my job.
    Now i'm actually trying to use your formula in a slight different way and require some more help.
    Can you make a tutorial, whereby you use the search function in conjunction with your above method?
    Is it possible to show founded values from multiple excel files too?

  45. Hi Leila, do you have a video or tip where you could (using this example) tally the Revenues together for the 'Game' Division

  46. Division Apps 
    Productivity  Blend
    Utility  Accord 
    Game  Fightrr
    Utility  Misty Wash
    Game  Hackrr
    Productivity  Sleops
    Game  Kryptis
    Utility  Twenty20
    Productivity  WenCal
    Game  Perino

    Dear LEILA,
    I want to make a table like the one above from your table, but when i tried your formula , it gave me only the first cell correct and in other cells appears #NUM!.
    I work with big data and i need to resolve this problem , i need it a lot.

  47. Thanks a lot for your enjoyable teaching:)

    but I have a puzzle makes always wondering how can I solve it!
    I'll give you an example of the data:

    vegetables: tomato l potato l lemon

    if I want to make sure that the lemon exists in another sheet and the row of vegetables in the second sheet is different also the column of lemon is different but in the same row of vegetables.
    how can I find out if the lemon exists in the second sheet with a formula?

  48. Hi , I want to know the solution for " how to avoid zero as value if we add two or more blank cells?"

  49. First of all, let me thank you for the wonderful work you have done and for making such easy to understand tutorials.

    Having said that, I have a query on this video.

    How is the ROWS($F$5:F5) working (relevant) here?

    You mention that this is what will give us the number of rows…and it will return the value of the "rows highlighted in a range". But this range "F5:F5"is covering any range with any data connected to the table from on which we are working!

    So, how does Excel know which rows are to be considered and which are to be ignored!

    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *