Name: INDEX和MATCH這兩個函數的應用分別和組合[高級Excel]。 (INDEX and MATCH application of the two functions separately and combined [Advanced Excel])
Uploaded: 2021-01-14T09:05:12.000Z
Duration: 7 min 27 s
Description: 【看影片學英語】數萬部 YouTube 影片，搭配英漢字典即點即查，輕鬆掌握單字發音與用法，長久累積看電影不必再看字幕。

未來簡單式

in this extract, we will see two Interesting Excel functions.

頻率副詞

We'll show them within the same video because they are often applied together.

Their combination offers an interesting alternative to V.

Let's see the application of these two functions separately and then combined Index is a formula which returns the value located at a given intersection within an array.

So basically the index formula needs us to indicate the following one an array where it will function to a row.

The formula will go and find the specified row and the specified column within the array and deliver its content.

For example, if we select the array from before to see 12 in our table and choose five as the row argument and two as the column argument, we will obtain Italy.

As a result, the index function simply delivers the cell, which has the coordinates we chose within the fifth row and second column of the range we selected lies Italy, right, we indicated those coordinates and index provided us the result.

Let's do another try also like the same range from before to see 12.

This time, let's pick one as the row argument and again one as the column argument.

The result is Marussya, given that the value in the first row and the first column of the selected range is Bruce CIA.

The next function we will see in this video is a match.

Match returns the relative position of an item within an array.

Let's illustrate that with an example, I'll type the match function.

The first argument that needs to be selected is the look up value in our example that will be Milan lying in B 19.

After that, we need to specify which is the array where the look of values position needs to be found.

Let's select all teams that lie within the range from before to be 12.

The third argument is a logical value, zero or one standing for an exact match and closest match.

I'll select zero as we need an exact match.

The output of the function is to which represents Milan's position within the selected array.

It is important to notice that the formula can be applied also vertically.

For example, if we go and look at number of games played within the array from B 32 F three.

The result will be four, which is correct as games played is the fourth column within the array.

Let's consider the combination of index and match together.

This is a pretty powerful tool, often considered superior to V Look up as it allows users to have ah, flexible look up value within the source table.

It is intuitive to combine index and match the first formula needs as an input relative positions within a range, and the second formula provides that.

Let's see how the two functions are applied together.

In practice, we have a task similar to the one before two tables.

The one on the left is our source table, and the one on the right needs to be filled.

First will apply the match formula to each of the blank cells in the table on the right, so we have plus match.

The look of value is Milan in Sell I for Let's fix its column reference as this will be the look up value of our table.

Okay, let's select as a lookup array that cells from before to be 12 and fix them.

We're looking for an exact match and all type zero as the third argument of the formula.

Let's copy the formula on the row below as well.

The results that we obtained are that Milan is in the second position and hamburger is in the fourth position within the selected array, and this is correct.

Now let's copy the two formulas for the rest of the blank cells.

Okay, so what we have here is the position of the lookup value within the source table.

It does not do much work, but only for now.

Let's type index in front of the match function and add a left parenthesis.

The array is the range of cells among which the output of the index formula is selected.

Let's select the cells from C four to see 12 and fix their row references.

Let's recap how the two functions work together.

The index formula needs as an input the number of the row within a range right, and we have found the number we're looking for, thanks to the match function.

In other words, the match function indicates the position of the result, and the index function extracts it.

First argument is the array of the source from which we want to extract the result.

The second argument of the index function is the match function, which finds the exact piece of data that we're looking for now.

I'd like to show you an example of when index match is superior to V.

Let's cut the values under the column team within the source table and paste them in column G.

Nothing changed within the table on the right.

This is because the combination of index and match works even when the look of value is on the right.

We would have seen an instant n a error message.

 The combination of index and match is great for situations when you have an original source sheet and you don't want to change anything in it, and your look of value is on the right, we hope that this video was useful to you. 