In today's video, I'm going to show you how
you can look up values across multiple worksheets. And depending on the
complexity of your data set up in these tabs, you might
want to use VLOOKUP, or you might want to use INDEX and MATCH , or you might need a
completely different formula depending on your specific case, but the process that
you follow is the same. (upbeat hip-hop music) So imagine you have a summary tab and based on the point of
view on the summary tab, you want to retrieve
information either from tab A or from tab B. Under summary tab, we have
the name of each division, what we want to do is to
retrieve the invoiced amount from their own respective tab, based on the month that we select here. Now if you take a look at
the data set up in the tabs, we see it's very simple,
we just have dates here and the invoiced amount,
they have the same layout, except that they have the
numbers for their own division. So that's the Productivity one and last is the Utility Division. What we don't want to do
is to use an IF formula so that if this is Game
Div, then go look here, if it's Productivity look in here, why? Because if we happen to
add on more tabs to this, we have to revise our IF and it's going to get really
complex and really frustrating. So we want to come up
with a dynamic formula that we can just pull down, no matter how many new tab names we have, it's going to figure out where to go, based on the name that it sees here. I'm going to show you two
different versions of solving this, one version is going to use sheet names and direct cell references
and the second version is going to use tables. So I'm going to convert the data in these three different sheets to Excel tables and we're
going to use table names. On top of that, I'm going to
use two different formulas. So because we've been using
INDEX and MATCH quite a lot in the past videos, I'm
going to use VLOOKUP first and then when I show
you the table version, I'm going to use INDEX and MATCH . (upbeat hip-hop music) I'm going to approach this
the way I usually approach more complex problems
and that's just to start with the basic formula. So just start simple
and assume that a part of this is fixed. Now, the part that I'm
going to assume is fixed in the first stage is
the Game Div, my tab. So I'm just going to assume
I have this single tab here and just make my VLOOKUP formula work. Once it works, then I'm
going to think about how I could make the tab part dynamic. So let's start with VLOOKUP, I have a more detailed video on VLOOKUP, if you want to get more details, make sure you click on the link in the description of this video. First argument in VLOOKUP
is our lookup value. Since we knew we're going
to be looking in Game Div, that's not an argument here, our lookup value is our month here. I'm planning to pull this formula down, which means that I need to
fully fix this cell reference. Next argument is the table array. So I'm going to go to the Game Div, and I'm going to highlight this, just going to include more cells in there so that once I get more data, my formula is going to
automatically update. I'm going to fix this as well. Mixed argument is our column index number, so inside our index area, we want to go to the second column. Last argument is if I want an exact match and I do, so close bracket
per center, that's my number, let's just go in and
double check, Game Div, that was March, 2018, that looks right, that's my number. So now obviously when I
pull this formula down, it's going to be the same number because it's always looking in Game Divs. The part of the formula
that I want to make dynamic, is this reference to the name. How do I do that? Well, indirect is a great formula that could help us make this part dynamic. I also have a video on this that goes into more detail on how it works, I'll also put the link to
it in the description below. But just to remind you, what it does is that it uses the text that you give it or the cell reference that you give it as the address where it should go to. For example, if I type in as text indirect and I put in A3, I get Summary Report. Why do I get that? Because it goes to cell A3 and it returns that to my cell here. So if I type in A1 here
and instead of putting A3 under quotation marks as texts, I'm going to do a direct
cell reference to this cell. What will I get here? I get my title back,
because it's using this cell to get the address where it should go to. Inside the cell, it says
go to A1, it goes to A1 and it returns that. So indirect could help
me get this part dynamic. Now notice another thing, when you have spaces in your tab names, you get this single quotation marks. You don't get them if
you don't have spaces. So it's much easier if you
write your basic formula first and you see how it looks,
and then it's easier to put in the indirect function than if you try to write this
whole thing up from scratch. Where do we need indirect? We need it where we
have our cell references that are changing. So this before right here, that's fixed, that's not changing. We don't need indirect
here, this is the part that's changing. And from this actually it's
this part that's changing and what we can't do is we
can't just replace this part with a cell reference without putting it inside the indirect function, because remember, indirect
function can translate this into an address for VLOOKUP otherwise Excel can't
translate it into an address. So if I just put this inside indirect, I'm going to put quotation
marks, that's going to be text, I close it off right
here, I get the same value because this text address
is being translated with the indirect to a real address. So now that I have it in this form, I just need to replace this
part with a cell reference. Because I have a single quotation here, I have to put that separately as text. And then I have to use an & because I'm going to use cell references, so I'm combining texts
with a cell reference I need the & here, click on this, which I'm going to use instead of this. Now, again, I'm going to be
combining cell reference with text, which means I need the &, quotation mark to put this in there so I have the closing
quotation mark there, that looks good, press Enter. Now let's just pull this down,
we get different numbers. Productivity Div is 8,000,
Productivity Div 8,000, that looks good, Utility is 6844, 6844. (upbeat hip-hop music) What I'm going to do is to transform these data into Excel tables, I'm just going to click anywhere
on the data, press Control + T and then press Enter because
my table has headers. Now, I'm just going to go quickly and clear that table formatting and revert to the original formatting. So under Design Table tools,
we can give our table a name, the names that I'm going to give it is going to be the same
name as my division. What I'm going to do is
to replace the spaces with another character. So I'm going to use the
underscore, in this case, it's going to be Game_Div. Enter. I'm going to do the same
thing for Productivity Div and for Utility. Let's solve this one
using INDEX and MATCH . I also have a separate
video on INDEX and MATCH that describes it to you
in a lot more detail, you can check out the link to
it in the descriptions below. First argument in index is the array. The array is the area where our answer is. So I'm going to use the same
approach here I use with VLOOKUP, where I'm going to assume that everything is in Game Div first, just to see the type of
syntax I get back from Excel and then think about the
parts that I can replace using the indirect function. Our array is the invoiced amount. So if I go here and click on
it, I get the table syntax. Next argument is how many
rows do I want to move down? Here I can use the MATCH
function to give back the row to the index function. I'm going to look for the month here, so this part I need to fully fix. I'm looking at up in here
and I want an exact match, close, close, press Enter,
I get the same members. So again, if I pull this
down, it's fixed to Game Div, so obviously it's not changing. Now comes the indirect function into play. So which part of this do I
need to change with indirect? It's the name of the table this time. So not the name of the
tab like we had before, because it's independent,
which tab it sits in, this is the name of the table, but now we have a difference
to our original names here. That difference is that
we have an underscore, we don't have an underscore here. So we need a formula that basically makes this
one look exactly like this, which means wherever we have spaces to add an underscore to this name. And that's the formula that we need inside the indirect function. But again, before we get
this to become too complex, let's just put this
directly inside indirect, see if our formula works and then we can think of our other formula that's going to make these match. So I'm going to open bracket here, I'm going to put this part
inside quotation marks and close this. So that works fine, now comes our second formula into play. Here we can use the substitute function. So right here where I reference this, instead of referencing this,
I need to reference this and then combine it with this. But to make these match, I need to put an underscore
in the cell reference. So I'm going to wrap it
up inside SUBSTITUTE. What SUBSTITUTE does is
that it looks at my text, here, so that's the first argument here. It wants to look at what old
text do I want to replace? And the old text is the space. With which text do I want to replace it? With the underscore. And the last argument is optional. For how many instances do I want this replacement to be done? And actually I want to do
it whenever I have a space. So I'm just going to skip that argument. So now let's press Enter,
we also have to apply the same thing to our March part. So this is the one that
we also have to replace. Now I'm just going to
take this part and copy it and paste it right here. And I need to have the
quotation marks here, close bracket here. Now let's test this, looks great. So now let's add data to this. Let's change this to February, 2019, and we get the same
answers using both methods. If you like this video, don't
forget to give it a thumbs up. And for more videos like this one, why not subscribe to this channel so that you can get updates
when new videos come out. (upbeat guitar music)
