電子表格/索引匹配 (SPREADSHEETS / INDEX-MATCH)

In a previous article, we discussed about how and when to use VLOOKUP functions and what are the issues that we might face while using them. This article, on the other hand, will take you to a journey to understand an upgraded version of VLOOKUP. This upgrade is a combination of two functions in spreadsheets — INDEX and MATCH. Let us try and understand the working of INDEX-MATCH through the following example.

在上一篇文章中 ，我們討論了如何以及何時使用VLOOKUP函數，以及在使用它們時可能遇到的問題。 另一方面，本文將帶您了解VLOOKUP的升級版本 。 此升級是電子表格中兩個功能的組合INDEX和MATCH 。 讓我們嘗試通過以下示例來理解INDEX-MATCH的工作。

了解數據 (Understanding the Data)

As always, let us take an example from our favorite data source — Kaggle. The following screenshot is a small subset of the Netflix data which consists of TV shows and movies available on Netflix as of 2019.

與往常一樣，讓我們??以我們最喜歡的數據源Kaggle為例。 以下屏幕截圖是Netflix數據的一小部分，其中包括截至2019年Netflix上可用的電視節目和電影。

This dataset consists of different shows and movies along with their unique show_id, country we are considering, date when the show was added and the year when the entity was released. It also contains rating of the show/movie, duration and title of the content piece.

該數據集包含不同的節目和電影，以及它們唯一的show_id ，我們正在考慮的country ，添加節目的date和發布實體的year 。 它還包含內容的放映/電影rating ， duration和title 。

Consider now that we want to create a search method where the user can select a title and we display information to the user regarding that title. This search method would look something like this:

現在考慮，我們想創建一種搜索方法，用戶可以在其中選擇title然后向用戶顯示有關該標題的信息。 此搜索方法如下所示：

The user can input any title in the above example and we will try and find the type and rating of the title mentioned from the database. One of the simpler solutions to this is through VLOOKUP. We can easily find the rating of the title through it. Although, we would need to change the structure of the table to get the type of the title since VLOOKUP can only look to the right of the search value. Let’s see how can INDEX and MATCH formulas help us in solving this problem.

用戶可以在上面的示例中輸入任何title ，我們將嘗試從數據庫中查找提到的標題的type和rating 。 一種更簡單的解決方案是通過VLOOKUP。 我們可以通過它輕松找到標題的等級。 雖然，我們將需要更改表的結構以獲取標題的類型，因為VLOOKUP 只能在搜索值的右側查找。 讓我們看看INDEX和MATCH公式如何幫助我們解決此問題。

什么是索引？ (What is INDEX?)

INDEX formula in spreadsheets look something like this:

電子表格中的INDEX公式如下所示：

INDEX helps us in finding the content of the cell. It takes 3 inputs.

INDEX幫助我們找到單元格的內容。 它需要3個輸入。

• Row: The number of rows from the beginning of the reference table where the value lies. This is an optional value. If no value is supplied, it will take the first row as the value.

  行 ：從值所在的引用表的開頭開始的行數。 這是一個可選值。 如果未提供任何值，則它將第一行作為值。

• Column: The number of columns from the beginning of the reference table where the value lies. This is an optional value. If no value is supplied, it will take the first column as the value.

  列 ：從值所在的參考表開始的列數。 這是一個可選值。 如果未提供任何值，則它將第一列作為值。

To find the type of the title ‘Carrie Pilby’ in our table, we apply the following formula:

要在我們的表格中找到標題“ Carrie Pilby”的類型，我們使用以下公式：

=INDEX(A1:H23,12,2)

We select the complete table as reference, we find that this movie title is in the 12th row and we know that the type of the title is stored in 2nd column of the reference table. This will give the result as ‘Movie’ which is absolutely correct!

我們選擇完整的表作為參考 ，我們發現該電影標題位于第12行，并且知道標題的類型存儲在參考表的第二列中。 這將給出絕對正確的“電影”結果！

But did you notice any problems with this? We actually had to count the row number and the column number to get 12 and 2 as the parameters in the formula. This isn’t easy, is it? Let’s find out if there is any other way in the world which can help us in easing this process.

但是您注意到這個有什么問題嗎？ 實際上，我們必須對行號和列號進行計數，以獲得12和2作為公式中的參數。 這不容易，是嗎？ 讓我們找出世界上是否還有其他方法可以幫助我們簡化這一過程。

什么是MATCH？ (What is MATCH?)

MATCH formula in spreadsheets look something like this:

電子表格中的MATCH公式如下所示：

MATCH helps us in finding the relative position of the content in our table. It takes 3 inputs.

MATCH幫助我們找到表中內容的相對位置。 它需要3個輸入。

• Search Key: The value that we want to find.

  搜索鍵 ：我們要查找的值。

• Range: The row/column in which the value is situated. Note that range can only take a row or a column, but not both.

  范圍 ：值所在的行/列。 請注意，范圍只能包含一行或一列，但不能同時包含兩者。

