vba =MATCH() 等效于多维范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7179115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
=MATCH() equivalent for multidimensional ranges
提问by Zach
I have an excel sheet, where cells A1-C20==INT(RAND()*10). This is my data range. Cell E1=1, E2=2, E3=3, etc. These are the values I am trying to find. I set cell F1==MATCH(E1,A:C,0), F2==MATCH(E1,A:C,0), etc.
我有一个 Excel 表，其中单元格 A1-C20= =INT(RAND()*10)。这是我的数据范围。单元格 E1=1、E2=2、E3=3 等。这些是我试图找到的值。我设置单元格 F1= =MATCH(E1,A:C,0)， F2==MATCH(E1,A:C,0)等。
However, all the MATCHfunctions return #N/A, because the input range is multi-dimensional. How can I test whether a given value (1, 2, 3, 4, etc.) exists in a multidimensional range (A1-C20)?
但是，所有MATCH函数都返回#N/A，因为输入范围是多维的。如何测试给定值（1、2、3、4 等）是否存在于多维范围 (A1-C20) 中？
/edit: This functionworks, but is more than I need. Is there any way to make it return just TRUE or FALSE, depending on if the lookup value is in the range?
/edit： 此功能有效，但超出了我的需要。有没有办法让它只返回 TRUE 或 FALSE，具体取决于查找值是否在范围内？
Function OzgridLookup(Find_Val As Variant, Occurrence As Long, Table_Range As Range, _
Offset_Cols As Long, Optional Column_Lookin As Long, Optional Row_Offset As Long) As Variant
Dim lLoop As Long
Dim FoundCell As Range
If Column_Lookin = 0 Then 'No column # specified
With Table_Range
'Top left cell has Find_Val & Occurrence is 1
If Table_Range.Cells(1, 1) = Find_Val And Occurrence = 1 Then
OzgridLookup = .Cells(1, 1)(1, Offset_Cols + 1)
Exit Function 'All done :)
Else 'No column # specified so search all for _
nth Occurrence reading left to right
Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start
For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _
and each time Set "FoundCell" to start next Find from
Set FoundCell = _
Table_Range.Find(What:=Find_Val, After:=FoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlRows, SearchDirection:=xlNext)
Next lLoop
End If
End With
Else 'column # specified
With Table_Range.Columns(Column_Lookin) 'Work with column # specified
Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start
For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _
and each time Set "FoundCell" to start next Find from
Set FoundCell = _
Table_Range.Find(What:=Find_Val, After:=FoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlRows, SearchDirection:=xlNext)
Next lLoop
End With
End If
OzgridLookup = FoundCell.Offset(Row_Offset, Offset_Cols)
End Function
回答by Excellll
You can use COUNTIF to do this since you only want to know whether the number is present (not its location).
您可以使用 COUNTIF 来执行此操作，因为您只想知道该号码是否存在（而不是其位置）。
=COUNTIF(A:C,E1)>0
This will return "TRUE" if it is present, "FALSE" if it is not.
如果存在，则返回“TRUE”，否则返回“FALSE”。
Just for fun, here's a worksheet function solution that returns the cell address that matches the lookup value. It uses the fact that you are only searching in 3 columns.
只是为了好玩，这里有一个工作表函数解决方案，它返回与查找值匹配的单元格地址。它使用您仅在 3 列中搜索的事实。
=IF(ISERROR(MATCH(E1,A:A,0)),IF(ISERROR(MATCH(E1,B:B,0)),IF(ISERROR(MATCH(E1,C:C,0)),"Not found.","C"&MATCH(E1,C:C,0)),"B"&MATCH(E1,B:B,0)),"A"&MATCH(E1,A:A,0))
I thought I'd also throw in a VBA solution that can return the match location inside a (contiguous) range. It looks at columns one at a time from left to right and returns the address of the first match found.
我想我也会投入一个 VBA 解决方案，它可以返回（连续）范围内的匹配位置。它从左到右一次查看一列，并返回找到的第一个匹配项的地址。
Public Function MDMATCH(srchfor As String, lookin As Range) As String
Application.Volatile
Dim RngArray() As Variant
Dim topleft As String
Dim tmpval As String
topleft = lookin.Address
topleft = Left(topleft, InStr(topleft, ":") - 1)
tmpval = "Not found."
RngArray = lookin
For i = 1 To UBound(RngArray, 2)
If tmpval = "Not found." Then
For j = 1 To UBound(RngArray, 1)
If RngArray(j, i) = srchfor Then
tmpval = Range(topleft).Offset(j - 1, i - 1).Address
Exit For
End If
Next j
Else
Exit For
End If
Next i
MDMATCH = tmpval
End Function
