如何在Microsoft Excel中使用XLOOKUP函数

Excel’s new XLOOKUP will replace VLOOKUP, providing a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and has extra functionality. Here’s what you need to know.

Excel的新XLOOKUP将取代VLOOKUP，为Excel最受欢迎的功能之一提供了强大的替代品。 此新功能解决了VLOOKUP的一些局限性，并具有其他功能。 这是您需要知道的。

什么是XLOOKUP？ (What is XLOOKUP?)

The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP. Plus, it also replaces HLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number. VLOOKUP is not this easy to use or as versatile. We’ll show you how it all works.

新的XLOOKUP函数为VLOOKUP的某些最大限制提供了解决方案。 另外，它还替代了HLOOKUP。 例如，XLOOKUP可以向左看，默认为完全匹配，并允许您指定单元格范围而不是列号。 VLOOKUP并非易用或通用。 我们将向您展示一切。

For the moment, XLOOKUP is only available to users on the Insiders program. Anyone can join the Insiders program to access the newest Excel features as soon as they become available. Microsoft will soon begin to roll it out to all Office 365 users.

目前，XLOOKUP仅对Insiders程序的用户可用。 只要有最新功能，任何人都可以加入Insiders计划以访问它们。 Microsoft将很快开始将其推广到所有Office 365用户。

如何使用XLOOKUP函数 (How to Use the XLOOKUP Function)

Let’s dive straight in with an example of XLOOKUP in action. Take the example data below. We want to return the department from column F for each ID in column A.

让我们直接来看一个实际使用XLOOKUP的示例。 请看下面的示例数据。 我们想为A列中的每个ID从F列返回部门。

This is a classic exact match lookup example. The XLOOKUP function requires just three pieces of information.

这是经典的完全匹配查找示例。 XLOOKUP函数仅需要三个信息。

The image below shows XLOOKUP with six arguments, but only the first three are necessary for an exact match. So let’s focus on them:

下图显示了带有六个参数的XLOOKUP，但是对于精确匹配，仅前三个参数是必需的。 因此，让我们专注于它们：

• Lookup_value: What you are looking for.

  Lookup_value：您要查找的内容。

• Lookup_array: Where to look.

  Lookup_array：在哪里看。

• Return_array: the range containing the value to return.

  Return_array：包含要返回的值的范围。

The following formula will work for this example: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

下面的公式将适用于此示例： =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Let’s now explore a couple of advantages XLOOKUP has over VLOOKUP here.

现在让我们在这里探索XLOOKUP相对于VLOOKUP的几个优点。

没有更多的列索引号 (No More Column Index Number)

The infamous third argument of VLOOKUP was to specify the column number of the information to return from a table array. This is no longer an issue because XLOOKUP enables you to select the range to return from (column F in this example).

VLOOKUP的臭名昭著的第三个参数是指定要从表数组返回的信息的列号。 这不再是问题，因为XLOOKUP使您能够选择要返回的范围(在此示例中为F列)。

And don’t forget, XLOOKUP can view the data left of the selected cell, unlike VLOOKUP. More on this below.

别忘了，与VLOOKUP不同，XLOOKUP可以查看所选单元格左侧的数据。 在下面的更多内容。

You also no longer have the issue of a broken formula when new columns are inserted. If that happened in your spreadsheet, the return range would adjust automatically.

插入新列时，您也不再遇到公式中断的问题。 如果您的电子表格中发生这种情况，则返回范围将自动调整。

完全匹配是默认设置 (Exact Match is the Default)

It was always confusing when learning VLOOKUP why you had to specify an exact match was wanted.

在学习VLOOKUP时，总是很困惑，为什么要指定一个精确匹配。

Fortunately, XLOOKUP defaults to an exact match—the far more common reason to use a lookup formula). This reduces the need to answer that fifth argument and ensures fewer mistakes by users new to the formula.

幸运的是，XLOOKUP默认为完全匹配，这是使用查找公式的更为常见的原因。 这减少了回答第五个参数的需要，并确保了新接触公式的用户减少了错误。

So in short, XLOOKUP asks fewer questions than VLOOKUP, is more user-friendly, and is also more durable.

简而言之，与VLOOKUP相比，XLOOKUP提出的问题更少，更加用户友好，并且更持久。

