All language subtitles for 18 - Index Match 1criterion V1 - lang_en

So, now we have builtin the year one values in our income statement,

but as we move to year two,

we quickly realize that it's not going to be so easy moving down the list.

AVGO and year one were the very first row in our original data.

But as we go further down,

it becomes more challenging to hard-code in the row number.

Because what if it was CSRA or year three for HPE ticker symbol?

We want to match both of these criteria

for the cell value to be pulled and placed into our dashboard.

This is where the match function comes in handy.

So, let's walk through one example.

We're matching just one criteria, the ticker symbol.

Again, it's the empty dashboard we're trying to fill,

and this is the data we want to pull from.

I've gone ahead and hidden the extra financial metrics that we were showing earlier.

So, right now, we're trying to match just the AVGO ticker symbol, just one criteria.

So to do that,

I've replicated that in this worksheet here.

So you can see we have the ticker symbol,

I'm going to go ahead and hide this, and the total revenue.

We're trying to, in our dashboard,

select the value that meets at least this ticker symbol.

So, we want the INDEX to,

we don't want to hard-code the number one here.

We just wanted to match it when the ticker symbol matches AVGO.

So, we need to plugin the position for it.

So, MATCH can return the number representing the position in the ticker symbol array.

So this is the ticker symbol array.

I'll go ahead and actually call it ticker_symbol.

We'll look for a MATCH H2 here,

that's the ticker symbol,

ticker symbol_array is the one here,

and we're saying if this match type value is one,

MATCH will look for the largest value that is less than or equal to the lookup value.

If MATCH type is zero,

MATCH looks for the first value that is exactly equal to the lookup value.

There is another MATCH type negative one that looks for

the smallest value that is greater than or equal to the lookup value.

I'm providing some example for this in the text box below.

So, we're going go ahead in zero because we want MATCH

to look for the position in this array,

ticker_symbol array, that matches AVGO,

which matches this condition that we're trying to meet.

So, we referred to this lookup value.

This is the lookup value highlighted in blue,

ticker_symbol is actually referring to this ticker symbol right here,

and zero is the condition we're trying to meet.

Then we hit Enter there,

it gives us the value one.

Place this here so you can see the formula,

but I had to do the Ctrl + Shift + Enter again,

and this is the value of return.

So, we want the value one to show up here inside the INDEX function,

and that's why we just plugin whatever we take as a MATCH into the INDEX function,

because it's looking for.

Let me show you that. So, it's looking for the row number,

and that row number's provided by the MATCH function.

As you can see, the column number right here is the

zero because we're saying stay within the total revenue array,

match the very first value in terms of row number but stay within the zero column number.

When we do this, it gives us this value.

I've again placed it here so that you can see what

the formula looks like and what it gives,

and it's the same value that is given by having the MATCH function is there.

So, we can put this MATCH function inside the INDEX function,

and it tells INDEX which cell to pull

the total revenue value from and it returns the value to us.