对于精通Excel的人来说，您很可能对(Excel)VLOOKUP函数非常熟悉。VLOOKUP函数用于根据同一行中的某些匹配文本在不同的单元格中查找值。
如果您对VLOOKUP功能还不熟悉，可以查看我之前关于如何在 Excel 中使用 VLOOKUP 的(how to use VLOOKUP in Excel)帖子。
尽管VLOOKUP(VLOOKUP)功能强大，但它在匹配参考表的结构方面存在限制，以便公式能够正常工作。
本文将向您展示VLOOKUP无法使用的限制，并在Excel中引入另一个称为INDEX-MATCH的函数，可以解决该问题。
索引匹配 Excel 示例
使用以下示例 Excel 电子表格，我们有一个车主姓名和车名列表。在此示例中，我们将尝试 根据多个所有者下列出的Car Model获取(Car Model)Car ID，如下所示：
在名为CarType的单独工作表上，我们有一个简单的汽车数据库，其中包含 ID、汽车型号(Car Model)和颜色(Color)。
使用此表设置， VLOOKUP函数仅在我们要检索的数据位于我们尝试匹配的列右侧（汽车型号(Car Model )字段）时才能工作。
换句话说，对于这个表结构，由于我们试图根据Car Model来匹配它，我们唯一能得到的信息是Color（不是ID，因为ID 列位于 Car Model列的左侧。）
这是因为对于VLOOKUP，查找值必须出现在第一列中，并且查找列必须在右侧。在我们的示例中，这些条件都不满足。
好消息是，INDEX-MATCH 将能够帮助我们实现这一目标。实际上，这实际上是结合了两个可以单独工作的Excel函数：INDEX函数和MATCH函数。
但是，出于本文的目的，我们将仅讨论两者的结合，目的是复制VLOOKUP的功能。
起初，这个公式似乎有点长而且令人生畏。然而，一旦你多次使用它，你就会背诵它的语法。
这是我们示例中的完整公式：
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
这是每个部分的细分
=INDEX( - “=”表示单元格中公式的开头，INDEX是我们正在使用的(INDEX)Excel函数的第一部分。
CarType!$A$2:$A$5 – 工作表CarType 上包含我们要检索的数据的列。在此示例中， 每个Car Model的(Car Model.)ID 。
MATCH( - 我们正在使用的(MATCH( )Excel函数的第二部分。
B4 - 包含我们正在使用的搜索文本的单元格（汽车模型(Car Model)）。
CarType!$B$2:$B$5 – 工作表CarType 上的列，其中包含我们将用于匹配搜索文本的数据。
0)) – 表示搜索文本必须与匹配列中的文本完全匹配（即 CarType!$B$2:$B$5）。如果未找到完全匹配，则公式返回#N/A。
注意：记住这个函数末尾的双右括号“))”和参数之间的逗号。(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)
就我个人而言，我已经远离VLOOKUP，现在使用 INDEX-MATCH 因为它能够做的比VLOOKUP更多。
与VLOOKUP(VLOOKUP)相比，INDEX-MATCH函数还有其他好处：
- 更快的计算(Faster Calculations)
当我们处理大型数据集时，由于许多VLOOKUP(VLOOKUP)函数，计算本身可能需要很长时间，您会发现一旦将所有这些公式替换为INDEX-MATCH，整体计算将计算得更快。
- 无需计算相对列(No Need to Count Relative Columns)
如果我们的参考表在列C(C)中有我们想要搜索的关键文本，并且我们需要获取的数据在列AQ中，那么在使用( AQ)VLOOKUP时，我们需要知道/计算列 C 和列 AQ 之间有多少列.
通过 INDEX-MATCH函数，我们可以直接选择需要获取数据的索引列（即AQ列），并选择要匹配的列（即C列）。
- 看起来更复杂(It Looks More Complicated)
VLOOKUP现在很常见，但很少有人知道一起使用 INDEX-MATCH 函数。
INDEX-MATCH函数中较长的字符串有助于使您看起来像处理复杂和高级Excel函数的专家。享受！
When to Use Index-Match Instead of VLOOKUP in Excel
For thoѕe of you who are well-verѕed in Excel, you are most likely very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.
If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.
As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.
This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.
INDEX MATCH Excel Example
Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:
On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.
With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).
In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)
This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.
The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.
However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.
The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.
This is the full formula in our example:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Here is the breakdown for each section
=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.
CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.
MATCH( – The second part of the Excel function that we are using.
B4 – The cell that contain search text that we are using (Car Model).
CarType!$B$2:$B$5 – The columns on sheet CarType with the data which we will use to match against the search text.
0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.
Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.
Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.
The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:
- Faster Calculations
When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.
- No Need to Count Relative Columns
If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.
With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).
- It Looks More Complicated
VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.
The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!