XLOOKUP可以向左看 (XLOOKUP can Look to the Left)

Being able to select a lookup range makes XLOOKUP more versatile than VLOOKUP. With XLOOKUP, the order of the table columns does not matter.

能够选择查找范围使XLOOKUP比VLOOKUP更具通用性。 使用XLOOKUP，表列的顺序无关紧要。

VLOOKUP was constrained by searching the left-most column of a table and then returning from a specified number of columns to the right.

通过搜索表的最左列，然后从指定的列数向右返回，可以限制VLOOKUP。

In the example below, we need to lookup an ID (column E) and return the person’s name (column D).

在下面的示例中，我们需要查找ID(E列)并返回此人的姓名(D列)。

The following formula can achieve this: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

下面的公式可以实现此目的： =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

如果找不到怎么办 (What to Do If Not Found)

Users of lookup functions are very familiar with the #N/A error message that greets them when their VLOOKUP or their MATCH function cannot find what it needs. And often there is a logical reason for this.

查找函数的用户非常熟悉＃N / A错误消息，当他们的VLOOKUP或MATCH函数找不到所需的消息时，它们会向他们打招呼。 通常这是有逻辑的。

Therefore, users quickly research how to hide this error because it is not correct or useful. And, of course, there are ways to do so.

因此，用户会Swift研究如何隐藏此错误，因为它不正确或无用。 而且，当然有一些方法可以做到这一点。

XLOOKUP comes with its own built-in “if not found” argument to handle such errors. Let’s see it in action with the previous example, but with a mistyped ID.

XLOOKUP带有自己的内置“如果找不到”参数来处理此类错误。 让我们在前面的示例中看到它的作用，但是ID输入错误。

The following formula will display the text “Incorrect ID” instead of the error message: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

下面的公式将显示文本“ =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID") ID”而不是错误消息： =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

使用XLOOKUP进行范围查找 (Using XLOOKUP for a Range Lookup)

Although not as common as the exact match, a very effective use of a lookup formula is to look for a value in ranges. Take the following example. We want to return the discount dependent upon the amount spent.

尽管不完全匹配，但查找公式的一种非常有效的用法是查找范围内的值。 请看下面的例子。 我们要根据所消费的金额退还折扣。

This time we are not looking for a specific value. We need to know where the values in column B fall within the ranges in column E. That will determine the discount earned.

这次我们不在寻找特定值。 我们需要知道B列中的值在E列中的范围内。这将确定所获得的折扣。

XLOOKUP has an optional fifth argument (remember, it defaults to the exact match) named match mode.

XLOOKUP有一个可选的第五个参数(请记住，它默认为完全匹配)，称为匹配模式。

You can see that XLOOKUP has greater capabilities with approximate matches than that of VLOOKUP.

您可以看到，XLOOKUP具有比VLOOKUP更大的近似匹配功能。

There is the option to find the closest match smaller than (-1) or closest greater than (1) the value looked for. There is also an option to use wildcard characters (2) such as the ? or the *. This setting is not on by default like it was with VLOOKUP.

可以选择查找小于该值的(-1)或大于(1)的最接近的匹配项。 还有一个使用通配符(2)的选项，例如？。 或者 *。 默认情况下，此设置未启用，就像使用VLOOKUP时一样。

The formula in this example returns the closest less than the value looked for if an exact match is not found: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

如果未找到完全匹配的示例，则此示例中的公式返回的最接近值小于所寻找的值： =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

However, there is a mistake in cell C7 where the #N/A error is returned (the ‘if not found’ argument was not used). This should have returned a 0% discount because spending 64 does not reach the criteria for any discount.

但是，在单元格C7中有一个错误，该错误返回了＃N / A错误(未使用“如果找不到”自变量)。 这应该返回0％的折扣，因为支出64没有达到任何折扣的标准。

Another advantage of the XLOOKUP function is that it does not require the lookup range to be in ascending order as VLOOKUP does.

XLOOKUP函数的另一个优点是，它不需要像VLOOKUP那样将查找范围按升序排列。

Enter a new row at the bottom of the lookup table and then open up the formula. Expand the used range by clicking and dragging the corners.

在查询表的底部输入新行，然后打开公式。 通过单击并拖动角来扩展使用范围。

The formula immediately corrects the error. It is not a problem with having the “0” at the bottom of the range.

