Function WLOOKUP(X As Variant, M As Range, A As Byte, B As Integer)
'X 條件
'M 要查詢的單列範圍
'A 返回第n個符合條件的結果(255)
'B 返回結果的列索引，可以是0或負數
' 用於數據查詢的單列範圍M所在列的值為1，代表第1列
' 右側第一列為2,3,4.....;左側第一列為0,-1,-2.....
Dim i As Integer, mr As Range, y As Integer
i = Application.WorksheetFunction.CountIf(M, X)
'Set M =Intersect(M.Parent.Usedrange,M)
For Each mr In M
If mr.Value = X Then
y = y + 1
If y = A Then
WLOOKUP = mr.Offset(0, B - 1).Value
Exit Function
End If
End If
Next mr
WLOOKUP = ""
End Function
Function WLOOKUP(X, xA As Range, xB As Range, SP$) As String
Dim i&, T$
For i = 1 To xA.Rows.Count
If xA(i) = X Then T = Trim(T & " " & xB(i))
Next i
WLOOKUP = Replace(T, " ", SP)
End Function
發表於 2016-11-29 11:45
| 
發表於 2016-11-29 12:40
| 