Transcripts
1. Introduction: We will talk about index
and match functions and the powerful combination
of both the functions. We'll also learn what
is an array function. Imagine in a given situation, you need to pull data
dynamically from a huge database from up from down from left from right, with 100% accuracy. What will you do? Index
and match can help you. It can survive column insertion. If you insert column and keeps adding the details
in the main data, it can still survive. It works beautifully
with array function. Now what is an array function? An array is Excel thinking multiple conditions
at the same time. It is used for more
complex calculations. When we use array,
Excel stops thinking cell by cell and starts
thinking more logically. Instead of asking, what is
the value in this cell, Excel asks, what value satisfies my condition?
That is an array function. In this class, we'll see
some more concepts of Excel like pay spatial
data validation, condition formatting, what
is the concept of locking of the cell and much more.
See you in the class.
2. Index + Match Functions: First, we will see the basics of the index and match functions
individually what it does. And then if we combine it, what amazing results can we get. Okay, so let's start with the basics of the
index and match. As I said, index returns the value and the match
returns the position. Now, let me type the
function as index, and this will be
my entire array. And let's say second
is the number I want. So second number we have
second name is James. I'm going to get that details. Let's talk about match and my
look of value may be James, and this is going to be
my entire table array. And zero for exact match. So again, I'm also getting
second number as James. Let's say, instead of this, if I type this cell, let's enter, and if I type two. So here from this cell,
I can give commands. Let's say three, right? So it's going to
replace accordingly. So this is a very
basic understanding of index and match function. Now we will see how magic
it can do in combination. We have details
here, let's say, uh, the details of some
employees on the left side and the details of
their employee ID respectively on the right side. Likewise, we have employee IDs on the left side and we want details on the right side here, and that has to
match accordingly. Now, Look up cannot work here because it works
only from left to right. Right, so we will be using
index and match function. Let me start with index folkson and my table array will
be, I want the names here. I'll type names, select names, and I'll lock the cell.
Why am I locking? Because I want to drag this. Okay. Then let me
use match function. I want to match the cells. With all these cells, and
all the cells will be locked and zero for exact match and close both the
brackets and enter. And let me track this
to get all the answers. And here we are with
all the answers 1024 is 1024 is Jessica. And 1028 is Michael, right? So accordingly, we
are getting this. Now, these are the system
errors that we are receiving. Suppose in a huge database, we want our own edited, our own customized error to appear and not the system error. And we also want it to be highlighted so that we can point out the errors in the huge
data, how we can do that. For our personal errors, what we will do, we will add one more function here if error. And I will re dig this. Instead of getting Excel
default systematic error, I'm getting not found
as my customized error. And how can I highlight this? We will use conditional
formatting. We will let this portion
remain selected, go to condition formatting
and text that contain. Not found. Here, we can also edit and customize the
color we want the details in, so we'll let it remain red. And here we are, let's say, if we delete this, let's say if we write
any hypothetical number, we are going to get the error, the customized error
that we have selected. I hope this is very
much clear so far that what combination of index and match we can use in
certain given situation. Now, let's use a couple of matches like index with
a couple of conditions. We have one more example. Where we have the names of
the students written here, their respective subjects and their respective
marks accordingly. Now we want the subject
names to appear here and the student's
name if you select, we should get the answer. Now for subject names, I'll select the
subject as Control C, copy, and I'll do
the pay special. This is called transpose. It's pay spatial.
It will transpose from vertical to
horizontal and vice versa. I can also type names
here, but I have written. I have made it as
a drop down list. I can go to data validation. Here, and from
here, I can select the list and the sources. So I hope that is
very much easier. Now, let's start with
index and match. Now we want the names if you select the name of a particular
student and the subject, we want their respective
marks to appear here. We have two conditions. In this entire table array, we want to search the names of the students as well as their
subjects, respectively. We will use index and
a couple of matches. Say index entire table.
I will lock this. Why am I locking? Because
I want to drag it. Then I want to match. Now what will I match the names first? I'll again log this. Where are the names appearing
in the second column? Again, I'll log this, I'll
select and lock this Control. Then I'll use another match. By because now I have made
the adjustments for names, but not for the subjects. I will select the subject, but I will not lock it because I want to drag only vertically. There's no horizontal
dragging. Match subjects. Now, where are the subjects
on the first row? A 12f. Again, I will lock this
zero for Exact Match, I'll close both the
brackets and here I am. If we look at Sara's
histories marks, we have 73. Now since we have locked
also, we can drag this. You can compare all
the marks given here. Let me change the name to Sima and here I'm
getting the names here. Let me change the name to
I'm getting the names here. I'm getting the
names here, right? So this is how we can use the but a couple of
functions like that. This is how we can complete, index and match function. We can now we'll look
for array function.
3. Array Function: Now we will see
what is an array? What is called an array, this curly bracket, this braces. Instead of typing Enter, pressing Enter after a function, we press Control Shift Enter. So that's an array and it uses multiple conditions with
multiple criterias. Have a look. Now
let's look at array. Now, we are given this table where we have the
names of the employees here, then their respective
departments. And if you observe, there are names common in
some departments. Let's say we have Peter Gratia in AHR as well as some in SAS. So this has to be noted, right? Then we are given the salary, the performance
allowances, and the total. Now what do we want here? We want the names
to appear here, and we've also want the
departments to appear here. So if you select the names
as well as the department, we should get the salary and
the performance allowances. Now for names, I have
created a drop down list. And for department also, I have created a drop down list. Now for names, as it's very common that I go to
data validation, select the list and select
the entire source 2-13. This is the names, right? But what will I do
for the department? Because there is no source. They are on
reputation. So instead of selecting any source, I will write it personally the names of the
departments with comma, so it will also become a kind of like a drop down list, right? Now, we have Alice
Johnson from operations. Now, we won the
performance of the sales. Like we won the details of the sales here with this
couple of criteria. What we will do we start with
index and match function. Now array, what it
is asking me array. Now what will be the
array, the array should be C one to C 13 because that's what the salary is and we
want the salary here. Now we will use match. Now we have two matches, the names and the department. I can use concatenate
here with first, second condition, bracket over, then, then I want to look
up array of this condition. What will I do? For the names, I'll select this entire
lookup the array as the names and uh I'll use and and
I will use department. So for b12b 13, this is going to
be my department, zero for exact match, couple of Blackett closes,
and this is the array. I want to press Control, Shift Enter instead of Enter. Control Shift Enter, and I'm going to
look at the results. Alice Johnson from operation
is earning 500 a salary. Now, for the performance also, I cannot drag this
function array function. So for the performance also, I'll use the same
method, index and match. But for performance, the array
will be a d12d 13 match, concat net of these two
names and department. Bracket close, and the
names are appearing 1-13 and the departments are appearing from
B one to B 13, zero for exact match, closing with the brackets,
control shift enter. Here I'm getting the details. Let's check some other names. Let's say we have Peter, we'll use Peter
and he's from HR. So Peter from HR is
getting 300 as bonus, $30 as allowance or 300
salary and 30 as allowance. And Peter from sales is
receiving a different salary. And here we are
Peter from sales. So likewise, we have
seen that how we can do the array function with
multiple conditions and multiple outcomes. And see the details, you know, um, the important documents can never be uploaded
to AI tools, right? We need a human understanding
and human calculations, and I hope I have just helped you in
understanding this function. Please go to the
project and do it.
4. Project: Now for the project
of this class, I have this table given
to me and what it has, it has some iPhone names, hypothetical random names
without model number, but then these are
actually company names and the Poco and Opo there on repetition, if
you observe, right? Now, we have the versions
also given here, the storage capacity, quantity, and the cost. What do I want? I want the model number to appear here as
a drop down list, iPhone should appear here Pim. I want the versions to appear. We all the versions they
have should appear here, and the storage capacity should appear here
as a drop down list, you will make it with
data validation. What else do I want? I want you to write
an array function here with index and
match combination, and I want that when
I select a model, when I select a version, when I select a storage
from the dropdown list, if all three conditions
are fulfilled, I want the data from
this table to be here, that what is the quantity
of these three conditions? Right? So I hope this
is very much clear that how you're going to approach for the same
same for the cost. You need to match use
index and match and find out what will be the cost if three conditions
are satisfied. So please do it yourself
and upload it in the project gallery. See
you in the next class.
Aamir Shaikh, Freelancer, Youtuber, Trainer
