Let's talk about a simple approach to get multiple match results in Excel. You have different options
depending on the situation and depending on the
version of Excel you have. So, if you have Excel for office 365, you can use the new filter function. If you don't have office 365, I have a video that shows
you two alternate approaches. One is with Excel TEXTJOIN function, which is in Excel 2019. And the other is with
the aggregate function, which works from Excel 2010. It was a one formula
approach, but to be honest, it's quite a complex formula
to remember how to write. Today I'd like to show
you a simple solution that lets you find multiple
matches from your data set. It's a two step approach, but it's two simple steps instead of one complicated step. This solution is from Bob Umlas. He sent it to me some time ago and I love the simplicity of it, but I didn't get chance to
get this to you earlier. The filter function was announced and I got super excited, but I realized that a lot of you are not going to have
Office 365 anytime soon. So, here's the approach
you can use instead. (upbeat music) Let me quickly give you some context. We have our mini dataset here, information and division,
apps and revenue. Notice, division is repeated. What we want to do is
type in a division name and get the results basically
the apps and revenue for all the occurrences of the division. This is Bob's solution. Take a look at the formula here. It's tiny. Notice, it's referencing a cell here. This is the helper column. The formula here is a bit longer, but it's not that scary. I'm going to take you through
how this works in a second. So, if you're interested, stay on. If not, feel free to
download this workbook and use it as you like. The link to the workbook and to the blog post is in
the description of the video. But just to let you know, I've also added two modified
versions of Bob's approach. So, just pick the one that
fits your dataset best. Bob's approach starts from the first cell and references columns. The modified version one, also
uses direct cell references but the formula starts
where your table starts or basically your dataset. The second modified version
uses an Excel table. So, the formulas are the same, but they just use table
referencing instead. The concept though, is all the same. And it all comes down to this formula. What this does is it returns
the different positions, the division is sitting on. So, notice the numbers here. These are the row numbers
wherever we have game. Because the moment we have the position, we can figure out the actual app formulas like index do that easily. So, let's go through this
and let me quickly explain how the helper column works here. To get the position, the great function is the MATCH function. Because we know MATCH returns a position. Lookup_value is our division here and I'm going to fix this because we want to pull the formula down so just press F4. The lookup array, why
are we looking it up? Well, we need to look
for it in the A column. But here's the problem, I
can't directly reference the A column like this or even like this. Why? Well, let's take a look. Let's just fill up the
last argument first. We need an exact match so
I'm going to put a zero, close the bracket, we get seven. Is this correct? It is, because the first instance of game is sitting in the seventh row. Now, what happens when I pull this down? It's not correct, right? why? Because my range is being pushed down and it also always finds
the first instance of game. So, based on this range,
it becomes the sixth. And then when I pull the range down again, it becomes the fifth. And I can't fix this either
because when I pull this down, I will always get seven back. So somehow, we need to
make this part dynamic. That's where the OFFSET function comes in. Because OFFSET is a great
function for dynamic ranges. So, let's start off with that, reference is our starting point. So, let's say we want to start
from the first cell here, I'm going to fix this. Next is how many rows
do we want to go down? Well, in the first case right now, I don't want to move down any rows. So, I'm just going to put
zero in there for now. Next is how many columns
do I want to move? I don't want to move any columns. So, you can put a zero or just
skip this argument height. How big should my range be? Well, I'm just going to put a big number in case I have new data added to this, that that data is included, okay? So, I'm just assuming that I
don't have other information on this sheet aside from this table. The last argument is the width. Well, I just want to get one column back. So, I can put a one or we
can just skip that argument and close the bracket. So now, we get seven. OFFSET is doing what our
direct cell references were doing before. If I pull this down, I'm
always going to get seven. There's nothing dynamic in here yet. So, now let's think about
how we can change this to get dynamic. To get a dynamic range, what we can do, is change the number of
rows we always go down. So, in the first instance, I
don't want to go down any rows because I want to get the
first occurrence of game. So, I want to start to look
for it from the first cell. But after I find the first one, I want to change the range I'm looking up and restrict it from the next cell. And then find the first
occurrence starting from the next cell. And then repeat that pattern. So, it's kind of like a moving range, the range always moves right
after the match was found. So, every time I pull this
down, that range moves with it. Well, to do that, I need
to always take a look at what it already found before. So, I can in the first instance,
just reference this cell because the cells empty,
so it will be zero. When I pull this down,
it still is a seven. Let's just leave the formula
now and see what we get. So, in the first instance,
we get back what we had, then when I pull this down,
I get different numbers. I get a one and a six. In theory, I want to
get an eight and a nine. But why is it giving me a one? Well, I have changed the
range that I'm looking in by bringing it down by seven. So, take a look at this, it's one, two, three,
four, five, six, seven. So, my range starts from here. The first occurrence of game
in that range is number one. That works, but I still need
to make a modification to this because instead of getting a
one, I want to get the eight. So, what I need to do here
is to add to the seven back to this. That means we need to
add right at the end, the value of the cell before. So, in the first instance is zero, so everything works great. Now when I pull this down,
I get the eight and nine. The proper row numbers here. Now if we pull this down further,
we're going to get errors. But we'll deal with these in a second. Let's just leave them up for now. The reason I want to pull this down, is to account for future data
and also different divisions. Because one division can have more matches than other divisions. So, we'll deal with this in a second. Let's go and do our index formula because right now everything got simple, index requires an array. Array is where our answer is, our answer is in app. Since our helper column starts
counting from the first cell, we need to do that here. So, you can either go from B 1 to B 1000 or just select the whole column. Now, the next argument is the row number. That's this. We don't need to move any columns, we just have one column. So, I'm going to close
this and press enter. And that's it, that's my answer. Now just bring this all the way down. And what about getting the revenue? Well, it's the same thing. I'm just going to pull this across and update this reference to this. I could have also done
the fixing properly, but it's a quick fix to
update it here as well. And these are my values. Now to account for these,
what Bob had done here was to use conditional
formatting to hide the errors. And then he used the IFERROR function to clean it up here. But you can use conditional formatting to hide the errors for
all of this as well, but I'll just stick to the way Bob had it in the original solution. And then I'll show you my
modifications on this side, highlight this range, go
to conditional formatting, new rule, the rule is simple,
we want to use the formula, the formula is NA,
basically is a DNA error. Which cells do we want to look at? We want to look at this, but
we don't want to fully fix it to this one single cell, we
want the row to be variable. So close the bracket. And if it's a TRUE so
basically, if it is an error, what do we want? We just want the text to be white and okay and okay, these disappear. Let's wrap this in the IFERROR function. IFERROR we want to see
nothing, close bracket, enter and send this down. And the same for here,
IFERROR show nothing. And that's it. Because the formulas
are a lot shorter here, this method is a lot easier to remember. Now let me quickly show
you my modified versions. Instead of using conditional formatting or the IFERROR function, I use the COUNTIF to figure out how many total occurrences of the division we have in the data set and then I use the function like this, so the rows function to
check if we've gone over or if we're still within our limit. If we are within the limit of this count, we're going to do the match function. And if not, we're just
going to show nothing. So, that concept here
applies to all three here. Now this other version also
starts to look at your table from where the table starts. So, in case you have
other information here, you don't want include
it, you can use this. But it does use direct cell
referencing not a table. So, the better way would actually be to turn your data set to a table because that way you don't
have to reference rows that you don't need. So, this is the table
approach of the same solution. Now this part becomes a bit longer because the table references
take a lot more space than direct cell references. But the formula is identical to before. Now in case you're interested, I've still kept the original solutions that did this in a one formula step and it didn't require Control+Shift+Enter. So, this is the solution
from the original video. This is the TEXTJOIN solution and I also have the
table version included. Then in addition, if you have office 365 and you have the new filter function, this is the easiest method. No helper columns are
needed, just a simple formula and you get both at the same time, you just have to write this, that's it. Okay, so different approaches in one place to create a report that
shows multiple match results. Pick the one that fits your
version and your dataset. So that's a super simple method to find multiple match results in Excel. Many thanks to Bob Umlas for sharing this. Hit that thumbs up if you
like the simplicity of it and feel free to download the
workbook from the description. If you're not subscribed to this channel, consider subscribing before you leave and I'm going to see
you in the next video. (upbeat music)
