如何使用 index-match 反转 excel 中的 vlookup？
要了解 INDEX 和 MATCH 如何作为反向查找公式工作，您需要选择一件简单的事情： Match 告诉索引某个值在列或行中的位置（单元格编号），然后索引返回该值。使用此位置（单元格编号）的值。
可以这样想，MATCH 函数是一名发现罪犯的卧底特工，而 INDEX 函数是一名随后逮捕该罪犯的警察。
但是，让我们详细了解如何组合这两个功能。如您所知，下面是 INDEX 的语法。
INDEX(数组, 行数, [列数])
在 INDEX 函数中，row_num 参数告诉它应该从哪一行返回值。假设如果输入 4，它将返回第 4 行的值。
要创建反向查找公式，我们需要将 MATCH 替换为 INDEX 的 row_argument。
当我们使用 MATCH 时，它会在搜索列中搜索值并返回该值的单元格编号。然后，INDEX 使用该数字来确定值列中的单元格位置。
最后它返回该单元格的值，您就得到了您正在寻找的值。但现在让我们来看一个真实的例子。下面是城市列表以及在那里工作的员工姓名。
在这里，我们需要搜索在孟买工作的员工的姓名。现在，如果您查看数据，在包含城市的列中，这是我们的查找列，在包含员工姓名的列中，这是您的值列。公式为：
=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
让我们把这个公式分成两部分来理解它。
第 1 部分：在第一部分中，我们使用 match 函数搜索值“Mumbai”，它返回“5”，这是城市列中包含值“Mumbai”的单元格的位置。
第 2 部分：在第二部分中，我们使用 INDEX 并引用员工姓名列来查找值。这里索引函数知道您想要该列的第 5 个单元格的值。因此，它在结果中返回“Siya”。
更多索引和匹配示例
我们面前有一些常见问题，我们已经使用 INDEX MATCH 公式解决了这些问题。访问示例文件：请务必从此处下载这些示例文件以遵循每个示例。
1. 使用 INDEX – MATCH 进行基本搜索
普通搜索是您需要使用搜索公式执行的最重要的任务之一，而 INDEX MATCH 非常适合此任务。这里我们有一个包含员工 ID 和姓名的数据表。每个 ID 都是唯一的，您需要通过 ID 搜索员工的姓名。
假设您要搜索名称 EMP-132。为此，公式为：
=INDEX(name_column,MATCH(emp-id,emp-id_column,0))
这就是这个公式的工作原理
首先，MATCH 匹配 emp id 列中的 emp id，并返回您要查找的 id 的单元格编号。这里的行号是 6。
之后， INDEX 使用相同的单元格编号从姓名列返回员工姓名。
2. 向左看
VLOOKUP 查找值时不能向左。正如我提到的，在 INDEX 和 MATCH 中您可以向任何方向搜索。在下面的数据表中，金额列后面有发票编号列。
因此，如果您想搜索特定账单的金额，则使用 VLOOKUP 是不可能的。在 VLOOKUP 中，当您选择一个表时，该表中的第一列将是查找列。
但是，在此表中，我们需要使用表的最后一列作为查找列。所以这里对 VLOOKUP 表示反对。让我们调用 INDEX 和 MATCH 进行救援，公式为：
=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)
……这就是这个公式的工作原理
- 首先，您引用了索引函数中的金额列。这是我们需要从中获取值的列。
- 其次，在索引函数的 row_number 参数中，您使用了匹配函数并指定了发票编号，引用发票列并使用零进行精确匹配。
- 第三，匹配函数返回范围内发票的单元格编号。
最后，INDEX 使用此数字通过定位金额列中的单元格来返回金额。
3.模糊搜索
就像VLOOKUP一样，你也可以使用INDEX/MATCH进行粗略搜索。
当您要查找的值未列出并且您想要获得最接近的匹配项时，模糊搜索会很有用。在下表中，您有一个基于成绩的成绩列表。
而且，如果你想得到 79 分，你可以使用下面的公式。
=INDEX(B2:B6,MATCH(D3,A2:A6,1))
……这就是这个公式的工作原理
在此公式中，我们在 match 函数中为match_type使用 1，这允许它执行近似搜索。它返回小于或等于搜索值的第一个值。
对于 79，第一个最低值是 75，对于 75，等级是 B。这就是结果中得到 B 的原因。
4. 横向搜索
如您所知，HLOOKUP 用于水平搜索，但您也可以使用 INDEX 和 MATCH 来实现。在下面的数据表中，您有一个每月销售额的水平表，并且您想要获取“五月”的销售额。
公式为：
=INDEX(amount,0,MATCH(lookup_month,months,0))
……这就是这个公式的工作原理
在上面的公式中，我们没有在索引的 row_num 参数中使用 MATCH，而是在 column_num 中使用它。并且，match 返回五月的列号。
然后INDEX根据位置号返回结果列的值。
5. 双向搜索
在双向搜索中，我们需要从表中获取值。只需查看下表即可了解按地区和产品划分的销售额。
现在，如果您想获取特定区域的产品销售额，您需要双向搜索，为此您需要使用 INDEX MATCH MATCH 的组合。是的，这里你必须使用MATCH两次。
在 INDEX 和 MATCH 的正常组合中，您使用 MATCH 作为行号，但在双向搜索中，您还必须使用它作为列号。公式为：
=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))
……这就是这个公式的工作原理
在上面的公式中，为了从表中获取销售额，您使用了索引函数，然后对其 row_num 和 column_num 参数使用了 match 函数。作为 column_num 参数的 match 函数返回 5 作为您引用的范围的第 5 行中的 Product-D 值。
并且，在 row_num 参数中找到的 match 函数返回 2，因为北区值位于您引用的范围的第二列中。
现在，使用这些值，索引函数返回第 2 列第 5 行中的值：1456。
6.区分大小写
如果您在列表或列中有两个相同的值但文本大小写不同时遇到问题，则可以执行区分大小写的搜索来查找正确的值。让我们看一下下面的学生列表，您在其中列出了名字，并在第二列中进行了标记。
而且，在开头，有一些名词是相同的，但在不同的文本情况下。例如，约翰·帕克和约翰·马修。假设您要搜索“JOHN”而不是“John”的品牌，您可以使用 INDEX 和 MATCH 创建精确匹配搜索。公式为：
=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))
……这就是这个公式的工作原理
在此公式中，您在匹配函数中使用了 EXACT 函数。由于 match 函数无法搜索区分大小写的值，而 EXACT 是一个完美的函数。
它可以比较两个值，如果它们完全相同（包括大小写），则返回 TRUE，但是您必须以表格形式输入此公式，因为您需要将整个列与 EXACT 中的单个值进行比较。当你输入它时，它会返回一个像这样的数组。
=INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))
从那里您需要使用 match 函数才能从数组中获取 TRUE 的位置。
此时，您已获得需要查找的值的单元格编号（行号）。并且，您可以对此列值使用 INDEX。
重要一点：如果您通过 EXACT 获得多个 TRUE，则匹配将仅返回第一个 TRUE 的数字
7. 使用通配符和 INDEX MATCH
通配符非常有用。您可以使用通配符执行部分搜索。而且，最好的部分是，像所有其他公式搜索一样，您也可以将通配符与索引和匹配一起使用。
只需看一下下面的名单，其中就有员工的名字和姓氏以及年龄。
您需要从此列表中获取特定员工 (Sondra) 的年龄。但事实是你只知道名字。
而且，如果您使用星号，则可以使用名字搜索桑德拉的年龄。为此，公式为：
=INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)
……这就是这个公式的工作原理
星号是通配符，可以替换 n 个字符。因此，当您在名字后面使用它时，它会取代姓氏。
8. 最低值
假设您有一个学生列表，其分数如下。现在，您想要从此列表中搜索得分最低的学生的姓名。
为此，您可以使用带有索引和匹配的 MIN 函数，公式为：
=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))
A 列中有学生的姓名，B 列中有学生的分数。
因此，当您将此公式插入单元格并按 Enter 时，它将返回分数最低的学生的姓名，即 Librada Bastian。
解释
在这个公式中，我们有三个不同的部分。
在第一部分中，MIN 函数返回最低分数。
之后，在第二部分中，匹配函数返回最低分数的单元格。
最后，索引函数使用 match 返回的相同单元格位置返回学生姓名列的值。
提示：以同样的方式，您还可以获取得分最高的学生的姓名。
9.第n次最佳成绩
现在这样想，您有一个包含考试成绩的学生列表，您希望从该列表中获取获得第二高分的学生的姓名。
问题是，你不知道第二高分是多少。
通常，当您使用公式搜索来搜索值时，您可以确定要查找的值。但是，在这里你不知道第二高分是多少。
因此，为此，您可以将一个大函数与索引结合起来并进行匹配。大函数将帮助您确定范围内的第二高值。
公式为：
=INDEX(student_names,MATCH(LARGE(score,2),score,0))
……这就是这个公式的工作原理
在此公式中，您在 match 函数内使用了 Large 函数作为 Lookup_value 参数。在大函数中，您提到了分数范围和 2 以获得第二高值。
Large 函数返回第二大值后，match 函数使用该值并返回相应的单元格编号。
最后，索引函数使用此单元格编号并返回学生姓名。
10. 多重标准
通常，索引和匹配的组合旨在搜索单个值。这就是为什么在匹配函数中只使用一个范围的原因。
但是，有时当您面对现实世界中的数据时，您需要使用多个标准来查找值。
考虑下面的例子。这里有一个产品列表，其中包含一些详细信息，例如产品名称、类别和尺寸。
您希望根据所有标准从这些数据中获取特定产品的价格。
因此，公式为：
=INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))
注意：这是一个数组公式，所以需要使用ctrl+shift+enter输入。
……这就是这个公式的工作原理
在此公式中，您有三个不同的数组来匹配三个不同的值，并且这些数组在值匹配的情况下返回 TRUE 和 FALSE。
之后，当你将它们相乘时，你会得到一个数组或类似的东西。
=INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))match 函数返回数组中 1 的位置。
最后，INDEX 使用匹配返回的数字返回价格列的价格。
提示：如果您不想使用数组公式，可以使用SUMPRODUCT 条件。
11. 范围的第一个数值
假设您有一个列表，其中同时包含文本和数值，现在您想从该列表中获取第一个数值。
为此，您可以将 ISNUMBER 函数与索引/匹配结合起来。 ISNUMBER 可以帮助您识别哪个值是数字，哪个值是文本。
公式为：
=INDEX(list,MATCH(TRUE,ISNUMBER(list),0))
您必须以表格形式输入此公式（使用 Ctrl + Shift + Enter）。
……这就是这个公式的工作原理
在此公式中，ISNUMBER 返回一个等于列表长度的数组，在该数组中，数字值为 TRUE，其余值为 FALSE。
之后，您在匹配函数中使用 TRUE 作为搜索值。所以它返回数组中第一个 TRUE 的位置号。
最终，使用此位置编号索引返回第一个数值。
12.获取第一个非空值
让我们这样想，您有一个值列表，其中某些第一个单元格为空，并且您想要获取第一个非空值。
并且，您可以使用此公式来获取第一个非空值。
=INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))
……这就是这个公式的工作原理
我们需要将这个公式分为三个不同的部分才能明智地理解它。
首先，您在 match 函数中使用 ISBLANK 函数来获取一个数组，其中 TRUE 表示空单元格，FALSE 表示非空单元格。
其次，MATCH 返回 ISBLANK 返回的数组中第一个 TRUE 的位置号。
因此，此时您已获得第一个非空白值的单元格编号。
第三，索引函数仅返回列表中的第一个非空值。
13. 最常见的文本
现在假设给定一个文本值列表，您需要计算最常见的文本。
下面的列表中有你的名字。
但是，有些名字不止出现一次。
因此，现在您需要获取列表中出现次数最多的名称。您可以使用下面的公式，它是 MODE、INDEX 和 MATCH 的组合。
=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))
当您插入此公式时，它将返回“Tamesha”，这是最常见的名称。
……这就是这个公式的工作原理
首先，MATCH 会将整个名称范围与其自身进行比较。并且，在此过程中，它将返回一个数组，其中每个文本都代表其第一个位置。
让我们以“Tamesha”这个名字为例，它是我们列表中最常见的名字。现在，如果您查看列表，就会发现这种情况首先发生在第 8 个单元格上，然后发生在第 12 个单元格上。
但是，如果你看一下表，对于我们有“Tamesha”的所有位置，它返回 8，这是它的第一个位置。
之后，从 MATCH 返回的数组中，mode 函数将返回出现次数最多的数字，即第一次出现“ Tamesha ”的单元格编号。
最后，INDEX 将使用此单元格编号返回文本。
14. 创建超链接
现在，假设除了搜索值之外，您还想为该值创建超链接。这样您就可以快速导航到搜索列所在的单元格。
例如，在下表中您需要获取一个人的年龄。而且，如果您为此值创建超链接，则可以轻松导航到该值所在的单元格。
并且，为此我们必须使用 HYPERLINK + Cell 以及 INDEX 和 MATCH，公式为：
这就是这个公式的工作原理
让我们把这个公式分成几个部分，以便更好地理解它。
- 首先，您在单元格函数中使用了索引和匹配。并且，当您在单元格函数中使用这两个函数时，您将获得相应值的单元格引用，而不是相应的值。
- 其次，您已将“#”与单元格引用连接起来。
- 第三，您再次使用索引和匹配来获取用作链接文本的匹配值。这样您就可以获得相应的值以及该值所在单元格的链接。