EX
如果
A檔案的A1=B檔案的A1:A100中的某值
A檔案的B1=B檔案的B1:100中的某值
A檔案的C1=B檔案的C1:100中的某值
那麼
A檔案的D1=B檔案的D1:100中的某值
不知道這樣問問題,夠清楚嗎?
請先進不吝指導...
謝謝...
Function getMatchValue(ByVal table_Array As Range, ByVal col_Index_Num As Integer, _
ByVal lookup_Value1 As Variant, ByVal lookup_Col_index_Num1 As Integer, _
Optional ByVal lookup_Value2 As Variant, Optional ByVal lookup_Col_index_Num2 As Integer, _
Optional ByVal lookup_Value3 As Variant, Optional ByVal lookup_Col_index_Num3 As Integer) As Variant
Dim i, j As Integer
Dim rng, items
Dim hasKey2, hasKey3 As Boolean
hasKey2 = Not IsMissing(lookup_Value2) And Not IsError(lookup_Value2)
hasKey3 = Not IsMissing(lookup_Value3) And Not IsError(lookup_Value3)
ReDim items(499) As Variant
i = 0
items(i) = Empty
For Each rng In table_Array.Offset(0, lookup_Col_index_Num1 - 1).Resize(table_Array.Rows.Count, 1)
If rng.Value = lookup_Value1 Then
If Not hasKey2 Then
GoSub collectItem
Else
If rng.Offset(0, lookup_Col_index_Num2 - lookup_Col_index_Num1).Value = lookup_Value2 Then
If Not hasKey3 Then
GoSub collectItem
Else
If rng.Offset(0, lookup_Col_index_Num3 - lookup_Col_index_Num1).Value = lookup_Value3 Then
GoSub collectItem
End If
end if
end if
End If
End If
Next
If Not IsNull(items(0)) And Len(items(0)) > 0 Then
getMatchValue = items
GoSub returnAsLines
Else
getMatchValue = Empty
End If
Exit Function
returnAsLines:
Dim ln As String
ln = ""
For j = 0 To i - 1
ln = ln & items(j) & Chr(10)
Next
If Len(ln) > 1 Then getMatchValue = Left(ln, Len(ln) - 1)
Return
collectItem:
items(i) = rng.Offset(0, col_Index_Num - lookup_Col_index_Num1).Value
i = i + 1
Return
End Function
2.在工作表上的D欄位，輸入公式:
=getmatchvalue([refer.xlsx]工作表1!A$1:D$9,4,B161,1,C161,2,D161,3)
a.refer.xlsx 也就是B表
b.4 表示傳回 Refer.xlsx 的工作表1 的第四欄(D)的值
c.B161,C161,D161 代表查找值所在格, 1,2,3 分別代表Refer.xlsx 的工作表1 上的第一欄、第二欄、第三欄
內文搜尋
從 APP 打開
X