Search value by row and column condition in target range (Matrix).
这个函数根据给出的两个查询条件，在指定的区域中查找值。查找不到时，返回空字符串。
比如：
某个物品具体两种状态，这两种状态组合起来可形成第三种状态。见下表：
| Title | X | A | AS | BG | FN | Q | U | Others |
| X | Active | Phasing out 1 | Phasing out 1 | Active | New | x | Phasing out 3 | x |
| A | Phasing out 1 | Phasing out 2 | Phasing out 3 | Phasing out 3 | x | x | Phasing out 3 | x |
| AS | Phasing out 2 | Phasing out 3 | Inactive | Inactive | x | Q Blocked | Inactive | x |
| BG | Active | x | Inactive | Other region | New | Q Blocked | Invalid | x |
| FN | New | x | x | New | New | x | x | x |
| Q | x | x | Q Blocked | Q Blocked | Q Blocked | Q Blocked | x | x |
| U | Phasing out 3 | Phasing out 3 | Inactive | Invalid | Invalid | x | invalid | x |
| Others | x | x | x | x | x | x | x | x |
则我们可以通过此函数依据条件获得以下结果：
| Status1 | Status2 | Status3 |
| U | A | Phasing out 3 |
| U | FN | Invalid |
| Q | FN | Q Blocked |
| U | AS | Inactive |
| U | BG | Invalid |
原理：
根据给定的status1去在目标矩阵中，找到符合条件的行；再根据给定的status2去找到目标矩阵中符合条件的列。最后返回行和列所对应的值。
an example of how to call this function:
=LookupByMatrix(A2, B2, $E$20:$M$28)
原码：
Public Function LookupByMatrix(para1 As String, para2 As String, Matrix As Range)
' Note: This function is case sensitive .
Dim nCol, nRow, nFirstCol, nFirstRow As Integer
nRow = Matrix.Rows.Count
nCol = Matrix.Columns.Count
' nFirstCol = Matrix.Cells(1, 1).Column
' nFirstRow = Matrix.Cells(1, 1).Row
With Matrix
For I = 2 To nRow ' Find value in column A5 (scan rows)
If .Cells(I, 1) = para1 Then 'if found value for A5,
For J = 2 To nCol ' Find value for column L (Scan columns)
If .Cells(1, J) = para2 Then
LookupByMatrix = .Cells(I, J).Value
Exit For
Else
LookupByMatrix = ""
End If
Next J
Exit For
Else
LookupByMatrix = ""
End If
Next I
End With
End Function
No comments:
Post a Comment