00:00If you are an Excel user, you're probably going to want to watch this video here.
00:03To determine Ella's 2021 earning, which is going to be right here,
00:07you're going to have to utilize two functions called index and match function,
00:11which is going to help you retrieve the intersection of value within the row and the column.
00:15Let's say, for example, if I were to draw a line from here, Ella, right across here,
00:20and 2021 down this way, I should be getting a value of 11 million if I were to draw an intersection here.
00:28So the way to do it, you're going to have to use this formula, equal index.
00:33The first argument of index would be the data set area itself or the array itself.
00:39And the second argument pertains to the name Ella and where can you find it from this respect here.
00:45So you're going to have to use a function called match.
00:48And the first argument would be Ella itself, which is hidden right here, which is on cell K3, comma.
00:55And the array where you're going to look for Ella would be from here all the way down here.
01:01And the third argument on that match function would be zero to indicate exact match.
01:08Let's close parenthesis and comma.
01:11Now to look for the year, you're going to use match again.
01:15Open parenthesis.
01:16The first argument would be this cell itself, L2, which is 2021, comma.
01:22And the array where you're going to look for 2021 would be this one here, C2 to I2, like this.
01:30And comma, we're going to say again, exact match, put a zero, close parenthesis, that closes the match.
01:36And one more close parenthesis to close the index.
01:40If you hit enter, you'll get 11 million, which is actually if you draw a line here and line here, you get 11.
01:48Let me break down this formula a bit more to see what you get.
01:51I'm going to take the first match function here, copy in a clipboard.
01:56And then just as an exercise, I'm going to say equal match here.
02:02And you can see that this returns a value of four.
02:04How do you get four?
02:05If you count here.
02:07So if you count one, two, three, four.
02:10So essentially it's returning the row number on that array here.
02:14And likewise, let's do the second match here like this.
02:18Copy in a clipboard.
02:19I'm going to put right below it, equal match.
02:22This is for the year.
02:23And this one will return the value of five.
02:25And if you were to count, one, two, three, four, five.
02:30So technically, this formula, the first match is returning four.
02:35And the second is returning an integer of five.
02:38And if you take those two numbers into this grid here, you get the exact location of where the 11 million comes from.
Comments