vlookup match

電子表格/索引匹配 (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.

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

了解數(shù)據(jù) (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.

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

Netflix Data for INDEX-MATCH demonstration用于INDEX-MATCH演示的Netflix數(shù)據(jù)

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.

該數(shù)據(jù)集包含不同的節(jié)目和電影，以及它們唯一的show_id ，我們正在考慮的country ，添加節(jié)目的date和發(fā)布實(shí)體的year 。 它還包含內(nèi)容的放映/電影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:

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

Example of how the search method will look like搜索方法外觀示例

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 ，我們將嘗試從數(shù)據(jù)庫(kù)中查找提到的標(biāo)題的type和rating 。 一種更簡(jiǎn)單的解決方案是通過VLOOKUP。 我們可以通過它輕松找到標(biāo)題的等級(jí)。 雖然，我們將需要更改表的結(jié)構(gòu)以獲取標(biāo)題的類型，因?yàn)閂LOOKUP 只能在搜索值的右側(cè)查找。 讓我們看看INDEX和MATCH公式如何幫助我們解決此問題。

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

INDEX formula in spreadsheets look something like this:

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

INDEX formula in Google SpreadsheetsGoogle Spreadsheets中的INDEX公式

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

INDEX幫助我們找到單元格的內(nèi)容。 它需要3個(gè)輸入。

• 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.

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

• 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.

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

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

要在我們的表格中找到標(biāo)題“ 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!

我們選擇完整的表作為參考 ，我們發(fā)現(xiàn)該電影標(biāo)題位于第12行，并且知道標(biāo)題的類型存儲(chǔ)在參考表的第二列中。 這將給出絕對(duì)正確的“電影”結(jié)果！

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.

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

什么是MATCH？ (What is MATCH?)

MATCH formula in spreadsheets look something like this:

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

MATCH formula in Google SpreadsheetsGoogle Spreadsheets中的MATCH公式

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

MATCH幫助我們找到表中內(nèi)容的相對(duì)位置。 它需要3個(gè)輸入。

• 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.

  范圍 ：值所在的行/列。 請(qǐng)注意，范圍只能包含一行或一列，但不能同時(shí)包含兩者。

• 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.

  搜索類型 ：出于所有實(shí)際目的， 我們將此值設(shè)置為零 。 這表明我們正在尋找確切的值。 這是一個(gè)可選術(shù)語(yǔ)，默認(rèn)情況下將值設(shè)為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本質(zhì)上為我們提供了搜索詞所在的行號(hào)或列號(hào)。 這不是我們之前遇到的INDEX難題的缺失部分嗎？ 我們需要一種更簡(jiǎn)單的方法來(lái)查找搜索項(xiàng)的行號(hào)和列號(hào)，而不是手動(dòng)對(duì)其進(jìn)行計(jì)數(shù)。 而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:

上面的解釋現(xiàn)在使我們可以將INDEX和MATCH公式結(jié)合在一起，并以最少的麻煩獲得所需的信息。 通用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的參考表 ，它基本上是所有信息所在的數(shù)據(jù)表。 接下來(lái)，第一個(gè)MATCH公式提供搜索項(xiàng)的行索引， 第二個(gè) MATCH公式提供搜索項(xiàng)的列索引 。 最后，這兩者的結(jié)合將為INDEX公式提供行索引和列索引，我們將獲得理想的結(jié)果！ 讓我們?cè)贜etflix示例中嘗試一下。

INDEX-MATCH for finding content typeINDEX-MATCH用于查找內(nèi)容類型

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的第一個(gè)參數(shù)中選擇整個(gè)表。 然后，它通過MATCH公式在整個(gè)內(nèi)容標(biāo)題行(即C1：C23)中搜索K1中提到的電影標(biāo)題。 這將返回標(biāo)題為“ Carrie Pilby”所在的行號(hào)。在第二個(gè)MATCH中，它將搜索J2，這是我們要查找的參數(shù)，在本例中為Type。 這將返回列名“ Type”所在的任何列。并且一起提供正確的結(jié)果，即Movie。

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

同樣，這是有關(guān)如何匹配表中給定內(nèi)容標(biāo)題的評(píng)級(jí)的公式。

INDEX-MATCH for finding content ratingINDEX-MATCH用于查找內(nèi)容分級(jí)

與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:

通常會(huì)比較使用哪個(gè)公式來(lái)查找給定內(nèi)容的值。 盡管VLOOKUP易于理解并且易于使用，但I(xiàn)NDEX-MATCH組合是功能強(qiáng)大的匹配項(xiàng)，具有以下優(yōu)點(diǎn)：

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 根據(jù)多個(gè)條件查找值 。 在以上示例中，我們找到了帶有內(nèi)容名稱標(biāo)準(zhǔn)和參數(shù)標(biāo)準(zhǔn)的Type和Rating 。 這在VLOOKUP中并非容易實(shí)現(xiàn)。

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在左側(cè)找到一個(gè)匹配項(xiàng)，并在搜索項(xiàng)的右側(cè)返回任何值。 另一方面， INDEX-MATCH可以雙向查看 。 在上面的示例中，類型位于標(biāo)題的左側(cè)，評(píng)級(jí)位于右側(cè)，但仍設(shè)法正確找到了兩個(gè)結(jié)果。

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可以在電子表格庫(kù)中添加一個(gè)極其通用的工具。 INDEX-MATCH以及數(shù)據(jù)透視表的知識(shí)可以真正幫助您提高分析技能。 在評(píng)論中讓我知道這是否是有用的內(nèi)容！

翻譯自: https://towardsdatascience.com/index-match-an-upgrade-on-vlookup-functions-320e43253d15

vlookup match

總結(jié)

以上是生活随笔為你收集整理的vlookup match_INDEX-MATCH — VLOOKUP功能的升级的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。