All language subtitles for 19 - Index Matching Multiple Criteria Dashboard V1 - lang_en

So, in our data, since we have two rows for a company at least,

we have Year 1 and Year 2 data,

we are in a pickle if you just try to use match with one criteria,

because when we get to this,

it's trying to match two criteria both the ticker symbol and the Year 1.

So, this is where we will use Match with two criteria.

For doing this, the Match function in Excel uses Boolean logic.

If you're familiar with Boolean logic this will be straightforward.

Using Boolean logic, we can assign a variable,

a value of 1 when a particular condition is true and

the variable is assigned a value of 0 if the condition is not met.

So, let's first tackle the Boolean logic here.

We can see that the ticker symbol AVGO is true in the case of cell 1 and

cell 2 which corresponds to the ticker symbol AVGO and the ticker symbol column here.

The value of Year 1 is true in the case of cell 1 or row 1,

3, 5, and 8.

But we want the condition of both ticker symbol AVGO and the

Year 1 to be true which happens to be only in the case of row 1.

So, this is when both the criteria are met.

So, I have indicated here with the true and false as well that these two are true.

But these are true in these rows,

but the combination of the two conditions is true only in this one, the first one,

and this is the value that we want to pull out here and then for Year 2,

where the condition is met,

we'll have to pull it out here.

So, we're saying match the condition when the value is 1.

So, we want both the conditions ticker symbol,

AVGO to be met,

which is row 1 as we know,

and we want the second Year 1 value to be matched as well.

So, only when both of these conditions are one,

meet that condition exactly using,

that's where the match type is zero and give us the location.

So, when we do this,

we see that the value is 1.

We want to plugin this particular value in the index function and say,

"Give me the cost of revenue from the cost of revenue array when the row number is 1."

So, I can show you this to be true.

I'm going to go back and actually put

this code here and you can actually see the formula.

We're making both the condition to be true.

What it does is, Excel does in the background is it calculates the, okay,

when H1 equal to ticker symbol and it does the Boolean logic

behind times when is a Boolean logic true for the years.

Multiply both of those conditions because it's assigning a value of 1 for

every true condition and 0 when the condition isn't met.

So, it's multiplying both of those and it's giving

this final array and when this condition is met.

So, it's saying, "Okay.

One match this condition exactly in the first location."

So, it gives us back the number 1.

That is how you get the value for and total revenue.

We can repeat the process for all of her cells.

You have to type it in by hand, let me do that.

So, we have total revenue,

match it to the ticker symbol and to the F6 is year,

give me the total revenue value when those conditions are met.

That's how you get the dashboard.

You can see that this is an interactive dashboard

because when you change the ticker symbol the value change.

It's showing up as NA in the case of Year 3 because as you,

let me show you this actual data.

As you can see, there's no Year 3 value except in the case of the HPE.

So, when you actually look at HPE,

that's when those values show up correctly.

That's how you do a dashboard using index and matching.