vlookup 只能返回第一个数,有办法可以返回满足这个条件的全部单元格的数组吗?vlookup不行,有其他函数吗?
例如:
c1 c2
1 a
2 c
1 d
求:c1为1的c2的值,返回数组{"a","d"}
vlookup 只能返回第一个数,有办法可以返回满足这个条件的全部单元格的数组吗?vlookup不行,有其他函数吗?
例如:
c1 c2
1 a
2 c
1 d
求:c1为1的c2的值,返回数组{"a","d"}
SplicingRange(Arr As Range, n As Integer),第一个参数是选定区域,第二个参数是 你想查询的哪个值
Function SplicingRange(Arr As Range, n As Integer)
Dim x As Integer
For i = 1 To Arr.Rows.Count
If Arr.Cells(i, 1) = n Then
If SplicingRange <> "" Then
SplicingRange = SplicingRange & "," & Arr.Cells(i, 2)
Else
SplicingRange = Arr.Cells(i, 2)
End If
End If
Next
End Function
插入开发工具,并启用宏
改为返回 数组
Function SplicingRange(Arr As Range, n As Integer)
Dim x As Integer
For i = 1 To Arr.Rows.Count
If Arr.Cells(i, 1) = n Then
If SplicingRange <> "" Then
SplicingRange = SplicingRange & ",""" & Arr.Cells(i, 2) & """"
Else
SplicingRange = "{""" & Arr.Cells(i, 2) & """"
End If
End If
Next
SplicingRange = SplicingRange & "}"
End Function