• Search Type: For all practical purpose, we set this value as zero. This indicates that we are finding the exact value. This is an optional term which takes the value as 1 by default.

  搜索類型 ：出于所有實際目的， 我們將此值設置為零 。 這表明我們正在尋找確切的值。 這是一個可選術語，默認情況下將值設為1。

MATCH essentially gives us the row number or the column number of where the search term lies. Isn’t this the missing part of the INDEX puzzle we encountered earlier? We needed an easier way to find the row and column number of the search item, rather than counting it manually. And MATCH gives you exactly that!

MATCH本質上為我們提供了搜索詞所在的行號或列號。 這不是我們之前遇到的INDEX難題的缺失部分嗎？ 我們需要一種更簡單的方法來查找搜索項的行號和列號，而不是手動對其進行計數。 而MATCH正是為您提供！

神圣的INDEX-MATCH婚姻 (The holy INDEX-MATCH matrimony)

The above explanation now allows us to join the INDEX and MATCH formulas together and get the information we require with the minimum amount of hassle. Here is how a general INDEX-MATCH formula would look like:

上面的解釋現在使我們可以將INDEX和MATCH公式結合在一起，并以最少的麻煩獲得所需的信息。 通用INDEX-MATCH公式如下所示：

=INDEX(reference, MATCH(search_key, row, 0), MATCH(search_key, column, 0))

In the above formula, we provide a reference table to the INDEX, which is basically the data table where all the information is. Next, the first MATCH formula provides the row index of the search term and the second MATCH provides the column index of the search term. Finally, the combination of these two will provide the row and column index to the INDEX formula and we’ll get our desired result! Let’s try it out in our Netflix example.

在上面的公式中，我們提供了INDEX的參考表 ，它基本上是所有信息所在的數據表。 接下來，第一個MATCH公式提供搜索項的行索引， 第二個 MATCH公式提供搜索項的列索引 。 最后，這兩者的結合將為INDEX公式提供行索引和列索引，我們將獲得理想的結果！ 讓我們在Netflix示例中嘗試一下。

The above formula selects the entire table in first parameter of INDEX. Then it searches for the movie title mentioned in K1 through MATCH formula in the entire row of content titles, which is C1:C23. This will return whatever row number the title ‘Carrie Pilby’ is in. In the second MATCH, it searches for J2, which is the parameter that we want to find, in this case Type. This will return whatever column the column name ‘Type’ is in. And together it will provide the correct result i.e. Movie.

上面的公式在INDEX的第一個參數中選擇整個表。 然后，它通過MATCH公式在整個內容標題行(即C1：C23)中搜索K1中提到的電影標題。 這將返回標題為“ Carrie Pilby”所在的行號。在第二個MATCH中，它將搜索J2，這是我們要查找的參數，在本例中為Type。 這將返回列名“ Type”所在的任何列。并且一起提供正確的結果，即Movie。

Similarly, here is the formula for how to match rating in the table for the given content title.

同樣，這是有關如何匹配表中給定內容標題的評級的公式。

與VLOOKUP比較 (Comparing with VLOOKUP)

Often there will be comparisons on which formula to use to find the values of a given content. Although VLOOKUP is simpler to understand and provides an easy application, the INDEX-MATCH combination is a powerful match which provides the following advantages:

通常會比較使用哪個公式來查找給定內容的值。 盡管VLOOKUP易于理解并且易于使用，但INDEX-MATCH組合是功能強大的匹配項，具有以下優點：

1. You can use INDEX-MATCH to find a value against multiple criteria. In the above examples, we found Type and Rating with content name criteria and parameter criteria. This wouldn’t have been easy to achieve in VLOOKUP.

   您可以使用INDEX-MATCH 根據多個條件查找值 。 在以上示例中，我們找到了帶有內容名稱標準和參數標準的Type和Rating 。 這在VLOOKUP中并非容易實現。

2. VLOOKUP finds a match on the left and returns any value to the right of the search item. On the other hand, INDEX-MATCH can look both ways. In the above example, type was on the left of title and rating on the right, still it managed to find both the results correctly.

   VLOOKUP在左側找到一個匹配項，并在搜索項的右側返回任何值。 另一方面， INDEX-MATCH可以雙向查看 。 在上面的示例中，類型位于標題的左側，評級位于右側，但仍設法正確找到了兩個結果。

Understanding INDEX-MATCH adds an extremely versatile tool in your spreadsheet armory. INDEX-MATCH along with the knowledge of Pivot Tables can really help you to improve your analytical skills. Let me know in comments if this was a helpful piece of content!

了解INDEX-MATCH可以在電子表格庫中添加一個極其通用的工具。 INDEX-MATCH以及數據透視表的知識可以真正幫助您提高分析技能。 在評論中讓我知道這是否是有用的內容！