单元格匹配的单元格，而不是值匹配的值 [英] Cell for cell match instead of value for value match

问题描述

理想情况下，我可以使用excel公式来完成此操作，但如果不可能，我还将接受用户定义函数作为解决方案.

Optimally, I would have it done with an excel formula, but if it isn't possible, I'd also accept a User Defined Function as solution.

考虑以下屏幕截图:

尽管C列中的匹配公式=IF(ISERROR(MATCH(A2,$B$2:$B$12,0)),"No Match", "Match")似乎运行良好，但对我来说有一个警告:即使在Lookup Cells列中只有两个4，它们也会显示"Match"查找范围列中的一个4.

Though the match formulas =IF(ISERROR(MATCH(A2,$B$2:$B$12,0)),"No Match", "Match") in the Column C seem to be working perfectly, they have one caveat for me: They display "Match" even if there are two 4 in the Lookup Cells Column for only one 4 in the Lookup Range Column.

在excel中，是否有一个函数或它们的组合对单元格进行匹配而不是对值进行匹配?

例如，在上面的示例中，第5行中的4不应显示匹配".而是应显示"No Match".

For instance, in the example above, the 4 in the 5th row should not display "Match". Rather, it should display "No Match".

现在，正如我在问题开始时所说的那样，如果无法使用excel函数，我将改为使用UDFS.

Now, as I've stated at the beginning of the question, if it isn't possible with excel functions, I'll instead make do with UDFS.

我还没有完成以vba代码形式编写的算法，它有3个参数:查找单元格，查找单元格和查找范围.函数输出为匹配"或不匹配".

The algorithm, that I've not yet finished writing in the form of vba code, is one that has 3 arguments: the lookup cell, the lookup cells and the lookup range. The function output is "Match" or "No Match".

基本上，如果我以上面的示例为例，查找单元格可能是 A2，查找单元格$ A $ 2:$ A $ 5和查找范围$ B $ 2:$ B $ 12.

Basically, if I take the example above, the lookup cell could be A2, the lookup cells, $A$2:$A$5 and the lookup range, $B$2:$B$12.

使用查找单元格和查找范围，我创建了两个数组，每个数组一个.

With the lookup cells and the lookup range, I create two arrays, one for each.

然后，循环比较它们的值.如果它们的值之一相同，则我将它们的值与它们在查找范围列中的相对行一起添加到另一个数组中，并将其在绝对值行中的绝对行数添加到另一个数组中. 查找单元格"列.该数组是动态数组，具有二维.

Afterwards, compare their values in a loop. If one of their values are the same, I add their value to another array along with their relative row within the lookup range column and their absolute row number within the Lookup Cells column. This array is dynamic and has two dimensions.

然后，我(停留在这一部分)将有另一个循环，将查找单元格中的值与数组中的值进行比较.如果它们相等，那么我希望Lookup Range循环在数组(+1)中存储的该值的相对行之后开始其循环.

Then, I would (stuck at this part) have another loop comparing the values from the lookup cells to the values within the array. If they are equal, then I'd like for the Lookup Range loop to start its loop after the relative row of this value stored in the array (+1).

最后，如果遍历所有内容并找到匹配的单元格(数组中的值)，则如果数组中的绝对行号之一与查找单元格的绝对行号一致(该函数的第一个参数) ，然后函数返回匹配".如果不是，则返回"No Match".

Finally, after looping through it all and finding which cells do match (values in the array), if one of the absolute row numbers in the array coincide with the absolute row number of the lookup cell (1st argument of the function), then the function returns "Match". If not, it returns "No Match".

我的感谢和我未完成的代码如下:

My thanks and my unfinished code below:

Function Rlookup(ByVal LookupCell As Range, ByVal LookupCells, ByVal LookupRange)

Dim LookupRngArray As Variant
Dim LookupCellsArray As Variant
Dim i As Long
Dim z As Long
Dim x As Long
Dim w As Long
Dim y As Long
Dim Arr() As Variant

x = 0

LookupRngArray = LookupRange.Value2
LookupCellsArray = LookupCells.Value2

For i = 1 To UBound(LookupCellsArray)

    For z = 1 To UBound(LookupRngArray)

        If LookupCellsArray(i, 1) = LookupRngArray(z, 1) Then

            For y = x To 0 Step -1

                If LookupCells(i, 1) = Arr(1, y + 1) Then
                    z > Arr(1, y + 1)

                    Else

                    x = x + 1
                    ReDim Preserve Arr(1 To 3, 1 To x)
                    Arr(1, x) = LookupCellsArray(i, 1)
                    Arr(2, x) = Application.WorksheetFunction.Match(LookupCellsArray(i, 1), LookupRngArray, 0)
                    Arr(3, x) = LookupCells.Row + i - 1

                End If

            Next y

        End If

    Next z

Next i

For w = 1 To x

If LookupCell.Row = Arr(3, w) Then
Rlookup = "Match"

End If
Next

If Rlookup = "0" Then Rlookup = "No Match"
End Function

推荐答案

可以使用COUNTIF()完成:

Can be done with COUNTIF():

=IF(A2<>"",IF(COUNTIF($A$2:A2,A2)<=COUNTIF(B:B,A2),"match","No Match"),"")

这篇关于单元格匹配的单元格，而不是值匹配的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！