该公式立即纠正错误。 将“ 0”放在范围的底部不是问题。

Personally, I would still sort the table by the lookup column. Having “0” at the bottom would drive me crazy. But the fact that the formula didn’t break is brilliant.

就个人而言，我仍将按查询列对表进行排序。 底部为“ 0”会让我发疯。 但是，公式没有被打破的事实真是太棒了。

XLOOKUP也替换了HLOOKUP函数 (XLOOKUP Replaces the HLOOKUP Function Too)

As mentioned, the XLOOKUP function is also here to replace HLOOKUP. One function to replace two. Excellent!

如前所述，XLOOKUP函数也可以代替HLOOKUP 。 一种功能替代两种功能。 优秀的！

The HLOOKUP function is the horizontal lookup, used for searching along rows.

HLOOKUP函数是水平查找，用于沿行搜索。

Not as well known as its sibling VLOOKUP, but useful for examples like below where the headers are in column A, and the data is along rows 4 and 5.

不像其兄弟VLOOKUP那样众所周知，但对于下面的示例很有用，其中标头位于A列中，数据沿第4和5行。

XLOOKUP can look in both directions – down columns and also along rows. No longer do we need two different functions.

XLOOKUP可以在两个方向上看-向下查看列，也可以沿行查看。 我们不再需要两个不同的功能。

In this example, the formula is used to return the sales value relating to the name in cell A2. It looks along row 4 to find the name, and returns the value from row 5: =XLOOKUP(A2,B4:E4,B5:E5)

在此示例中，该公式用于返回与单元格A2中的名称相关的销售价值。 它沿着第4行查找名称，然后从第5行返回值： =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP可以从下往上看 (XLOOKUP Can Look From the Bottom-Up)

Typically, you need to hunt down a list to find the first (often only) occurrence of a value. XLOOKUP has a sixth argument named search mode. This enables us to switch the lookup to start at the bottom and look up a list to find the last occurrence of a value instead.

通常，您需要查找列表以查找值的第一次出现(通常是唯一出现)。 XLOOKUP具有第六个名为搜索模式的参数。 这使我们能够将查找切换为从底部开始，并查找列表以查找最后出现的值。

In the example below, we would like to find the stock level for each product in column A.

在下面的示例中，我们想在A列中找到每种产品的库存水平。

The lookup table is in date order, and there are multiple stock checks per product. We want to return the stock level from the last time it was checked (last occurrence of the Product ID).

查找表按日期顺序排列，每个产品有多个库存检查。 我们要从上次检查(最近一次出现的产品ID)返回库存水平。

The sixth argument of the XLOOKUP function provides four options. We are interested in using the “Search last-to-first” option.

XLOOKUP函数的第六个参数提供了四个选项。 我们对使用“从后到先搜索”选项感兴趣。

The completed formula is shown here: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

完整的公式如下所示： =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

In this formula, the fourth and fifth argument were ignored. It is optional, and we wanted the default of an exact match.

在此公式中，第四和第五自变量被忽略。 它是可选的，我们需要默认为完全匹配。

围捕 (Round-Up)

The XLOOKUP function is the eagerly awaited successor to both the VLOOKUP and HLOOKUP functions.

XLOOKUP函数是VLOOKUP和HLOOKUP函数都迫切期待的后继 。

A variety of examples were used in this article to demonstrate the advantages of XLOOKUP. One of which is that XLOOKUP can be used across sheets, workbooks and also with tables. The examples were kept simple in the article to help our understanding.

本文中使用了许多示例来演示XLOOKUP的优点。 其中之一是XLOOKUP可以跨工作表，工作簿以及表使用。 本文中的示例保持简单，以帮助我们理解。

Due to dynamic arrays being introduced into Excel soon, it can also return a range of values. This is definitely something worth exploring further.

由于动态数组很快就会引入Excel ，因此它也可以返回一定范围的值。 这绝对是值得进一步探索的东西。

The days of VLOOKUP are numbered. XLOOKUP is here and will soon be the de facto lookup formula.

VLOOKUP的日期已编号。 XLOOKUP在这里，很快就会成为事实上的查找公式。

翻译自: https://www.howtogeek.com/439286/how-to-use-the-xlookup-function-in-microsoft-excel/