英文:
Find all values in Column A which are present in Column B
问题
以下是您要的翻译：
考虑下面的表格：
| A | B |
| -------- | ------ |
| 1 | 4 |
| 3 | 5 |
| 2 | 2 |
| 5 | 0 |
| 4 | 1 |
我想要查找列1的每一行是否与列2的任何一行匹配。理想情况下，这将给我：
| A | B |C|
| -------- | ------ | ---- |
| 1 | 4 |是|
| 3 | 5 |否|
| 2 | 2 |是|
| 5 | 0 |是|
| 4 | 1 |是|
作为第一步，我正在使用`=MATCH(A2,B2:B6)`来获得匹配的**索引**，然后手动在各行之间调用这个公式，以获得类似这样的结果：
| A | B |C|
| -------- | ------ | ---- |
| 1 | 4 |6|
| 3 | 5 |-|
| 2 | 2 |3|
| 5 | 0 |2|
| 4 | 1 |1|
现在我遇到了一个问题：
我想要将此方法应用于A中的500行和B中的2000行。我考虑手动填充前几行，然后选择并拖动前500行。然而，这不起作用，因为对于每个后续单元格，它只会更改公式为`=MATCH(A(N +1),B2 + N:B6 + N)`，这会给我错误的值，最糟糕的情况下，只是重复旧的模式。
有人可以帮助我如何仅使用`MATCH`函数找到A中存在于B中的所有值吗？
英文:
Consider the sheet below:
| A | B |
|---|---|
| 1 | 4 |
| 3 | 5 |
| 2 | 2 |
| 5 | 0 |
| 4 | 1 |
I want to find if there is a match for each row of column 1 with any row of column 2. So ideally this would give me:
| A | B | C |
|---|---|---|
| 1 | 4 | Yes |
| 3 | 5 | No |
| 2 | 2 | Yes |
| 5 | 0 | Yes |
| 4 | 1 | Yes |
As a first and simple step, I am using =MATCH(A2,B2:B6) to get the index of the match and then manually calling this across the rows to get something like this:
| A | B | C |
|---|---|---|
| 1 | 4 | 6 |
| 3 | 5 | - |
| 2 | 2 | 3 |
| 5 | 0 | 2 |
| 4 | 1 | 1 |
I am now having a problem:
I want to apply this for a row of 500 in A and 2000 in B. I was thinking of manually filling in the first few rows and then select and drag over the first 500 rows. This however does not work as for each subsequent cell, it just changes the formula to =MATCH(A(N +1),B2 + N:B6 + N) which gives me wrong values and at worst, just repeats the older pattern ahead.
Can anyone help me with how I can just use the MATCH function to find all the values in A that are present in B?
答案1
得分: 2
让我继续你停下的地方：
=MATCH(A2,B2:B6,0)
（你忘了最后的零）
这个公式是正确的，但也是错误的。
???
当你拖动它时，你得到：
=MATCH(A3,B3:B7,0)
这不是你想要的：你希望搜索项（A2）变成A3，但你希望搜索数组（B2:B6）保持不变。为了实现这个目标，你需要使用绝对引用。公式看起来是这样的：
=MATCH(A2,B$2:B$6,0)
当你拖动它下来时，你得到：
=MATCH(A3,B$2:B$6,0)
=> 到目前为止没问题。
现在的问题是：你需要将当前的结果（一个数字或者#N/A）翻译成“是”或“否”。有很多种方法可以做到这一点，让我给你举个例子：
=IF(ISERROR(MATCH(A2,B$2:B$6,0)),"No","Yes")
一个注意：Excel中有一个IFERROR()函数，但它没有“else”子句，所以选择了IF(ISERROR(的组合。
英文:
Let me continue where you arrived:
=MATCH(A2,B2:B6,0)
(You forgot the last zero)
This formula is correct, but it is also wrong.
???
Well, when you drag it down, you get:
=MATCH(A3,B3:B7,0)
This is not what you want: you want the search term (A2) to change into A3 but you want the search array (B2:B6) not to change. In order to get this done, you need to work with absolute references. This looks like this:
=MATCH(A2,B$2:B$6,0)
When you drag this down, this is what you get:
=MATCH(A3,B$2:B$6,0)
=> ok so far.
Problem now: you need to translate your current results (a number or #N/A) into "yes" or "no". This can be done in numerous ways, let me give you an example:
=IF(ISERROR(MATCH(A2,B$2:B$6,0)),"No","Yes")
One remark: there exists an IFERROR() function in Excel, but this does not have an "else"-clause, hence the choice for the IF(ISERROR( combination.
答案2
得分: 0
你可以尝试在表格中使用以下公式：
=index(if(len(A2:A),if(ifna(xmatch(A2:A,B2:B)),"Yes","No"),))
英文:
Within Sheets you may try this out:
=index(if(len(A2:A),if(ifna(xmatch(A2:A,B2:B)),"Yes","No"),))
答案3
得分: 0
如果您想要分离这些匹配的数值，可以使用FILTER()函数。
=FILTER(A1:A5,COUNTIFS(B1:B9,A1:A5))
对于动态的YES和NO，可以尝试使用MAP()函数。
=MAP(A1:A5,LAMBDA(x,ISNUMBER(XMATCH(x,B:B))))
英文:
If you want to separate those matching values then could use FILTER() function.
=FILTER(A1:A5,COUNTIFS(B1:B9,A1:A5))
And for YES, NO dynamically, try MAP() function.
=MAP(A1:A5,LAMBDA(x,ISNUMBER(XMATCH(x,B:B))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论