您有一个包含数千个数字和单词的Excel 工作簿。(Excel workbook)那里肯定有相同数字或单词(number or word)的倍数。您可能需要找到它们。因此，我们将研究在Excel 365中查找匹配值的几种方法。
我们将介绍在两个不同的工作表和两个不同的列中查找相同的单词或数字。我们将研究如何使用EXACT、MATCH和VLOOKUP函数。我们将使用的一些方法可能不适用于Microsoft Excel的 Web 版本，但它们都可以在桌面版本中使用。
什么是 Excel 函数？(What’s An Excel Function?)
如果您以前使用过函数，请跳过。
Excel 函数(Excel function)就像一个迷你应用程序(mini app)。它应用一系列步骤来执行单个任务。最常用的Excel 函数(Excel function)可以在“公式(Formulas )”选项卡中找到。在这里，我们看到它们按功能的性质分类-(function –)
- 自动求和
- 最近使用
- 金融的
- 逻辑的
- 文本
- 约会时间
- 查找和参考
- 数学与三角
- 更多功能。
More Functions类别包含Statistical、Engineering、Cube、Information、Compatibility 和 Web(Statistical, Engineering, Cube, Information, Compatibility, and Web)类别。
确切的功能(The Exact Function)
Exact 函数(Exact function)的任务(s task)是遍历两列的行并在 Excel 单元格中查找匹配值(Excel)。准确的意思是准确的。Exact 函数(Exact function)本身是区分大小写的。它不会将纽约(New York )和纽约(new york )视为一场比赛。
在下面的示例中，有两列文本 - Tickets 和 Receipts(text – Tickets and Receipts)。只有 10 组文本，我们可以通过查看它们来比较它们。想象一下，如果有 1,000 行或更多行。那时您将使用Exact 函数(Exact function)。
将光标放在单元格 C2 中。在公式栏中(formula bar)，输入公式
=EXACT(E2:E10,F2:F10)
E2:E10指的是第一列值，F2:F10指的是它旁边的列。一旦我们按下Enter，Excel将比较每行中的两个值并告诉我们它是否匹配（True）或不匹配（False）。由于我们使用范围而不是仅使用两个单元格，因此该公式将溢出到其下方的单元格中并评估所有其他行。
这种方法虽然有限。它只会比较同一行上的两个单元格。例如，它不会比较 A2 和 B3 中的内容。我们如何做到这一点？MATCH可以提供帮助。
MATCH 函数(The MATCH Function)
MATCH可用于告诉我们特定值的匹配项在单元格范围内的位置。
假设我们想在下面的示例中找出特定SKU（库存单位(Stock Keeping Unit)）在哪一行。
如果我们想找到AA003所在的行，我们将使用以下公式：
=MATCH(J1,E2:E9,0)
J1指的是具有我们要匹配的值的单元格。E2:E9是指我们正在搜索的值的范围。公式末尾的零 ( 0 ) 告诉(0)Excel查找完全匹配。如果我们匹配数字，我们可以使用1来查找小于查询的内容，或者使用2查找大于查询的内容。
但是如果我们想找到AA003的价格呢？
VLOOKUP 函数(The VLOOKUP Function)
VLOOKUP中的V代表垂直。这意味着它可以在列中搜索给定值。它还可以在与找到的值相同的行上返回一个值(found value)。
如果您在每月频道(Monthly channel)中订阅了Office 365，则可以使用较新的XLOOKUP。如果您只有半年订阅，它将在2020 年 7 月(July 2020)提供给您。
让我们使用相同的库存数据并尝试找出某物的价格。
在我们之前寻找行的地方，输入公式：
=VLOOKUP(J1,E2:G9,3,FALSE)
J1指的是我们匹配的值的单元格。E2:G9是我们正在使用的值的范围。但VLOOKUP只会在该范围的第一列中查找匹配项。3指的是从范围开始的第 3 列 。
因此，当我们在 J1 中键入SKU时， (SKU)VLOOKUP将找到匹配项并从单元格 3(cell 3)列中获取值。FALSE告诉Excel我们正在寻找什么样的匹配。FALSE意味着它必须是精确匹配，而TRUE会告诉它它必须是紧密匹配。
如何在两个不同的工作表中找到匹配值？(How Do I Find Matching Values in Two Different Sheets?)
上面的每个函数都可以跨两个不同的工作表在Excel中查找匹配的值。我们将使用EXACT 函数(EXACT function)向您展示如何操作。这几乎可以用任何功能来完成。不仅仅是我们在这里介绍的那些。还有其他方法可以链接不同工作表和工作簿之间的单元格。
在Holders表上工作，我们输入公式
=EXACT(D2:D10,Tickets!E2:E10)
D2:D10是我们在Holders sheet上选择的范围。一旦我们在后面加上一个逗号，我们就可以单击门票表并拖动(Tickets sheet and drag)并选择第二个范围。
看看它是如何将工作表和范围(sheet and range)引用为Tickets!E2:E10的？在这种情况下，每一行都匹配，所以结果都是True。
我还能如何使用这些功能？(How Else Can I Use These Functions?)
一旦掌握了这些用于匹配和查找事物的功能，您就可以开始用它们做很多不同的事情。还要看看一起使用 INDEX 和 MATCH 函数(using the INDEX and MATCH functions)来做类似于VLOOKUP的事情。
关于使用Excel(Excel)函数在Excel中查找匹配值有一些很酷的技巧吗？也许关于如何做更多的问题？在下面的评论中给(Drop)我们留言。
How to Find Matching Values in Excel
You’ve got an Excel workbook with thousands of numbers and words. There are bоund to be multiples of the sаme numbеr or word in there. You might need to find them. So we’re goіng to look at several waуs you can find matching values in Excel 365.
We’re going to cover finding the same words or numbers in two different worksheets and in two different columns. We’ll look at using the EXACT, MATCH, and VLOOKUP functions. Some of the methods we’ll use may not work in the web version of Microsoft Excel, but they will all work in the desktop version.
What’s An Excel Function?
If you’ve used functions before, skip ahead.
An Excel function is like a mini app. It applies a series of steps to perform a single task. The most commonly used Excel functions can be found in the Formulas tab. Here we see them categorized by the nature of the function –
- AutoSum
- Recently Used
- Financial
- Logical
- Text
- Date & Time
- Lookup & Reference
- Math & Trig
- More Functions.
The More Functions category contains the categories Statistical, Engineering, Cube, Information, Compatibility, and Web.
The Exact Function
The Exact function’s task is to go through the rows of two columns and find matching values in the Excel cells. Exact means exact. On its own, the Exact function is case sensitive. It won’t see New York and new york as being a match.
In the example below, there are two columns of text – Tickets and Receipts. For only 10 sets of text, we could compare them by looking at them. Imagine if there were 1,000 rows or more though. That’s when you would use the Exact function.
Place the cursor in cell C2. In the formula bar, enter the formula
=EXACT(E2:E10,F2:F10)
E2:E10 refers to the first column of values and F2:F10 refers to the column right next to it. Once we press Enter, Excel will compare the two values in each row and tell us if it’s a match (True) or not (False). Since we used ranges instead of just two cells, the formula will spill over into the cells below it and evaluate all the other rows.
This method is limited though. It will only compare two cells that are on the same row. It won’t compare what’s in A2 with B3 for example. How do we do that? MATCH can help.
The MATCH Function
MATCH can be used to tell us where a match for a specific value is in a range of cells.
Let’s say we want to find out what row a specific SKU (Stock Keeping Unit) is in, in the example below.
If we want to find what row AA003 is in, we would use the formula:
=MATCH(J1,E2:E9,0)
J1 refers to the cell with the value we want to match. E2:E9 refers to the range of values we’re searching through. The zero (0) at the end of the formula tells Excel to look for an exact match. If we were matching numbers, we could use 1 to find something less than our query or 2 to find something greater than our query.
But what if we wanted to find the price of AA003?
The VLOOKUP Function
The V in VLOOKUP stands for vertical. Meaning it can search for a given value in a column. What it can also do is return a value on the same row as the found value.
If you’ve got an Office 365 subscription in the Monthly channel, you can use the newer XLOOKUP. If you only have the semi-annual subscription it will be available to you in July 2020.
Let’s use the same inventory data and try to find the price of something.
Where we were looking for a row before, enter the formula:
=VLOOKUP(J1,E2:G9,3,FALSE)
J1 refers to the cell with the value we’re matching. E2:G9 is the range of values we’re working with. But VLOOKUP will only look in the first column of that range for a match. The 3 refers to the 3rd column over from the start of the range.
So when we type a SKU in J1, VLOOKUP will find the match and grab the value from the cell 3 columns over from it. FALSE tells Excel what kind of match we’re looking for. FALSE means it must be an exact match where TRUE would tell it that it has to be a close match.
How Do I Find Matching Values in Two Different Sheets?
Each of the functions above can work across two different sheets to find matching values in Excel. We’re going to use the EXACT function to show you how. This can be done with almost any function. Not just the ones we covered here. There are also other ways to link cells between different sheets and workbooks.
Working on the Holders sheet, we enter the formula
=EXACT(D2:D10,Tickets!E2:E10)
D2:D10 is the range we’ve selected on the Holders sheet. Once we put a comma after that, we can click on the Tickets sheet and drag and select the second range.
See how it references the sheet and range as Tickets!E2:E10? In this case each row matches, so the results are all True.
How Else Can I Use These Functions?
Once you master these functions for matching and finding things, you can start doing a lot of different things with them. Also take a look at using the INDEX and MATCH functions together to do something similar to VLOOKUP.
Have some cool tips on using Excel functions to find matching values in Excel? Maybe a question about how to do more? Drop us a note in the comments below.
