excel中match函数_30天中30个Excel函数：19

excel中match函数

Yesterday, in the 30XL30D challenge, we found text strings with the SEARCH function, and used IFERROR and ISNUMBER to deal with its error results.

昨天，在30XL30D挑战中，我们使用SEARCH函数找到了文本字符串，并使用IFERROR和ISNUMBER处理其错误结果。

For day 19 in the challenge, we'll examine the MATCH function. It searches for a value in an array, and returns its position, if the value is found.

在挑战的第19天，我们将检查MATCH函数。它在数组中搜索值，如果找到该值，则返回其位置。

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the MATCH information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下MATCH信息和示例，如果还有其他提示或示例，请在评论中分享。

功能19：MATCH (Function 19: MATCH)

The MATCH function returns the position of a value in an array, or #N/A if not found. The array can be sorted, or unsorted, and the MATCH function is not case sensitive.

MATCH函数返回值在数组中的位置；如果找不到，则返回＃N / A。数组可以排序或不排序，并且MATCH函数不区分大小写。

您如何使用MATCH？ (How Could You Use MATCH?)

The MATCH function returns the position of an item in an array, and that result can be used by other functions, such as INDEX or VLOOKUP. For example:

MATCH函数返回一个项目在数组中的位置，该结果可由其他函数使用，例如INDEX或VLOOKUP。例如：

Find position of item in unsorted list
在未排序列表中查找项目的位置
Use with CHOOSE to get student grades
与CHOOSE一起使用可获得学生成绩
Use with VLOOKUP for flexible column choice
与VLOOKUP一起使用以进行灵活的列选择
Use with INDEX for to show winner's name
与INDEX配合使用以显示获奖者的名字

匹配语法 (MATCH Syntax)

The MATCH function has the following syntax:

MATCH函数具有以下语法：

- lookup_value can be text, number or logical value
  lookup_value可以是文本，数字或逻辑值
- lookup_array is an array, or array reference (contiguous cells in a single row or column)
  lookup_array是一个数组或数组引用(单个行或列中的连续单元格)
- match_type can be -1, 0 or 1. If omitted, assumed to be 1
  match_type可以为-1、0或1。如果省略，则假定为1
MATCH(lookup_value,lookup_array,[match_type])
匹配(lookup_value，lookup_array，[match_type])

比赛陷阱 (MATCH Traps)

MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.

MATCH函数返回找到的项目的位置，而不是值。如果需要该值，则将MATCH与另一个函数(如INDEX)结合使用。

示例1：在未排序列表中查找项目 (Example 1: Find Item in Unsorted List)

For an unsorted list, you can use 0 as the match_type argument, to find an exact match. If you're searching for text, and using 0, you can include wildcard characters in the lookup value.

对于未排序的列表，可以将0用作match_type参数，以查找完全匹配。如果要搜索文本并使用0，则可以在查找值中包含通配符。

In this example, you can type a month name, or partial name with wildcards, to find that month's position in the list.

在此示例中，您可以键入月份名称或带通配符的部分名称，以查找该月份在列表中的位置。

=MATCH(D2,B3:B7,0)

= MATCH(D2，B3：B7,0)

Instead of an array reference, you can enter an array as the lookup_array argument.

您可以输入一个数组作为lookup_array参数来代替数组引用。

In this variation, the lookup month name is entered in cell D5, and three month names are entered in the MATCH function's second argument.

在此变体中，在单元格D5中输入查找月份名称，并在MATCH函数的第二个参数中输入三个月份的名称。

If a later month, such as Oct, is entered in D5, the result will be #N/A.

如果在D5中输入了一个较晚的月份，例如Oct，则结果将为＃N / A。

=IF(C2+0<14,"Time to upgrade","Latest version")

= IF(C2 + 0 <14，“升级时间”，“最新版本”)

示例2：将学生成绩更改为字母 (Example 2: Change Student Grades to Letters)

Just as you did with VLOOKUP, you can use MATCH to help convert a student's score to a letter grade. In this example, it is combined with CHOOSE, to get the letter grade. The match_type is -1, because the scores are sorted in descending order.

就像使用VLOOKUP一样 ，您可以使用MATCH来帮助将学生的分数转换为字母分数。在此示例中，将其与CHOOSE结合使用以获得字母等级。 match_type为-1，因为分数按降序排列。

When the match_type is -1, the result is the smallest value greater than or equal to lookup value. The lookup value is 54, and it's not in the list of scores, so the position for 60 is returned.

当match_type为-1时，结果是大于或等于查找值的最小值。查找值为54，并且不在分数列表中，因此返回60的位置。

Because 60 is in position 4, the 4th value in the CHOOSE options is the result -- cell C6, with a value of D.

由于60在位置4中，因此CHOOSE选项中的第4个值是结果-单元格C6，其值为D。

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

=选择(MATCH(B9，B3：B7，-1)，C3，C4，C5，C6，C7)

示例3：在VLOOKUP中创建灵活的列选择 (Example 3: Create Flexible Column Choice in VLOOKUP)

To make a VLOOKUP formula more flexible, you can use MATCH to find a column number, instead of hard-coding it into the formula.

为了使VLOOKUP公式更加灵活，可以使用MATCH查找列号，而不是将其硬编码到公式中。

In this example, users can select a region in cell H1, as the value for the VLOOKUP. Then, they can select a Month in cell H2, and the MATCH function returns the column for that month.

在此示例中，用户可以选择单元格H1中的区域作为VLOOKUP的值。然后，他们可以在单元格H2中选择一个月，然后MATCH函数返回该月的列。

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

= VLOOKUP(H1，$ B $ 2：$ E $ 5，MATCH(H2，B1：E1,0)，FALSE)

示例4：使用INDEX查找最接近的匹配项 (Example 4: FIND Closest Match with INDEX)

The MATCH function also works well with the INDEX function, which we'll see later in the challenge.

MATCH函数也可以与INDEX函数一起很好地使用，稍后我们将在挑战中看到。

In this example, the MATCH function is used to find the guess that is closest to the correct number.

在此示例中，MATCH函数用于查找最接近正确数字的猜测。

The ABS function returns the absolute difference between each guess and the correct number.
ABS函数返回每个猜测和正确数字之间的绝对差。
The MIN function finds the smallest difference.
MIN函数找到最小的差异。
The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned.
MATCH函数在差异列表中找到最小的差异。如果存在多个相同的差异，则将返回第一个。
The INDEX function returns the name in that position in the list of names.
INDEX函数返回名称列表中该位置的名称。

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

= INDEX(B2：B5，MATCH(MIN(ABS(C2：C5-F1))，ABS(C2：C5-F1)，0))

下载MATCH功能文件 (Download the MATCH Function File)

To see the formulas used in today's examples, you can download the MATCH function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载MATCH函数示例工作簿 。该文件已压缩，并且为Excel 2007文件格式。