表格如下:
函數代碼如下
Function WLOOKUP(X As Range, M As Variant, A As Byte, B As Byte)
Dim I As Integer, g
I = Application.WorksheetFunction.CountIf(M, X)
'g = X + "*"
'=IF(COUNTIF(A1,"g?o"),1,0)'IF支持通配符
Set M = Intersect(M.Parent.UsedRange, M)
For Each MR In M
' If (CountIf(MR.Value, g)) Then
If MR.Value Like X + "*" Then
'If [X] Like MR.Value Then
Y = Y + 1
If Y > I Then Exit Function
If Y = A Then
WLOOKUP = MR.Offset(0, B).Value
End If
End If
Next MR
End Function
用法實例:
基本用法 : =wlookup(A3,A3:F10,2,3) 引用單元格 引用區域 第幾個數 第幾列
高級篩選
L2 輸入 =wlookup($K$2,$A$3:$A$10,ROW(A1),COLUMN(A1))
向下 向右拖動, 篩選中所有數據
閱讀更多 淡墨留餘香 的文章