用于将列与重复值进行比较的Excel函数

I'm using excel and have two columns (A & B) with values

我使用excel，有两个列(A和B)和值

I want to search for each value in Column A and return the position in Column B. I'm using this formula:

我想搜索A列中的每个值，然后返回b列中的位置，我使用的公式是:

IFERROR(MATCH("Values in Column A";"Array = Column B";0);0)

The results are:

结果是:

Column A  | Column B  | Column C
  1       |   4       |   2
  2       |   1       |   3
  3       |   2       |   4
  4       |   1       |   1
  1       |   2       |   2
          |   3       | 

It works fine if it doesn't encounter repeated values. However, I want it to encounter repeated values, so the formula should ignore the ones it was encountered before and go through the others. So the correct result should look like this:

如果它没有遇到重复的值，那么它可以正常工作。但是，我希望它遇到重复的值，因此公式应该忽略它之前遇到的值，并遍历其他值。所以正确的结果应该是这样的:

Column C  
  2  
  3  
  5  
  1  
  4

Can you help me on this? Is there a VBA routine for this?

你能帮我一下吗?有VBA程序吗?

1 个解决方案

#1

From the article Getting the 2nd matching value from a list using VLOOKUP formula, you can create a helper column to affix the instance number of each value, to create unique id's.

通过使用VLOOKUP公式从列表中获取第2个匹配值，您可以创建一个helper列来附加每个值的实例数，以创建惟一的id。

For example, in Column C, add the following function:

例如，在C列中，添加以下函数:

=A1&"-"&COUNTIF($A$1:A1,A1)

Note: The relative reference on the count range will cause the applicable range to grow as it is dragged down. The count of the items matching that cell in a range containing only that cell should always be one. As it gets dragged down to include other cells, it will increment accordingly.

注意:计数范围上的相对引用将导致适用范围在向下拖动时增长。在只包含该单元格的范围内匹配该单元格的项的计数应该始终为1。当它被拖下以包含其他单元时，它将相应地增加。

Then add the same thing in Column D to get the instances of cells in Column B:

然后在D列中添加同样的东西，得到B列中单元格的实例:

=B1&"-"&COUNTIF($B$1:B1,B1)

Finally, do the math you want to do in Column E like this:

最后，做你想在E列中做的数学运算:

=IFERROR(MATCH(C1,D:D,0),0)