玩转Excel系列-index+match查找函数用法-嵌套match函数-excel表格

 

 

在本系列有关INDEX-MATCH的第1部分中，我从以下两个示例开始：

 

公式1：= INDEX（Product，2,1）

公式2：= $ B $ 4 

我解释说，在我使用的特定工作簿中，两个公式返回的结果完全相同。

 

好吧，几十年前，Excel首次推出后不久，我就在电话上与Excel Program Manager进行了交谈。考虑到像这两个公式这样的示例，我告诉他INDEX是一个无用的函数，因为它要求我们为行和列参数输入数字。

 

我说：“如果我们知道要使用的行号和列号，那么我们也可以输入单元格地址。那么索引的意义是什么？”

 

他对我很耐心。他说：“查理，您看过MATCH函数吗？我们创建了MATCH专门用于INDEX。”

 

好吧，正如Excel的首席执行长在几年前建议我做的那样，今天，我们将看一下MATCH函数……该函数是专门为与INDEX配合使用而创建的。

 

（此链接使您 可以在此处下载所有三个示例工作簿的zip文件。）

 

MATCH是一种查找功能，例如VLOOKUP，HLOOKUP和LOOKUP。但是与其他函数不同，MATCH不会返回找到的值。而是，MATCH返回单个行，列或一维数组中查找值的位置。

 

为了说明，如果您的查找值是查找数组中的第三项，则MATCH返回3。或者，如果您的查找值位于查找数组中的第67位，则MATCH返回67。

 

这是函数的语法...

 

 

...哪里...

 

lookup_value是您要查找的值。

lookup_array是单个行或列，或者是一维数组，MATCH在其中应查找lookup_value。

match_type是三个数字之一，用于指定数据的排序方式和MATCH的执行方式。

以下是有关您可以为match_type输入的三个数字的简短说明...

 

Match_Type =0  。大约95％的时间将使用此类型。无论lookup_array的排序方式如何，它都会返回在lookup_array中找到的第一个匹配项的位置。如果Lookup_Array中是不是在你的查找数组，MATCH将返回＃N / A。

 

Match_Type =1。   仅出于特定原因，并且仅对以升序排序的数据使用此类型。如果数据不是按升序排列，则MATCH可能返回错误的结果或错误的＃N / A值。如果lookup_value不在lookup_array中，则MATCH返回小于或等于lookup_value的最大值的位置。（我稍后将为您提供示例。）

 

Match_Type = -1。 我认为我从未找到过将match_type用于实际工作的理由。您只能将此类型用于按降序排序的数据。如果lookup_value不在您的lookup_array中，则它将使MATCH返回大于或等于lookup_value的最小值的位置。

 

让我们看一些例子...

 

 

您大约有95％的时间会使用这种类型。无论您的lookup_array如何排序，它都会返回正确的结果。如果lookup_value不在您的lookup_array中，则MATCH返回＃N / A。

 

MATCH类型等于零本示例使用未排序的表。也就是说，数据是随机的。

 

公式3：= MATCH（6，Test，0）   [结果= 3]

 

MATCH沿“测试”列向下移动，直到找到6，然后返回3，表示值6是列表中的第三项。

 

公式4：= MATCH（7，Test，0）  [结果=＃N / A]

 

MATCH浏览整个列表，但是找不到值7。因此，它返回＃N / A。

 

对文本数据使用MATCH无论文本如何排序，MATCH都可以处理文本。

 

公式5：= MATCH（“ x”，Test，0）   [结果= 5]

 

 

到这个时候，应该就不足为奇了。该公式搜索“ x”，并告诉我们它是列表中的第五项。

 

公式6：= MATCH（“ m”，Test，0）   [结果=＃N / A]

 

在这里，我们正在搜索不在列表中的项目，并且MATCH可靠地为我们提供了＃N / A值。

 

 

仅出于特定原因使用此match_type，并且始终对数据以升序排序。如果数据不是按升序排列，则MATCH有时会返回错误的结果或错误的＃N / A值。

 

匹配类型为1的升序数据使用match_type为1的一个常见原因是与价格折扣表一起使用，我将在本系列的第3部分中向您展示。

 

 

 

以下是排序表中的一些示例...

 

公式7：= MATCH（4，Test，1）  [结果= 2]

 

当您的数据正确排序时，使用match_type为1会给您可靠的正确结果。

 

公式8：= MATCH（3，Test，1）   [结果= 1]

 

这是一种记住MATCH在搜索表中未包含的数据时如何工作的方法：MATCH查找下一个最大值，然后备份一个位置。

 

 对文本数据使用MATCH公式9：= MATCH（“ m”，Test，1）   [结果= 3]

 

即使文本正确排序，我也无法想到为什么您会对文本使用match_type为1的原因。例如，在公式9中，我们搜索“ m”，MATCH（正确！）返回“ f”的位置。

 

 

您只能将此类型用于按降序排序的数据...这是我从未发现过的理由。

 

但是，如果您确实需要查找以降序排列的数据，则MATCH是 与MATCH功能配合使用的数据降序排列您唯一的选择。VLOOKUP或LOOKUP都不提供该功能。

 

 

 

 

 

这是两个简单的例子...

 

公式10：= MATCH（6，Test，-1）   [结果= 2]

 

 

公式11：= MATCH（3，Test，-1）   [结果= 4]

 

如果列表中不存在lookup_value，则match_type为-1的MATCH返回大于或等于lookup_value的最小值的位置。也就是说，它找到小于lookup_value的第一个值，然后备份一个位置。