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.

How would you add the revenue column to the right beside each match with game?

Amazing work!!

Hello Leila, Can u do a video on huge data mearge

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

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!

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!

Can DGET be better? https://www.youtube.com/watch?v=ZIsxd__5Wyk

literally saved my life, used this application for capstone project and last minute fix worked perfectly!!!

why not use filter?

Good Information,,,,,

❤

i want to replace row when new entry (User ID) is same. how to do this

Great job Leila!!! Very nice, subtle and finesse approach.

amazing method of teaching

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

Excellent video!

how to use countif with nested if

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.

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.

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

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?

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?

Hey Leilah,

Many thanks for the great video!

One more way to go with array formula instead of using the aggregate function is that:

{=INDEX($B$5:$B$14,SMALL(IF($J$4=$A$5:$C$14,ROW($A$5:$C$14)),ROW(1:1))-Row($A$4))}

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…

This formula is so complicated but well explained!

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.

Calvin

Hi Leila, what if want to ignore duplicate values in second row. please guide it

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?

is there a method like this for excel 2007

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

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!!!!

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?

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!

quite an eye opener.

Excel is never the same to me again.

Mind boggling

Superrrr. I love it.

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

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

Absolutely Brilliantly explained! Thank you!

Dear Madam

I want to know keyboard shortcut for subtotal function.

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

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?

Dynamic tables are easier

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

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

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.

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?

Hi liela – Mind blowing videos your formulas & explains thanks alot ?

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

love you for giving us easy tutorials

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?

Wow, you're good. Thanks for the help.

Great video. Thank you! You're a great teacher and absolutely one of my favorites.

Thanks☺

Hi Leila, your video helps me so much.. thank you

You helped me a lot to get good at excel, thank you so much! wish all sucess for you!

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!

Thank u so much, well explained , no space for Doubts,

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)),"")

Been wondering for a long time if this could be done in excel with everyday formulas. Excellent!!!

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.

Why use small? Can you explain this?

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

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

CAN U HELP ME IN EXCEL PROBLEM

I HAVE A FILE IN WHICH I WANT SOME VALUE FROM ANOTHER SHEET

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

J

Amazing and brilliant. Thank you!

Simply Brilliant!!

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

nice video

Good Approach. Is there any better way to do the same?

how to summarize the datas of my parent drop down and its dependent drop down?

Great Effort but for me, I prefer to use the second method

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

you are sexy

Plz send the short cut froms to the excel

This is what I was looking for. Thank you so much ? ??

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)

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

productivlet's say instead of productivity.Im in love with you, call me please

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

Eugene

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?

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

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

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

HOT SPICY VIDEO LEILA

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

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?

Really useful… especially the first one..I wish u to get million of subscribers

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

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.

Regards.

Excellent video and explanation

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?

Thanks,

how do you do this with vba?

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

Very nice, thank you . But How can I exclude empty values

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.

Wow, My brain has melted, overload. Very Nice Tutorial, Great explaination.

Fantastic! Used the 2nd formula

I have a Mac; what is the equivalent of control + shift + enter?